[Dbix-class] UNION, INTERSECTION, nested queries

[Fernan Aguero]

Hi,

I have a number of DBIx::Class 'queries' that users make by
using an HTML form (this is from Catalyst). 

And I store this queries in the users's 'history'.

In this history, users can combine queries: merge (boolean
OR/UNION), intersect (boolean AND/INTERSECTION), and
subtract.

To avoid overly complex DBIC constructs, what I'm
doing is: performing each query separately, retrieving the
list of ids of the objects and then using List::Compare to
get the result.

But now I want to go back and try to revisit this using DBIC
... but the question is: is it possible?



This is what I have (example data):

$query1->{where} = { name => 'me', age => '35', 'roles.role' => 'admin'};
$query1->{attr} = { join => 'roles', distinct => 1 };

$query2->{where} = { surname => 'smith', 'roles.role' => 'user'};
$query2->{attr} = { join => 'roles', distinct => 1};

etc.

And this is how I do the queries:
$rs = $c->model('users')->search( $query1->{where}, $query1->{attr} );


Now, say I'd like to do the UNION of query1 and query2. On
April 27th, Marc Mims suggested the following: create a result source for your query, like this:

package MySchema::Whatever;
use base qw/DBIx::Class/;

__PACKAGE__->table('DUMMY');
__PACKAGE__->add_columns(qw/a b c v/);
__PACKAGE__->result_source_instance->name(\<<'');
(select a, b, c, 'var1' as v from table1
where d='value'
union select a, b, c, 'var2' as v from table2
where d='value')

1;

and Oliver Rasnita confirmed that this worked for him.

Now, I can't do this because I don't know beforehand how the two
selects would look like ... these are generated dynamically
for each user.

I'm using DBIx::Class v0.07005 ... is this functionality
available? 

Any ideas, suggestions, tips, workarounds are appreciated,

Thanks!

Fernan