posts_where Fails with More than One Custom Field in Query

3 Views

Querying either of the 2 fields individually gives the expected result (posts are displayed).

But, when both fields are included in the query (ex = ‘foo’ and ‘bar’ shown in below example), then no posts are displayed.

Read More
function do_stuff( ) {
    global $wpdb;
    return "
    AND ($wpdb->postmeta.meta_key = 'foo' 
    AND $wpdb->postmeta.meta_value = '1') 
    AND ($wpdb->postmeta.meta_key = 'bar' 
    AND $wpdb->postmeta.meta_value = '1')";
}

add_filter( 'posts_where', 'do_stuff' );

Here is a print_r of the WP_Query: 

[query] => Array
    (
        [post_type] => testPostType
        [pagination] => 1
        [posts_per_page] => 10
        [paged] => 1
    )

[request] => SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id  WHERE 1=1 
AND (wp_postmeta.meta_key = 'foo'
AND wp_postmeta.meta_value = '1')
AND (wp_postmeta.meta_key = 'bar'
AND wp_postmeta.meta_value = '1')  ORDER BY wp_posts.post_date DESC LIMIT 0, 10
[posts] => Array
    (
    )

)

Have I made a logic error in the sql? How can I get the posts to display as expected?

Post Views: 3

Related posts

1 comment

  1. Think about your query. You are asking that $wpdb->postmeta.meta_key be both “foo” and “bar”. That will never happen. If you ran this in a context where you’d get to read MySQL debugging data (PhpMyAdmin’s SQL console will do this) you would probably see a warning about an “impossible where clause”. You need to JOIN on the post meta table for one of the values, but as near as I can tell WP_Query will do this too. 

    $args = array(
  'post_type' => 'post',
  'meta_query' => array(
    array(
      'key' => 'foo',
      'value' => '1',
    ),
    array(
      'key' => 'bar',
      'value' => '1',
    )
  )
);
$query = new WP_Query( $args );

    If you look at the SQL for that query– var_dump($qry->request);— you will see the JOIN I am talking about.

    You can do the same with a filter on pre_get_posts.

    function set_post_meta_wpse_111456($qry) {
  $qry->set(
    'meta_query',
    array(
      array(
        'key' => 'foo',
        'value' => '1',
     ),
     array(
       'key' => 'bar',
       'value' => '1',
     )
  );
}
add_action('pre_get_posts','set_post_meta_wpse_111456');

    WARNING: As written above, that will effect every query on the site so you do need to add conditions to limit where that runs.

Comments are closed.