merging query objects/arrays

[youngmicroserf]

As multiple post type installations proliferate, I assume more and more people will come across a situation (like me) in which they need to display two (or more) sets of content units, say posts, and post-type "audio" in one stream - say on the main blog page. As long as the only condition is the post type itself, that will work fine. However, if those two sets need to be selected using different conditional criteria, say a category in one case, and a custom field in the other, then the current query_posts options will not be sufficient.

While it is possible to retrieve multiple arrays via get_posts and merge them, then sort them using cusomt php, such a solution will break the paged navigation.

If such situations will - as I assume - become more likely as more people use multiple post types and additional variables to specify content, I think it would be useful to be able to combine multiple queries into a meta query that will sort the combined object according to a common sorting variable, say date, and thus preserve the paged navigation.

[Denis-de-Bernardy]

So... In other word, add SQL UNION support?...

[scribu]

Can you do a global sort on UNIONs ?

[Denis-de-Bernardy]

Replying to scribu:

Can you do a global sort on UNIONs ?

Yes, with a subquery.

SELECT *
FROM (
  SELECT ...
  UNION
  SELECT ...
) as stmt
ORDER BY ...
LIMIT N

It's a bit slow, since it'll need to quick sort or heap sort the result, but it'll still be faster and more reliable than pulling the X results of N queries, in order to drop dups and use php to sort the top X results out of the resulting merged set. Better let SQL do it than re-invent the union statement... As a bonus, using an SQL UNION allows to extract stats, i.e. the total number of rows.

Pushing this to future pending a patch, though, because it's an extremely messy proposition -- even with an ORM.

[Denis-de-Bernardy]

While we're at it: if this ever gets implemented, supporting SQL EXCEPT is only a tiny bit more work once SQL UNION is done.

[wonderboymusic]

This would be a fun lil project for someone - not moving to 3.7 though