Querying posts with a false or NULL meta value

As I was working on our latest theme, I came across a situation where I needed to filter out posts that had a specific meta key that either never was set (NULL) or was false. I started down the route of using the meta_query argument to WP_Query.

$query_args = array(
    'meta_query' => array(
        array(
            'key' => 'my_key',
            'value' => false,
            'type' => 'BOOLEAN'
        )
    )
);

This works for the false case, but not for the NULL case. As I did a bit of searching, I found this core ticket which is going to allow searching for NULL meta values in WordPress 3.4. However, I had 2 problems with this solution:

1. WordPress 3.4 isn’t out yet (duh) and we need to support a couple of versions back, anyhow.
2. I needed to modify the main query. Tacking on items to the meta_query is error prone once someone else (a plugin, for example) tries to modify it as well. Specifically, the relation argument causes issues:

$query_args = array(
    'meta_query' => array(
        'relation' => 'OR',
        array(
            'key' => 'my_key',
            'value' => false,
            'type' => 'BOOLEAN'
        ),
        array(
            'key' => 'my_key',
            'compare' => 'NOT EXISTS'
        )
    )
);

The above code would work as long as no other plugin needed to use the meta_query. But what if a plugin tried to modify the query args with their own keys and values?

$plugin_query_args = array(
    'meta_query' => array(
        'relation' => 'AND',
        array(
            'key' => 'their_first_key',
            'value' => true,
            'type' => 'BOOLEAN'
        ),
        array(
            'key' => 'their_second_key',
            'value' => true,
            'type' => 'BOOLEAN'
        )
    )
);

You’ll notice the relation values would conflict, since WordPress only allows one meta_query at a time. That means we need another method to check for false or NULL values. After a bit of digging around, I pieced together this SQL-based solution:

add_filter( 'posts_join', 'lets_modify_the_posts_join' );
add_filter( 'posts_where', 'lets_modify_the_posts_where' );

function lets_modify_the_posts_join( $clause='' ) {
    global $wpdb;

    // We join the postmeta table so we can check the value in the WHERE clause.
    $clause .= " LEFT JOIN $wpdb->postmeta AS my_postmeta ON ($wpdb->posts.ID = my_postmeta.post_id AND my_postmeta.meta_key = 'my_key') ";
    
    return $clause;
}

function lets_modify_the_posts_where( $clause='' ) {
    global $wpdb;

    // Check whether the value is false or NULL. If it is neither, then we want to filter it.
    $clause .= " AND ( (my_postmeta.meta_key = 'my_key' AND CAST(my_postmeta.meta_value AS CHAR) = '') OR my_postmeta.meta_id IS NULL ) ";

    return $clause;
}

What we’re doing here is adding a JOIN statement to the query that joins the wp_postmeta table on the key “my_key”. We join the table as “my_postmeta” in the example.

Then, we’re adding a condition to the WHERE clause to check if the my_postmeta.meta_value is either false or NULL.

I hope this is useful to somebody out there. If I’ve overlooked some easier way to do this, let me know in the comments – thanks!

Enjoy this post? Read more like it in From the workshop.