4

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?

  • I am pretty sure this is beyond simple query arguments. I have written several answers about merging queries and a lot about posts_where. One of the two should do it. I love a good WP_Query question, but don't have time to write an answer. Do you have the exact SQL you need to query to generate? – s_ha_dum May 8 '15 at 14:13
  • Thank you both for the great comments. I have some more research to do obviously. I don't have anything exact at this point was just kind of mulling it around in my head. – helgatheviking May 8 '15 at 16:23
  • 1
    I created the experimental Combined Query plugin on GitHub some time ago when I was playing with dynamic UNION queries generation. Maybe that can help in this case? – birgire May 10 '15 at 18:20
  • @birgire if you have time, it would be nice seeing this as an answer with some code and explanation. Most people don't read comments, and I really do feel that even though this is a plugin, it can be really useful answer as we get many questions combining queries. – Pieter Goosen May 10 '15 at 18:29
  • Sure if it makes sense as a solution for OP ;-) I just updated it so it can be easily used for main queries as well as secondary queries. ps: there are 5-6 examples on the GitHub page that I could try to adopt to similar cases as here. I also plan to test it further when I get some extra time ;-) @PieterGoosen – birgire May 10 '15 at 18:37
4

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.

4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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