[Dbix-class] UNION, INTERSECTION, nested queries

[Fernan Aguero]

+----[ Jess Robinson <castaway at desert-island.me.uk> (24.May.2007 05:01):
|

[snipped]

| >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?

Because I want to sort the result of the UNION in some
meaningful way?

I'd like to simplify my code, be it the code in the
controller or in the template (the outputting code). So
instead of having to run each query, get the items, and try
to sort them in code do something like:

$rs = $model->search(
	[ -union => 
		{ $query1->{where}, $query1->{attr} },
		{ $query2->{where}, $query2->{attr} },
		etc.
	],
	{ order_by => 'score DESC' }
);


Of course being able to use -union, -intersection, -minus,
interchangeably.

That would be an elegant solution.

I was just wondering whether this syntax/functionality is
available, being worked on, planned, drafted, or already
discussed and discarded?

I'm not familiar with the DBIC internals (or those of
SQL::Abstract), but I volunteer myself to the task if
someone is willing to mentor me on this.

This is, of course, supposing that this sounds like a good
idea. I'd be glad to continue doing my own work otherwise :)
 
| 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.

Thanks for the tip!
 
| Jess
|
+----]

Fernan