Peter Rabbitson wrote:
Chris Cole wrote:
Peter Rabbitson wrote:
You are not prefetching properly somewhere. Yet this is getting extremely
confusing - please start fresh showing a "slow but correct" dbic query
(the entire search chain) and the resulting SQL (presumably the one above).
Ok. Sorry for not getting back earlier, I've been sidetracked with
something else.
The problem stems in trying to retrieve columns from the reads2expt
table in a SELECT.
The below works as expected:
my $schema = DB::Schema->connect();
my @data;
my @rs = $schema->resultset('DbMatch')->search(
{
expt_id => $dataset,
abundance => {'>' => 1},
type => 'hairpin-miRNA'
},
{
join => [
{ # join on seq_reads -> reads2expt tables
read_id => {
reads2expts => 'read_id'
}
},
'seq_id' # join db_sequences table.
]
}
);
foreach my $hit (@rs) {
push @data, [ $hit->get_column('seq_id'), $hit->match_start ];
}
return(\...@data);
The SQL for the above is:
SELECT me.seq_id, me.read_id, me.search_id, me.match_start,
me.match_end, me.query_start, me.mismatch FROM db_match me JOIN
seq_reads read_id ON read_id.read_id = me.read_id LEFT JOIN reads2expt
reads2expts ON reads2expts.read_id = read_id.read_id LEFT JOIN seq_reads
read_id_2 ON read_id_2.read_id = reads2expts.read_id JOIN db_sequences
seq_id ON seq_id.seq_id = me.seq_id WHERE ( ( abundance > ? AND expt_id
= ? AND type = ? ) ): '1', '1', 'hairpin-miRNA'
However, if I want to get the value for the 'abundance' column for each
row I need to use a search_related() step, which dramatically slows down
the process.
Read up on this:
http://search.cpan.org/~ribasushi/DBIx-Class-0.08103/lib/DBIx/Class/Manual/Cookbook.pod#Using_joins_and_prefetch
Ah, right. Thanks.
The prefetch is working as far as I can tell, but can't seem to access
the relationship. e.g.
my $schema = DB::Schema->connect();
my @data;
my @rs = $schema->resultset('DbMatch')->search(
{
expt_id => $dataset,
abundance => {'>' => 1},
type => 'hairpin-miRNA'
},
{
join => [
{ # join on seq_reads -> reads2expt tables
read_id => {
reads2expts => 'read_id'
}
},
'seq_id' # join db_sequences table. Need this in order to
filter by 'type'
],
prefetch => [
{
read_id => {
reads2expts => 'read_id'
}
},
]
},
);
The SQL from this is:
SELECT me.seq_id, me.read_id, me.search_id, me.match_start,
me.match_end, me.query_start, me.mismatch, read_id.read_id,
read_id.length, read_id.seq, reads2expts.expt_id, reads2expts.read_id,
reads2expts.abundance, reads2expts.read_name, read_id_2.read_id,
read_id_2.length, read_id_2.seq FROM db_match me JOIN seq_reads read_id
ON ( read_id.read_id = me.read_id ) LEFT JOIN reads2expt reads2expts ON
( reads2expts.read_id = read_id.read_id ) JOIN seq_reads read_id_2 ON (
read_id_2.read_id = reads2expts.read_id ) JOIN db_sequences seq_id ON (
seq_id.seq_id = me.seq_id ) WHERE ( abundance > ? AND expt_id = ? AND
type = ? ) ORDER BY reads2expts.read_id: '1', '1', 'hairpin-miRNA'
But I can't get the syntax right for accessing the 'abundance' column.
According to the docs, something like this should work:
foreach my $hit (@rs) {
push @data, [ $hit->get_column('seq_id'), $hit->match_start,
$hit->read_id->reads2expts->read_id->abundance ];
}
I've tried lots of different variations of the above to access the
'abundance' data, but I always get a 'Can't locate object method...'
error. I really can't see where I going wrong.
Thanks very much for your continued help.
Cheers,
Chris
_______________________________________________
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]
