[Dbix-class] Complex join problem

[Peter Rabbitson]

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