DBIC joins in Catalyst web app

Question

I've been working with Catalyst and DBIC for a while now, but I never started a project by myself. Now I am creating a webapp database from the scratch and I started to wonder if I could make data fetching in fewer lines of code. I'll go right ahead with a chunk of the schema's diagram to make it clearer. Let's say we have a database like this:

Normally, if I wanted to fetch, say, all the informed consents signed by a particular subject, I'd so something like this:

my $consentsignatures_mod = $c->model('pbitdb::InformedConsentSubjectSignature');
my $subject_consents = $consentsignatures_mod->search(
        {subject_id => $subject_id},
        {join => 'consent'},
      );
$c->stash->{subject_consents};

And then in the template I'd iterate through the resultset like

    [% WHILE ( consent_signatures = subject_consents.next() ) -%]
            <tr>
              <td> [% consent_signatures.consent.get_column('consent_title') -%]</td>
              <td> [% consent_signatures.consent.get_column('consent_type') -%]</td>
              <td> [% consent_signatures.consent.get_column('consent_description') -%]</td>
              <td> [% consent_signatures.consent.get_column('consent_form_version') -%]</td>
              <td> [% consent_signatures.get_column('signed_date') -%]</td>
            </tr>
    [% END -%]

Similarly, for the diseases and the family history, I'd make a separate search for subject_id in the corresponding linking table and join it to the parent (precedents) to get the precedent data. This works just fine, but considering I also have to retrieve a ton of information coming from a dozen more tables (like those of familial and disease history with their corresponding relations to precedents table), I thought I should give nested joins a try. So i did... try, and this came out:

  my $subject_info = $subject_mod->search(
    {subject_id => $subject_id},
    {join => [{'disease_histories' => 'precedent'}, 
      {'informed_consent_subject_signatures' => 'consent'}
      {'familial_history' => 'precedent'}]}
  );
  $c->stash->{subject} = $subject_info

Neither syntax errors nor DBIC exceptions arise so, I guess, the above code is OK. However, I'm having troubles printing the data in the template. If I want to retrieve the informed consents, I'd do something like:

    [% WHILE ( consent_signatures = subject.informed_consent_subject_signatures.next() ) -%]
            <tr>
              <td> [% consent_signatures.consent.get_column('consent_title') -%]</td>
              <td> [% consent_signatures.consent.get_column('consent_type') -%]</td>
              <td> [% consent_signatures.consent.get_column('consent_description') -%]</td>
              <td> [% consent_signatures.consent.get_column('consent_form_version') -%]</td>
              <td> [% consent_signatures.get_column('signed_date') -%]</td>
            </tr>
    [% END -%]

All the above comes out empty as my dreams. Any ideas?

score 2 · Accepted Answer · answered Jan 29 '17 at 22:51

2

Using join you only tell DBIC to join those tsbles but to fetch data from them. Replace it with prefetch and you will see that the generated SQL will also select all related columns. Note that you should only use get_column for special cases where you need the raw database values, normally just use the generated column accessors which default to the column name.

answered Jan 29 '17 at 22:51

Alexander Hartmaier

2,178
12
21

Sorry for the delay, got caught in another project and couldn't test this out until now. I noticed I could use prefetch but didn't quite get the difference so I went with the good'ol join. Your recommendation works. Thanks a lot! – Leitouran Feb 02 '17 at 18:44

DBIC joins in Catalyst web app

1 Answers1