Hi,
I'm kind of new to DBIx::Class. and am attempting to find the union of two
ResultSets using the Helper::ResultSet::SetOperations module for a catalyst
application.
The two individual ResultSets I've created are:
my $ortholog_rs = $c->model('OrtholugeDB::Ortholog')->search(
{
analysis_id => { 'IN' => \@analysis },
inparalog => { '=' => 0 },
ing1_gene_id => { 'IN' => \@gene_ids },
},
{ rows => $limit, page => $pagenumber, }
);
my $ortholog_rs2 = $c->model('OrtholugeDB::Ortholog')->search(
{
analysis_id => { 'IN' => \@analysis },
inparalog => { '=' => 0 },
ing2_gene_id => { 'IN' => \@gene_ids },
},
{ rows => $limit, page => $pagenumber, }
);
where the only difference between the two is that I'm querying either the
ing1_gene_id OR ing2_gene_id field in an 'ortholog' table. If I execute them
separately, I get the expected number of results and my DBIC_TRACE output
resembles what you would expect:
SELECT me.ortholog_id, me.analysis_id, me.cluster_id, me.ing1_gene_id,
me.ing2_gene_id, me.outg_gene_id, me.inparalog, me.run_status, me.dist1,
me.dist2, me.dist3, me.ratio1, me.ratio2, me.ratio3, me.locfdr1, me.locfdr2,
me.class1, me.class2, me.class, me.inparalog1, me.inparalog2, me.isvalid FROM
ortholog me WHERE ( ( ing1_gene_id IN ( ? ) AND inparalog = ? ) ) LIMIT ?:
'122832353', '0', '20'
However, I was trying to set do a union of the two using
Helper::ResultSet::SetOperations using the following:
$ortholog_rs->union($ortholog_rs2)->all;
$c->stash->{orthologs} = $ortholog_rs;
it appears the query generated from the first resultset (ortholog_rs) is nested
within itself while the ortholog_rs2 looks fine.
SELECT me.ortholog_id, me.analysis_id, me.cluster_id, me.ing1_gene_id,
me.ing2_gene_id, me.outg_gene_id, me.inparalog, me.run_status, me.dist1,
me.dist2, me.dist3, me.ratio1, me.ratio2, me.ratio3, me.locfdr1, me.locfdr2,
me.class1, me.class2, me.class, me.inparalog1, me.inparalog2, me.isvalid FROM
(SELECT me.ortholog_id, me.analysis_id, me.cluster_id, me.ing1_gene_id,
me.ing2_gene_id, me.outg_gene_id, me.inparalog, me.run_status, me.dist1,
me.dist2, me.dist3, me.ratio1, me.ratio2, me.ratio3, me.locfdr1, me.locfdr2,
me.class1, me.class2, me.class, me.inparalog1, me.inparalog2, me.isvalid FROM
ortholog me WHERE ( ( ing1_gene_id IN ( ? ) AND inparalog = ? ) ) LIMIT ?, ?
UNION
SELECT me.ortholog_id, me.analysis_id, me.cluster_id, me.ing1_gene_id,
me.ing2_gene_id, me.outg_gene_id, me.inparalog, me.run_status, me.dist1,
me.dist2, me.dist3, me.ratio1, me.ratio2, me.ratio3, me.locfdr1, me.locfdr2,
me.class1, me.class2, me.class, me.inparalog1, me.inparalog2, me.isvalid FROM
ortholog me WHERE ( ( ing2_gene_id IN ( ? ) AND inparalog = ? ) ) LIMIT ?, ?)
me: '122832353', '0', '20', '20', '122832353', '0', '20', '20'
I've spent a lot of time trying to get it to work, including trying to inflate
the results using the DBIx::Class::ResultClass::HashRefInflator on the
documentation page (which I don't think is necessary for this example) but I'm
getting nowhere. I haven't been able to find an example online that would be
able to point me in the right direction.
If someone could suggest where I am going wrong, it would be much appreciated.
Thanks!
_______________________________________________
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]
