Complex meta query with 3 keys

3 comments

1. The culprit

   The culprit of the matter is meta queries not supporting different and/or nested relations – a shortcoming by the way, that has driven me nuts before as well. In a recent instance with a search scenario also.

   What you want to do simply cannot be accomplished with WP_Query as well as one loop alone.
   As you appear to have noticed, whether you place the sorting key in the meta_query array or outside of it as a general query argument does not make a difference. If you set the meta query relation to OR and specify a meta_key anywhere of the query args without setting the accompanying meta_value parameter, the query will always return at least all posts where that meta_key is set.
   By the way and for the sake of completeness: When you use a single meta_query with != as a value for meta_compare, the query will return all results with the meta_key set and not equal to the given meta_value – it will not return any posts that do not have the meta_key used at all. Another point where meta queries fail.

   Solution 1

   I see two options. For one, you could omit the is_sponsored meta key from the query, omit pagination as well, get the correct posts and do the sorting with a second instance of WP_Query, passing it the filtered post IDs via the post__in parameter:

   $sfp_search_args = array(
       'sfp_complex_search' => 'yeap', 
       'tax_query' => array( array( 'taxonomy' => 'sfp_post_category', 'terms' => $term_id ) ),
       'post_type' => 'sfpposts',
       'post_status' => 'publish',
       'meta_query' => array(
           'relation' => 'OR', 
           array( 'key' => 'pd_city', 'value' => $sfp_search_meta, 'compare' => 'LIKE' ), 
           array( 'key' => 'pd_country', 'value' => $sfp_search_meta, 'compare' => 'LIKE' )
       )
   );
   
   $sfp_search = new WP_Query( $sfp_search_args );
   $post_ids = array();
   while ( $sfp_search->have_posts() ) : $sfp_search->next_post();
       $post_ids[] = $sfp_search->post->ID;
   endwhile;
   
   $sfp_ordered_args(
       'post__in' => $post_ids,
       // note that 'showposts' is deprected
       'posts_per_page' => (int)$per_page, 
       'paged' => $paged,
       'meta_key' => 'is_sponsored',
       'order' => 'DESC',
       'orderby' => 'meta_value_num date'
   );
   $sfp_ordered = new WP_Query( $sfp_ordered_args );
   while ( $sfp_ordered->have_posts() ) : $sfp_ordered->next_post();
       // display posts
   endwhile;
   

   Note, that the $orderby paramter of WP_Query will take multiple values separated by a space. Your search modification might be more complex than necessary.

   Solution 2

   Since I like your idea of var_dumping the query object’s request property, let me fire a quick – and, note, untested – secondary suggestion:

   If you slightly modified the given SQL by changing the logical operator of OR mt2.meta_key = 'is_sponsored' to AND and moving it accordingly, you could pull the posts with $wpdb:

   $sfp_post_ids = $wpdb->get_col(
       "
       SELECT wp_posts.ID 
       FROM wp_posts 
       INNER 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 AS mt1 ON (wp_posts.ID = mt1.post_id) 
       INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id) 
       WHERE 1=1 
       AND ( wp_term_relationships.term_taxonomy_id = $term_id ) 
       AND wp_posts.post_type = 'sfpposts' 
       AND (wp_posts.post_status = 'publish') 
       AND ( (wp_postmeta.meta_key = 'pd_city' 
       AND CAST(wp_postmeta.meta_value AS CHAR) 
       LIKE '%$sfp_search_meta%') 
       OR (mt1.meta_key = 'pd_country' 
       AND CAST(mt1.meta_value AS CHAR) 
       LIKE '%$sfp_search_meta%') )
       AND mt2.meta_key = 'is_sponsored' 
       GROUP BY wp_posts.ID 
       ORDER BY CASE WHEN mt2.meta_value = 0 THEN 1 END, wp_posts.post_date DESC
       "
   );
   

   At this point you have two options as well:
   Either iterate over the $sfp_post_ids array with a simple foreach and pull the post data with get_post() individually within that loop, or, if you want the niceties of WP_Query – paging, template tags and so on – feed $sfp_post_ids to the post__in parameter as in Solution 1.

   Log in to Reply

2. This all happens because of the OR relation on meta_query and the way WordPress generates the actual query string. I ended up hooking into the posts_clauses filter to modify the where and orderby pieces of the query:

   public function wpse_68002_orderby_fix($pieces){
       global $wpdb;
       $pieces['where']  .= " AND $wpdb->postmeta.meta_key = 'your_meta_key'"; // <--- update here with your meta_key name
       $pieces['orderby']  = "$wpdb->postmeta.meta_value ASC";
       return $pieces;
   }
   

   Just add the filter before setting up your WP_Query object and then make sure to remove it after running your query to not affect other queries:

       add_filter( 'posts_clauses', 'wpse_68002_orderby_fix', 20, 1 );
       $query = new WP_Query($args);
       $result = $query->get_posts();
       remove_filter( 'posts_clauses', 'wpse_68002_orderby_fix', 20 );
   

   Remember to leave out the meta_key and orderby from the query args.

   Log in to Reply

3. That is complicated 🙂

   I was going to suggest that you may not need to have the array( 'key' => 'is_sponsored' ) value in the ‘meta_query’ array and that you could do this by adding a ‘meta_key’ to main array, but it looks like you’ve tried that. Did you get the same results?

   JOINs can make things complicated. You hooked into posts_orderby. Have you considered hooking into posts_fields and adding a subquery that would get you your meta_value?

   
(SELECT meta_value FROM $wpdb->postmeta WHERE meta_key = 'is_sponsored' AND post_id = {$wpdb->posts}.ID) as is_sponsored


   Log in to Reply