Query Multiple Post types each with own meta query

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.

Related Posts:

  1. Nested meta_query with multiple relation keys
  2. Perform query with meta_value date
  3. Meta Query with date and time on the same Day before given time
  4. How do I create my own nested meta_query using posts_where / posts_join?
  5. How to query ‘posts_per_page’ to display a different blog posts index template?
  6. Order a query result by a numeric meta key even if it does not exist, but put the posts with meta key first
  7. WP_Query last five posts, simply ordered by meta_value
  8. Fetch Record based on meta key dates
  9. How to display multiple custom fields with the same meta_key in an ascending order?
  10. how to make members list directory through wordpress post custom meta key.
  11. How to provide meta_key array to wp_query?
  12. Show single posts date, in a page of posts
  13. Why doesn’t my WP Meta Query return any results?
  14. WP_Query Class custom field parameters
  15. How do i create a custom post query when the meta value is an array?
  16. ElasticPress is (aparently) messing with my search filters
  17. Query for current post
  18. Custom query to retrieve oldest post and retrieve others with date interval
  19. Orderby query does not work for custom fields even with meta query
  20. Custom WP_query and integrating into theme file
  21. query post based on comparison
  22. WP Query – Show custom posts only if user contain some user meta
  23. When to use WP_query(), query_posts() and pre_get_posts
  24. WP Query Args – Title or Meta Value
  25. Does tax_query really beats meta_query in all situations?
  26. Query WooCommerce orders where meta data does not exist
  27. Query Custom Meta Value with Increment
  28. WP_Query using meta_query with relation OR and orderby meta_value doesn’t work
  29. querying with custom meta field with meta_query
  30. Order by two meta keys
  31. multiple meta key but get server load is very high
  32. A WP_Query that will look for posts after 2 weeks ago OR with a certain meta value
  33. How to show only one post for each categories of taxonomy of custom post that contains a specific custom field
  34. Pass the same object to multiple widgets in a template with one query
  35. Use meta_query to display events by date in custom field
  36. How to show recent and upcoming birthdays using meta_query
  37. Query post with meta_query where date is not in future
  38. Next/Prev posts on same page
  39. Using WP_Query and WP_Meta_Query Outside of WordPress
  40. If two first numbers exist in wp_meta_query value
  41. WP_Query on custom field and order results
  42. How to query a custom post type with a taxonomy filter but display post type archive page?
  43. Tax query clause inside a meta query clause?
  44. Change query_posts to WP_Query in page but does not work
  45. Optional Meta Query
  46. How do I make a meta_query OR relation work? [closed]
  47. Serve specific “template” page at any URL that ends in its slug
  48. Meta_query on same meta key, with diffrenct values
  49. Can we return all category (not post) with Custom Query Filter? [closed]
  50. Extend product search with meta in WooCommerce
  51. Add class to first post in custom loop using post_class filter
  52. WP_Query with meta_query dosen’t return results
  53. How to get all post except particular category without breaking the paging
  54. How do I rewrite this loop as a new WP_Query style-loop?
  55. Filter Custom Taxonomy Posts
  56. Order post type by sum of two custom fields
  57. Use WP_Query or query_posts() or get_posts() for optimizing a site?
  58. How to set up hierarchical relationships without using plugins / meta query
  59. Meta query compare for ID’s greater than specific ID
  60. Is it possible to add relation between meta_query and tax_query?
  61. filter posts by meta key with pagination
  62. Performance concerns: index.php vs taxonomy-$taxonomy.php
  63. Custom template for password protected page
  64. Add AJAX “Load more” on custom query block
  65. WP_Query not returning correct result with meta_query parameter
  66. Custom query looking at multiple custom fields and properly sorting
  67. meta_query where the meta value is not the post title
  68. Elementor posts custom query based on tag [closed]
  69. Order WP_Query by meta_key priority when ‘OR’ relation used for multiple meta values
  70. Facing problem with tax_query results
  71. meta_query with meta values as serialize arrays
  72. Woocommerce Get Orders By Meta Value
  73. meta_query is overriding default search
  74. Multiple WP_Query args combinations according to post type
  75. WP_Query multiple value not working
  76. Use WP_query to match post types based on custom field values
  77. WP_User_Query Orderby Not Working
  78. Unusual high query of user meta data
  79. WordPress Query optimaization for slow query
  80. How to query most recent post for each author?
  81. How to filter on placeholder image
  82. How to search CPTs in draft using get_page_by_title()
  83. Meta query doesn’t remove placeholder escape before query
  84. WP Query Meta Query
  85. Query custom meta value – post view and date meta
  86. How can I modify standard search query to include also ACF custom fields values?
  87. WP_Query – show posts where meta value and user_email match
  88. Best way to Order Post in Home without a plugin
  89. WP_Query using meta_query with LIKE doesn’t return what it should
  90. Return a single custom post from multiple meta queries
  91. How to organize a WP_Query’s list of posts by category and display category title?
  92. Including ‘cat’ => $cat breaks meta_query
  93. Multiple loop for “featured” items returns wrong posts
  94. Query Posts to fetch Posts with Unique Tag
  95. Query string form $_GET[‘value’] is not working as meta value in wp_query
  96. query_posts problem – need help
  97. Can’t figure out query logic
  98. 294 Queries on Mainpage of WordPress
  99. How do I have multiple metaqueries inside one wordpress query
  100. Display only posts with thumbnails

Leave a Comment