[Dbix-class] New user with JOIN problem

[Chris Cole]

David Ihnen wrote:
> Chris Cole wrote:
>> Ultimately, the SQL code I want to replicate is:
>>
>> SELECT miRNA_id, sum(abundance) as sum
>> FROM mirbase_hairpin_match, seq_data
>> WHERE query_id = id
>> AND query_id LIKE 'mmuD%'
>> GROUP BY miRNA_id
>>
>> I've tried breaking it down to a simpler SQL for testing:
>> SELECT query_id, miRNA_id, abundance
>> FROM mirbase_hairpin_match
>> JOIN seq_data ON  query_id = id
>> AND query_id LIKE 'mmuD%'
>>
>> but the nearest I can get to is (code snippet):
>> my @rnas = $schema->resultset(MirbaseHairpinMatch)->search(
>>    {
>>       query_id => { -like => "$dataset%" },
>>    },
>>    {
>>       columns => [ qw/query_id mirna_id/ ],
>>       join => { seq_data => 'abundance'},
>>       group_by => [qw/mirna_id/],
>>    },
>> );
>>   
> First of all, you didn't include your code that is supposed to match the
> tables, so its hard to say exactly what is wrong.  Keep in mind its
> important what the classes are defined as to debug errors like this in
> the future.

Apologies. I used the DBIx::Class::Schema::Loader module to load the
schema so I didn't have it explicitly. However, I got it to dump the
schema to file.

> But I'm going to suspect that MirbaseHairpinMatch.pm MUST have a line
> something like this in it
> 
> __PACKAGE__->belongs_to('seq_data', 'Whatever::seq_data', { 'foreign.id'
> => 'self.query_id' });

Close. It's:
__PACKAGE__->belongs_to("query_id", "My::Schema::SeqData", { id =>
"query_id" });

> In order for anything like 'seq_data' to be in the 'join' clause, you
> must define the relationship!

That, I realised. Like Matt mentioned I was confusing the relationship
name with the table name. As assumed you used the table name as you
would in the SQL.

> Additionally, 'abundance' being in the join clause makes no sense at all
> as it is *not* a relationship, and may be causing the error.

More than likely. I got to stage of randomly changing things in the vain
hope it would work. The code I sent was not the last 'working' one as I
thought.

> So i'm going to guess what you mean is more like:
> 
> my @rnas = $schema->resultset(MirbaseHairpinMatch)->search
>   ( { query_id => { -like => $dataset."%" }  }
>   , { 'select' => [ 'mirna_id', 'SUM(abundance)' ]
>     , 'as' => [ 'mirna_id', 'sum' ]
>     , join => 'seq_data'
>     , group_by => 'mirna_id'
>     },
>   );

That's exactly right! I only needed to swap in the correct relationship
name as above to get it working. I hadn't got as far as sorting out the
sum() procedure because I was getting the JOIN wrong.

>> printf "Found %d miRNAs in dataset $dataset\n", scalar @rnas;
>> foreach my $mirna (@rnas) {
>>    printf "miRNA: %s %d\n", $mirna->mirna_id, $mirna->abundance;
>> }
>>   
> You seem slightly confused about how to access columns too.  If you
> wanted the abundance value, you would prefetch it (not just join) and
> access through $mirna->seq_data->abundance - but you don't want that, do
> you?  You're selecting the sum.  But thats not an accessor because its
> an additional as column.  So you use get_column instead.

Yeah, the 'abundance' thing was a mistake on my part...

> printf "Found %d miRNAs in dataset $dataset\n", scalar @rnas;
> foreach my $mirna (@rnas) {
>    printf "miRNA: %s %d\n", $mirna->mirna_id, $mirna->get_column('sum');
> }

Again correct.

> 
> Hope that helps,

Absolutely. Thanks very much.