Complex meta query with 3 keys

Question

I think the problem essentially relates to sql query structure and I am not an expert....

I need to search for posts (custom post type) by 2 parameters:

1. pd_city

2. pd_country

Please note that meta_query relation is 'OR' so if either of above two is LIKE we should have some results.

Third key (is_sponsored) is used to sort posts! It can be 1 or 0 and posts whose "is_sponsored" value equals to 1 should be listed on top.

So here's the WordPress thing:

    $sfp_query_args = array(
        'sfp_complex_search' => 'yeap', 
        'tax_query' => array( array( 'taxonomy' => 'sfp_post_category', 'terms' => $term_id ) ),
        //'meta_key' => 'is_sponsored',
        'post_type' => 'sfpposts',
        'post_status' => 'publish',
        'showposts' => (int)$per_page,
        'paged' => $paged, 
        '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' ), 
                array( 'key' => 'is_sponsored' )
                )
    );
$sfp_search = new WP_Query( $sfp_query_args );

I also need to filter results with "posts_orderby" in order to get those sponsored to the top:

add_filter( 'posts_orderby', 'sfp_modify_search' );
function sfp_modify_search( $orderby ) {
    if( !is_admin() && is_page( $this->options[ 'sfp_page_entries_search' ] ) ) {
        global $wpdb;
        $orderby = " CASE WHEN mt2.meta_value = 0 THEN 1 END, $wpdb->posts.post_date DESC ";
    }
    return $orderby;
}

The real problem relies in fact that with this query ALL POSTS from "sfp_post_category" are returned, not only those who match "pd_city" or "pd_country" because ALL POSTS HAVE "is_sponsored" meta key (and value set to 1 or 0). Once again: "is_sponsored" is needed for sorting!

When var_dump

var_dump( $sfp_search->request );

...WordPress' sql looks like this:

SELECT SQL_CALC_FOUND_ROWS 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 IN (77) ) 
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 '%something%') 
OR (mt1.meta_key = 'pd_country' 
AND CAST(mt1.meta_value AS CHAR) 
LIKE '%something%') 
OR 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 
LIMIT 0, 10

How do I eliminate all posts that do not match "pd_city" or "pd_country" from results?

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

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

Answer 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