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. _______________________________________________ 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/dbix-class@lists.scsys.co.uk