Query Multiple Post types each with own meta query

by

I have 2 post types: lets say products and product_variations. I’d like to be able to modify the query via pre_get_posts to get all product with meta_keys "_visible" = "true" and "_available" = "true" AND all product_variations with meta_key "_featured" = "true".

The product_variations don’t have the meta_keys "_visible"https://wordpress.stackexchange.com/"_available" and vice versa, so a straight up AND meta_query returns no results. OR relation isn’t quite right either as two fields are AND related for one post type then OR related to the other post type.

To make that slightly more visual because I just realized how complex it is to write out:

**product**        
_visible = true      
_available = true

**product_variation**      
_featured = true

Is this possible via query args?
Is this possible via posts_where?
Do I need to run a separate query and then somehow merge the results?

2 Answers
2

Is this possible via query args?

I don’t think so.

Is this possible via 'posts_where'?

It is probably doable using some 'posts_*' filters, not only with 'posts_where'. Or maybe using 'posts_request' filter to completely oveeride the query.

Do I need to run a separate query and then somehow merge the results?

That would be the simplest choice, the most easy to customize e.g would be easy to use a limit for products and a different limit for product variations.

Moreover, this approach is not necessarily the worst on performance side, because not always 2 queries are slower than a single one if the latter is very complex.

The 4th alternative would be build a completely custom SQL. A (vaguely tested) query that might do the trick is the following:

$products_and_variations = $wpdb->get_results("
  SELECT * FROM (

    SELECT products.* FROM {$wpdb->posts} products
      LEFT JOIN {$wpdb->postmeta} meta1 ON meta1.post_id = products.ID
      LEFT JOIN {$wpdb->postmeta} meta2 ON meta2.post_id = products.ID
      WHERE products.post_type="product"
      AND products.post_status="publish"
      AND (meta1.meta_key = '_visible' AND meta1.meta_value="1")
      AND (meta2.meta_key = '_available' AND meta2.meta_value="1")
      GROUP BY products.ID

    UNION

    SELECT variations.* FROM {$wpdb->posts} variations
      LEFT JOIN {$wpdb->postmeta} meta3 ON meta3.post_id = variations.ID
      WHERE variations.post_type="product_variation"
      AND variations.post_status="publish"
      AND (meta3.meta_key = '_featured' AND meta3.meta_value="1")
      GROUP BY variations.ID
  )
  posts
    ORDER BY post_date DESC
    LIMIT 0, 100
");

You can notice:

  • how complex it is
  • how is not possible to use a limit for products and a different limit for variations: it is only possible to limit the merged results.

Before to use something like that, I would test its performance comparing them with the 2-queries approach and only use the custom query if there is any appreciable performance improvement.

Leave a Comment