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.
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' });
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 = $schema->resultset(MirbaseHairpinMatch)->search
( { query_id => { -like => $dataset."%" } }
, { 'select' => [ 'mirna_id', 'SUM(abundance)' ]
, 'as' => [ 'mirna_id', 'sum' ]
, join => 'seq_data'
, group_by => '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
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[email protected]
