[Dbix-class] UNION, INTERSECTION, nested queries

[Jess Robinson]

On Wed, 23 May 2007, Fernan Aguero wrote:

> 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} );
>

Unless it's somehow really important to do the UNION on the DB itself, why 
not just run both queries one after the other, and make your results 
outputting code be able to handle multiple resultsets?

As for intersections, you can do this:
$rs = $c->model('users')->search(
    $query1->{where}, $query1->{attr} )->search(
    $query2->{where}, $query2->{attr} );

and everything should just DWIM.

Jess