posts_where Fails with More than One Custom Field in Query

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?

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.