Complex meta query with 3 keys

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.