How should I intercept the main query and inject custom join / order by / group by criteria

Question

I have a CPT that is a member of two separate taxonomies. On the taxonomy archive page for one of the taxonomies, I need to further group and sort on the second taxonomy like so:

mysite.com/tax1/term1/

tax2-term1 -post -post -post [see more]

tax2-term2 -post -post -post [see more]

... etc.

Better Example

To provide a more easily accessible example, consider the following CPT: Toys. And the two taxonomies: Colour and Age Group.

On the taxonomy archive page for mysite.com/colour/red, I need to further group on the second taxonomy, "age group" like so:

Red Toys

Ages: 0 - 12 months -toy -toy -toy [see more Red toys for ages 0 - 12 mo]

Ages: 12-24 months -toy -toy -toy [see more Red toys for ages 12 - 24 mo]

etc...

Intercepting query_posts

So, unless I'm mistaken and we've suddenly got the ability to sort on taxonomy (grouping would be ideal, but I could work around that), I'm thinking that a custom SQL query with my own tax joins will be the best way to get what I need. Correct me if I'm wrong. Most of the time I want to go the built-in WP way, I just don't see that working here.

How would you hijack the main query to still use The_Loop(TM) but with custom SQL? Or would you find a way to return an empty query and then just roll your own custom SQL loop?

Right now I'm leaning toward using pre_get_posts to set up a posts_clauses replacement of the join, groupby, orderby and maybe limits clauses, or even a wholesale replacement of the entire request using posts_request.

Is there an easier way?

Answer 1

If I understood you right...

• [A] Multi-Tax queries have the problem that they're doing a JOIN of the meta table for every term. So imho it's not the best idea to use the built in way.
• [B.1.a] I'd simply do a normal query with a one (main-)tax arg and then do the sorting depending on the meta values. So actually just calling get_post_custom() and merging it with a single post object during a loop and then appending it to a resulting array.
• [B.1.b] Then modifying the query through posts_clauses if some bits doesn't meet the requirements.
• [B.2] Start sorting for e.g. by name.
• [B.3] Then I'd go and run some foreach/etc loop on the resulting array.

The benefit of browser/php vs. DB isn't there so far (as long as the site hasn't got much hits). But if you start minifying css/js/html and caching the resulting stuff (and delivering the results from the hard disk), I think this would be the way I'd go.