[Dbix-class] Help finding union of two ResultSets using Helper::ResultSet::SetOperations

[Geoff Winsor]

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!