Query Multiple Post types each with own meta query

Question

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"/"_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?

Answer 1

Here is another approach with pre_get_posts.

• Use get_posts() ( x2 queries ) to get all the post ids of each post type according to its requirements. Add the fields parameter ('fields' => 'ids') to your query arguments to just fetch post ids as this is what we need, nothing else. This will drastically improve performance and will not be that tough on resources. So, you will end up with two arrays of post ids

• You now need to merge the two arrays with array_merge() to create a "super" array which will hold all the post ids

• You would want to look into saving this in a transient and flushing the transient when a post is updated, trashed, untrashed or published. This will ensure that do not comprise performance of your site.

• Now that you have an array of ID's fitting your needs, you can just pass that array of post ids to post__in in pre_get_posts

Example (and untested as this is only an illustration. Unfortunately I don't have time for proper coding)

function get_all_special_posts()
{
     /*
     * This is here were you would want to introduce your transient
     */
    $args1 = [
        'post_type' => 'post_type_1',
        'fields' => 'ids',
        'nopaging' => true,
        'meta_query' => [[ /*Your specific custom fields */ ]]
    ];
    $query1 = get_posts( $args1 );

    $args2 = [
        'post_type' => 'post_type_2',
        'fields' => 'ids',
        'nopaging' => true,
        'meta_query' => [[ /*Your specific custom fields */ ]]
    ];
    $query2 = get_posts( $args2 );

    $post_ids = array_merge( $query1, $query2 );

    /*
     * Set your transient here, you would want to store $post_ids in the transient
     */

    return $post_ids;
}

Remember to create a function to flush your transient on post publish, trash, untrash and update. Look at transition_post_status for this. Very versatile hook. Use $post->post_type to target your two desired post types only

add_action( 'pre_get_posts', function ( $q )
{
    if ( !is_admin() && $q->is_main_query() && $q->is_home() ) {
        $q->set( 'post__in', get_all_special_posts() );
    }
});

Just to note, I would first check whether get_all_special_posts() actually have a valid array and that it is not empty before passing it to post__in

Answer 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.