[Dbix-class] Complex join problem

[Peter Rabbitson]

Chris Cole wrote:
> Hi all,
> 
> I'm managing to 'cascade' joins along relation's relationships, but only
> so far. Is there a limit?
> 
> The table definitions are at the bottom, but I'm trying to convert this
> SQL into DBIx:
> SELECT
> db_match.read_id,db_match.seq_id,match_start,match_end,query_start,mismatch,abundance
> FROM db_match
> JOIN seq_reads ON db_match.read_id = seq_reads.read_id
> JOIN reads2expt ON seq_reads.read_id = reads2expt.read_id
> WHERE expt_id = 2
> 
> The code have is:
> my @rs = $schema->resultset('DbMatch')->search(
>    {
>       expt_id => 2,
>    },
>    {
>       join => {         # join on seq_reads -> reads2expt tables
>          read_id => {
>            reads2expts => 'read_id'
>          }
>       },
>    }
> );
> 
> This works, in that I get the right rows back, but I cannot retrieve any
> specific column values from the reads2expt table (e.g. 'abundance').
> Neither with a get_column() method nor directly. I've tried things like
> the following with no joy:
> 
> $rs[0]->abundance;
> $rs[0]->read_id->reads2expts->read_id->get_column('abundance');
> 
> I can get get the data from the other tables no problem, it's just the
> reads2expt one. Anyone know where I'm going wrong?
> Any help is most appreciated.

Your SeqReads has_many Reads2expt, thus the call:

read_id->reads2expts returns a ResultSet, not a Row. Now the real
question is - which 'abundance' do you want? The one from the read_id
of the first reads2expts? Or the last one? Or all of them?

Possibilities would be:

my $col = $rs[0]->read_id->reads2expts->first->read_id->get_column('abundance');
my @cols = $rs[0]->read_id->reads2expts->search_related ('read_id')->get_column('abundance')->all;

You get the idea

Also do not call a relationship 'read_id' - call it 'read'

Cheers