Perl DBIC for nested queries in a dynamically created set of conditions

Question

INTRO

I'm working on a complex form to perform customized queries to a rather large database. Because the user can use one or more fields to search for, I decided to make the conditions dynamic, like:

my $where{};


if($some_param && some_param ne ''){
  push @{ $attr->{join} }, { 'aref' => { 'a_deeper_ref' => 'a_much_deeper_ref' }};
  $where->{a_much_deeper_ref.field} = [-and => $some_param, {'!=', undef}];
}

if($another_param && $another_param ne '') {
... # same thing: do the join > build the condition.
}

my $rs = $model->search ($where, $attr);

There are about 40-50 fields and while I can address most of them like I did above, a few fields are a bit more tricky ...

**THE ISSUE **

I'll use THE problematic example to make it clearer. Supose yuo have a laboratory_exams table, which rows state that a given subject/patient [foreign key to a subject table] has been tested for a given clinical exam [foreign key to a clinical_exam table] and stores the result for that test.

Now, when the user wants to know which subjects have been tested for, say, both HIV and Hepatitis C, and had a negative result for HIV test and a positive result for Hepatitis C; the form submission would create an array with exam_ids and results which I can easily retrieve in my controller like

my $params = $c->req->parameters;
my @labexams = ref $params->{exam_ids} ? @{$params->{exam_ids}} : $params->{exam_ids};
my @results = ref $params->{results} ? @{$params->{results}} : $params->{results};

Of course, when I try to save the condition in the $where hash looping the arrays, it overrides the previous one and I end up only with the last element in the array, because...

foreach my $i (0 .. $#labexams){
 $where->{'labexams.exam_id'} = $labexams[$i];
 $where->{'labexams.result'} = $results[$i];
}

... and I just keep writing my condition on either 'labexams.exam_id' or 'labexams.result', which are static.

Notice that I need the resulting SQL to be like

... WHERE (labexams.exam_id = $labexam[$i] AND labexams.result = $result[$i]) AND ... #repeat for every exam/result pair

THE ACTUAL QUESTION

HOW ON EARTH CAN I ACHIEVE THIS?

Answer 1

I ended up building a SQL expression for the sub-queries and passing that as a reference to the $where hash. Worked like a charm <3

  if ($params->{labexam} && $params->{labexam} ne '' ){
  my @labexams = ref $params->{labexam} ? @{$params->{labexam}} : $params->{labexam};
  my @labexam_res = ref $params->{labexam_result} ? @{$params->{labexam_result}} : $params->{labexam_result};
  my $exp_sql;
  push @{ $attr->{join} }, 'laboratory_exams';

  foreach my $i (0 .. $#labexams){
    if($i == 0) {
      $exp_sql .= ' IN ';
    } else {
      $exp_sql .= ' AND laboratory_exams.subject_id IN ';
    }
    $exp_sql .= '(SELECT subject_id FROM laboratory_exams WHERE exam_id = "'.$labexams[$i].'" AND value = "'.$labexam_res[$i].'")';
    $i++;
  }
  $where->{'laboratory_exams.subject_id'} = \$exp_sql;
}