package LIMS::Model::Roles::QueryFormatter;
use Moose::Role;
has search_fields_map => ( is => 'ro', isa => 'HashRef', lazy_build => 1 );
has relationships_map => ( is => 'ro', isa => 'HashRef', lazy_build => 1 );
has search_constraint => ( is => 'rw', isa => 'HashRef', default => sub { {} });
use strict;
use Data::Dumper;
#-------------------------------------------------------------------------------
sub get_args_for_requests_count {
my ($self, $search_constraints) = @_; # warn Dumper $search_constraints;
$self->search_constraint($search_constraints);
# require data from these tables:
my @tables = (); # none for count
# format args_for_search:
my $args_for_search = $self->_format_args_for_search(\@tables);
return $args_for_search; # warn Dumper $args_for_search;
}
#-------------------------------------------------------------------------------
sub get_args_for_request_id {
my ($self, $search_constraints) = @_;
$self->search_constraint($search_constraints);
# require data from these tables:
my @tables = qw( patients specimens referrers referral_sources clinical_trials );
# format args_for_search:
my $args_for_search = $self->_format_args_for_search(\@tables);
return $args_for_search; # warn Dumper $self->args_for_search;
}
#-------------------------------------------------------------------------------
# recieves $args hashref with keys 'search_constraints' & 'args_for_search':
sub get_args_for_find_requests {
my $self = shift;
my $args = shift; # hashref with keys = 'search_constraints' & 'args_for_search'
my $search_constraints = $args->{search_constraints}; # $self->debug($search_constraints);
# add $search_constraints to $self for _format_relationships():
$self->search_constraint($search_constraints);
# require data from these tables:
my @tables = qw( patients referrers referral_sources diagnoses status_options);
# format args_for_search:
my @args = (\@tables, $args->{args_for_search});
my $args_for_search = $self->_format_args_for_search(@args);
return $args_for_search; # warn Dumper $self->args_for_search;
}
#-------------------------------------------------------------------------------
# accepts pairs of params & returns them in a format suitable for RDBO::Manager
sub format_query {
my ($self, %params) = @_;
# RBDO::Manager needs query as arrayref of pairs:
return [ %params ];
}
#-------------------------------------------------------------------------------
# accepts arrayref of table_names, returns arrayref of relationship names:
sub get_relationships {
my ($self, $tables) = @_; # arrayref
my $relationships_map = $self->relationships_map;
# get relationship names needed for table joins:
my @relationships = map {
$relationships_map->{$_}->{rel_name}
} grep {
# check entry exists (or rel silently skipped & db gets hit again for missing data):
$relationships_map->{$_} || die "no such entry '$_' in _build_relationships_map()"
} @$tables;
return \@relationships;
}
#-------------------------------------------------------------------------------
# accepts $required_relationships arrayref and optional $args_for_search hashref
# or creates new; adds 'query' arrayref from $self->search_constraints; adds
# 'with_objects' & 'require_objects' arrayrefs if required; returns $args_for_search
sub _format_args_for_search {
my $self = shift;
my $tables = shift; # arrayref
my $args_for_search = shift || {}; # optional (contains limit, sort_by, etc)
my $search_fields_map = $self->search_fields_map; # warn Dumper $search_fields_map;
my $relationships_map = $self->relationships_map;
my $search_constraints = $self->search_constraint; # warn Dumper $search_constraints;
{ # convert $search_constraints hashref format into arrayref for query func:
my @params = %$search_constraints;
# put 'query' into $args_for_search:
$args_for_search->{query} = \@params;
}
# convert $tables array(ref) into a hash to prevent duplicate relationships
# in FIELD block below:
my %tables = map { $_ => 1 } @$tables; # warn Dumper \%tables;
# get any additional table joins required for this query:
FIELD:
foreach my $field (keys %$search_constraints) { # warn $field;
# get table name for 'key' of search_constraints (last_name, unit_number, etc):
my $table_name = $search_fields_map->{$field}
|| next FIELD; # eg request_number, year - don't need additional table joins
$tables{$table_name}++;
} # warn Dumper \%tables;
# create 'with_objects' & 'require_objects' from data in %tables:
foreach my $table (keys %tables) { # keys are unique so tbl only loaded once
my $relationship_data = $relationships_map->{$table} # eg with => 'request_trial'
|| die "no such entry '$table' in _build_relationships_map()";
my $join_type = $relationship_data->{join_type}; # 'with_objects' or 'require_objects'
my $rel_name = $relationship_data->{rel_name}; # eg 'patient_case.patient', etc
# push relationship name onto $args_for_search $join_type key:
push @{ $args_for_search->{$join_type} }, $rel_name;
}
if ( $args_for_search->{with_objects} ) {
# needs multi_many_ok => 1 to silence warnings and nested_joins => 0 for speed:
$args_for_search->{multi_many_ok} = 1;
$args_for_search->{nested_joins} = 0;
} # warn Dumper $args_for_search;
# set default 'order by':
$args_for_search->{sort_by} ||= 'requests.id'; # not supplied in patient merge
return $args_for_search;
}
#-------------------------------------------------------------------------------
sub _build_search_fields_map {
my $self = shift;
# create map of form field => $self->relationships_map name:
my %search_fields_map = (
# field_name # in table
last_name => 'patients',
first_name => 'patients',
dob => 'patients',
nhs_number => 'patients',
unit_number => 'patients',
name => 'referrers',
specimen_id => 'specimens',
option_name => 'request_options',
trial_id => 'clinical_trials',
trial_number => 'patient_trials',
referral_source_id => 'referral_sources',
parent_code => 'parent_organisations',
external_reference => 'request_external_ref',
# for simple sql query:
clinical_details => 'request_report',
comment => 'request_report',
detail => 'request_gross_description',
sample_code => 'sample_code',
'diagnoses.name' => 'diagnoses',
'referrers.name' => 'referrers', # or could just use 'name' as above
'display_name' => 'referral_sources',
'patients.id' => 'patients',
'unit_number' => 'patient_case',
'screens.description' => 'request_initial_screen',
results_summary => 'results_summary',
trial_name => 'clinical_trials',
);
return \%search_fields_map;
}
#-------------------------------------------------------------------------------
# map of relationship names for tables, relative to LIMS::DB::Request:
sub _build_relationships_map {
my $self = shift;
my %relationship_map = (
# table name => join-type / relationship name
# require = 'inner join'; with = 'left outer join'
clinical_trials => {
join_type => 'with_objects',
rel_name => 'request_trial.trial',
},
diagnoses => {
join_type => 'with_objects',
rel_name => 'request_report.diagnosis',
},
diagnostic_categories => {
join_type => 'with_objects', # changed from diagnosis.icdo3_category.diagnostic_category
rel_name => 'request_report.diagnosis.diagnostic_category',
},
hospital_departments => {
join_type => 'require_objects',
rel_name => 'referrer_department.hospital_department',
},
parent_organisations => {
join_type => 'require_objects',
rel_name => 'patient_case.referral_source.parent_organisation',
},
patients => {
join_type => 'require_objects',
rel_name => 'patient_case.patient',
},
patient_case => {
join_type => 'require_objects',
rel_name => 'patient_case',
},
patient_trials => {
join_type => 'require_objects',
rel_name => 'patient_case.patient.patient_trial',
},
referral_sources => {
join_type => 'require_objects',
rel_name => 'patient_case.referral_source',
},
referrers => {
join_type => 'require_objects',
rel_name => 'referrer_department.referrer',
},
request_external_ref => {
join_type => 'with_objects',
rel_name => 'request_external_ref',
},
request_general_notes => {
join_type => 'with_objects',
rel_name => 'request_general_note',
},
request_history => {
join_type => 'require_objects',
rel_name => 'request_history',
},
request_gross_description => {
join_type => 'with_objects',
rel_name => 'request_gross_description',
},
request_initial_screen => {
join_type => 'with_objects',
rel_name => 'request_initial_screen.screen',
},
request_options => {
join_type => 'with_objects',
rel_name => 'request_option.option',
},
request_report => {
join_type => 'require_objects',
rel_name => 'request_report',
},
results_summary => {
join_type => 'require_objects',
rel_name => 'results_summary',
},
sample_code => { # used by simple sql search only
join_type => 'require_objects',
rel_name => 'request_specimen.specimen',
},
specimens => {
join_type => 'require_objects',
rel_name => 'request_specimen',
},
status_options => {
join_type => 'require_objects',
rel_name => 'status_option',
},
);
return \%relationship_map;
}
1;
