[Dbix-class] Complex join problem

[Peter Rabbitson]

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.