Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

up vote 3 down vote accepted

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.

share|improve this answer

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.

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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