[Dbix-class] Complex join problem

Peter Rabbitson rabbit+dbic at rabbit.us
Mon Jun 1 10:37:43 GMT 2009


Chris Cole wrote:
> Peter Rabbitson wrote:
>> Chris Cole wrote:
>>> Peter Rabbitson wrote:
>>>> 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.
>>> Ah, OK.
>>>
>>>> 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?
>>> The one that's associated with expt_id = 2
>>>
>>>> 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
>>> I do. Got to it with this:
>>> $rs[0]->read_id->reads2expts->search_related ('read_id', {expt_id =>
>>> 2})->get_column('abundance')->first
>>>
>>> What I don't understand is why the expt_id => 2 in the search() doesn't
>>> limit the join on the reads2expt table? It needs me to do the filtering
>>> twice. The SQL returns the right thing, so why doesn't DBIC?
>> I am not sure what do you mean by twice, and I am not sure why you
>> stuck a condition into search_related. Show generated SQL.
> 
> Because otherwise it returned all 'read_ids' that matched in the
> reads2expt table. The 'abundance' value must only be for rows where
> expt_id = 2 in that table. I don't understand why that requirement isn't
> being fed through.
> 
> SQL:
> 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 ( ( expt_id = ? AND type = ? )
> ): '2', 'hairpin-miRNA'
> 
> followed by lots of lines like below. Presumably from the
> search_related() step:
> SELECT me.read_id, me.length, me.seq FROM seq_reads me WHERE (
> me.read_id = ? ): '17'
> SELECT abundance FROM reads2expt me  JOIN seq_reads read_id ON
> read_id.read_id = me.read_id WHERE ( ( expt_id = ? AND me.read_id = ? )
> ): '2', '17'
> 
> 

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).



More information about the DBIx-Class mailing list