[Dbix-class] Complex join problem

[Chris Cole]

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'