[Dbix-class] New user with JOIN problem

[David Ihnen]

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 =3D 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 =3D id
> AND query_id LIKE 'mmuD%'
>
> but the nearest I can get to is (code snippet):
> my @rnas =3D $schema->resultset(MirbaseHairpinMatch)->search(
>    {
>       query_id =3D> { -like =3D> "$dataset%" },
>    },
>    {
>       columns =3D> [ qw/query_id mirna_id/ ],
>       join =3D> { seq_data =3D> 'abundance'},
>       group_by =3D> [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.

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' =

=3D> 'self.query_id' });

In order for anything like 'seq_data' to be in the 'join' clause, you =

must define the relationship!

Additionally, 'abundance' being in the join clause makes no sense at all =

as it is *not* a relationship, and may be causing the error.

So i'm going to guess what you mean is more like:

my @rnas =3D $schema->resultset(MirbaseHairpinMatch)->search
  ( { query_id =3D> { -like =3D> $dataset."%" }  }
  , { 'select' =3D> [ 'mirna_id', 'SUM(abundance)' ]
    , 'as' =3D> [ 'mirna_id', 'sum' ]
    , join =3D> 'seq_data'
    , group_by =3D> 'mirna_id'
    },
  );

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

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');
}


Hope that helps,

David

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20090512/62a=
1d8a5/attachment.htm