[Dbix-class] Complex join problem

[Chris Cole]

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?

> Also do not call a relationship 'read_id' - call it 'read'

Yeah the names are confusing, but these were generated automatically by
DBIC::Schema::Loader. If I change them I can't regenerate the classes :(

Thanks for the help.