[Dbix-class] Complex join problem

[Chris Cole]

Peter Rabbitson wrote:
> Chris Cole wrote:
>> Peter Rabbitson wrote:
>>> You are not prefetching properly somewhere. Yet this is getting extremely
>>> confusing - please start fresh showing a "slow but correct" dbic query
>>> (the entire search chain) and the resulting SQL (presumably the one above).
>> Ok. Sorry for not getting back earlier, I've been sidetracked with
>> something else.
>>
>> The problem stems in trying to retrieve columns from the reads2expt
>> table in a SELECT.
>>
>> The below works as expected:
>>
>> my $schema = DB::Schema->connect();
>> my @data;
>> my @rs = $schema->resultset('DbMatch')->search(
>>    {
>>       expt_id => $dataset,
>>       abundance => {'>' => 1},
>>       type => 'hairpin-miRNA'
>>    },
>>    {
>>       join => [
>>          {         # join on seq_reads -> reads2expt tables
>>             read_id => {
>>                reads2expts => 'read_id'
>>             }
>>          },
>>          'seq_id'  # join db_sequences table.
>>       ]
>>    }
>> );
>>
>> foreach my $hit (@rs) {
>>    push @data, [ $hit->get_column('seq_id'), $hit->match_start ];
>> }
>> return(\@data);
>>
>> The SQL for the above is:
>> SELECT me.seq_id, me.read_id, me.search_id, me.match_start,
>> me.match_end, me.query_start, me.mismatch FROM db_match me  JOIN
>> seq_reads read_id ON read_id.read_id = me.read_id LEFT JOIN reads2expt
>> reads2expts ON reads2expts.read_id = read_id.read_id LEFT JOIN seq_reads
>> read_id_2 ON read_id_2.read_id = reads2expts.read_id  JOIN db_sequences
>> seq_id ON seq_id.seq_id = me.seq_id WHERE ( ( abundance > ? AND expt_id
>> = ? AND type = ? ) ): '1', '1', 'hairpin-miRNA'
>>
>>
>> However, if I want to get the value for the 'abundance' column for each
>> row I need to use a search_related() step, which dramatically slows down
>> the process.
>>
>>
> 
> Read up on this: http://search.cpan.org/~ribasushi/DBIx-Class-0.08103/lib/DBIx/Class/Manual/Cookbook.pod#Using_joins_and_prefetch

Ah, right. Thanks.

The prefetch is working as far as I can tell, but can't seem to access 
the relationship. e.g.

    my $schema = DB::Schema->connect();

    my @data;
    my @rs = $schema->resultset('DbMatch')->search(
       {
          expt_id => $dataset,
          abundance => {'>' => 1},
          type => 'hairpin-miRNA'
       },
       {
          join => [
             {         # join on seq_reads -> reads2expt tables
                read_id => {
                   reads2expts => 'read_id'
                }
             },
             'seq_id'  # join db_sequences table. Need this in order to 
filter by 'type'
          ],
          prefetch => [
             {
               read_id => {
                   reads2expts => 'read_id'
                }
             },
          ]
       },
    );

The SQL from this is:
SELECT me.seq_id, me.read_id, me.search_id, me.match_start, 
me.match_end, me.query_start, me.mismatch, read_id.read_id, 
read_id.length, read_id.seq, reads2expts.expt_id, reads2expts.read_id, 
reads2expts.abundance, reads2expts.read_name, read_id_2.read_id, 
read_id_2.length, read_id_2.seq FROM db_match me  JOIN seq_reads read_id 
ON ( read_id.read_id = me.read_id ) LEFT JOIN reads2expt reads2expts ON 
( reads2expts.read_id = read_id.read_id )  JOIN seq_reads read_id_2 ON ( 
read_id_2.read_id = reads2expts.read_id )  JOIN db_sequences seq_id ON ( 
seq_id.seq_id = me.seq_id ) WHERE ( abundance > ? AND expt_id = ? AND 
type = ? ) ORDER BY reads2expts.read_id: '1', '1', 'hairpin-miRNA'

But I can't get the syntax right for accessing the 'abundance' column. 
According to the docs, something like this should work:
foreach my $hit (@rs) {
   push @data, [ $hit->get_column('seq_id'), $hit->match_start, 
$hit->read_id->reads2expts->read_id->abundance ];
}

I've tried lots of different variations of the above to access the 
'abundance' data, but I always get a 'Can't locate object method...' 
error. I really can't see where I going wrong.

Thanks very much for your continued help.
Cheers,

Chris