Tax query clause inside a meta query clause?

Question

I need to run the following query via a WP_Query object:

SELECT wp_posts.* FROM wp_posts  
LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) 
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )  
INNER JOIN wp_postmeta mt1 ON ( wp_posts.ID = mt1.post_id )  
WHERE 1=1  
AND 
(
  (
    wp_term_relationships.term_taxonomy_id IN (47) /* current season */
    AND wp_postmeta.meta_key = 'show_is_creation' AND wp_postmeta.meta_value = '1'
  ) 
  OR (
    mt1.meta_key = 'show_in_ontour' AND mt1.meta_value = '1'
    AND wp_postmeta.meta_key = 'show_is_creation' AND wp_postmeta.meta_value = '1'
  )
) 
AND wp_posts.post_type = 'show' 
AND ((wp_posts.post_status = 'publish')) 

GROUP BY wp_posts.ID ORDER BY wp_posts.menu_order ASC;

So, I need to run a Tax query inside a Meta query clause. This does not seem to be possible with the 'meta_query' argument, something like this:

$meta_query = [
     'relation' => 'OR',
     [
       'relation'           => 'AND',
       'current_season_clause' => [
         'tax_query' => [
           'taxonomy' => 'season',
           'operator' => 'IN',
           'terms'    => [47],
         ],
       ],
       'is_creation_clause' => [
         'key'     => 'show_is_creation',
         'compare' => '=',
         'value'   => 1,
       ],
     ],
     [
       'relation'           => 'AND',
       'is_creation_clause' => [
         'key'     => 'show_is_creation',
         'compare' => '=',
         'value'   => 1,
       ],
       'is_ontour_clause'   => [
         'key'     => 'show_in_ontour',
         'compare' => '=',
         'value'   => 1,
       ],
     ],
 ];

What would be the best way to achieve this? (without running a raw SQL query). Should I use a filter to modify the WHERE statement ?

thanks

Answer 1

So, I need to run a Tax query inside a Meta query clause. This does not seem to be possible with the 'meta_query' argument, something like this:

Correct, what you want to do is not possible using the parameters of WP_Query while keeping your data stored as is. The query is already very expensive/slow to run, and if you could add in the taxonomy term query it would be even more expensive.

Instead, these values should never have been stored as post meta, they should have been stored as taxonomy terms. You can do this with a private/hidden taxonomy, and ACF can be told to use that taxonomy to store the values.

For example, a show_in taxonomy with terms such as on_tour or is_creation.

You can then test for this using a standard tax_query clause. You can also create secondary terms such as not_on_tour, allowing you to take the super expensive NOT IN queries and replace them with super cheap/fast versions.

Additionally, you can test using has_term, e.g.:

// should we show this post on the homepage?
if ( has_term( [ 'homepage' ], 'show_on' ) {
    //
}

This is significantly faster to query for than post meta, and on large sites this can be thousands of times faster to query for.

You can also automate some of this, e.g. on a lot of sites I've been involved with, a show_on_homepage term is added to every single post automatically using the save_post hook. The user can then remove that term and replace it with a hide_on_homepage term. This is also a great way to let users curate regions or zones of prominent pages, like a show_in_homepage_carousel term.

All that's needed to support this on the homepage, is a custom taxonomy named something like hidden_post_markers, and a snippet in functions.php like this:

add_action( 'pre_get_posts', function( $query ) {
    if ( $query->is_home() && $query->is_main_query() ) {
        $query->set( 'hidden_post_markers', 'show_on_homepage' );
    }
} );

As a general guide:

• If you need to find a post or query for posts by a piece of data, that data should be represented as a term in a taxonomy
• If a piece of data about a post is only accessed when you already know the post ID, such as when displaying a post, then use post meta
• You can store data in both
• You can store a simplified version in terms for querying, and a more detailed version in post meta for display
• You can store boolean data as terms, the simplistic method is to create two terms with _true and _false appended to the names, or just testing for the presence of the term
• The post meta table wasn't designed for searching for posts, expect major performance issues
• The taxonomy/term system was designed for searching for posts, filtering them in queries, etc.
• meta_query queries get more expensive as the number of posts rises, and it gets expensive/slow fast