1

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

4
  • That would be an extremely heavy slow and expensive query to run. show_is_creation and other tags should be stored as a custom taxonomy, which would make your question significantly easier to answer, and the query possibly hundreds of times quicker. Otherwise what you're asking for is going to be very difficult to do, and very heavy on the database server. Likewise with your season value, it should be a custom taxonomy named season. If you ever need to sort/filter posts by something, store the data you're filtering by in a custom taxonomy not post meta
    – Tom J Nowell
    Jun 23, 2020 at 10:32
  • show_is_creation is just a boolean flag. IMO this should not be a taxonomy. Jun 23, 2020 at 12:19
  • I disagree as you're searching and filtering for posts via their values. As a reviewer of millions of lines of enterprise level code running billions of page views, I've seen cheaper queries bring down much more powerful servers. Use a private taxonomy and give posts either show_is_creation or show_is_not_creation. This allows you to make huge performance gains, and makes your queries much simpler to write. Note I'm not advising you make seperate custom taxonomies for each and every value, you only need a single taxonomy then individual terms.
    – Tom J Nowell
    Jun 23, 2020 at 17:26
  • Otherwise what you want to do in this Q will require complex SQL, multiple queries, and will have a big performance impact, resulting in a very slow multi-second query, and a significant reduction in the number of concurrent visitors your site can handle
    – Tom J Nowell
    Jun 23, 2020 at 17:27

1 Answer 1

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
1
  • Perfect answer ! thanks ! Jun 24, 2020 at 7:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.