SAVE 25% when you purchase our Theme Bundle and Make Plus together. View bundle deal.

Querying posts with a false or NULL meta value

By Andy Adams on April 24, 2012

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?

Did you know that Surf Office, Postmatic, Yeah Dave, and over 700,000 small businesses run their websites with Make, our free WordPress page builder. Discover the Make page builder now.

$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.

8 Comments

  1. Mike

    Hi Andy, I’ve referenced your post in my wordpress.stackexchange.com question that I started and answered. ;)

    I’m also looking to do something similar for limiting the returned users from get_users, which uses a WP_User_Query. I can’t seem to find a hook for the join and where clause like posts_join or posts_where. Any ideas?

  2. Mike

    Nvm, figured it out. Sorry about all the posts!

    add_filter( 'pre_user_query', 'user_meta_callback' );
    function user_meta_callback( $user_query ) {
    
    	global $wpdb;
    
    	$user_query->query_from .= "
    		LEFT JOIN    " . $wpdb->usermeta . " AS um 
    		ON           (" . $wpdb->users . ".ID = um.user_id AND 
    				um.meta_key = 'key_here') ";
    
    	$user_query->query_where .= "
    			AND ((um.meta_key = 'key_here' AND um.meta_value = 'Value Here')
    				OR um.umeta_id IS NULL) ";
    
    }
    
  3. Andy Adams

    Hey Mike,

    Thanks for the mention! I’m glad it’s helped others already. Sometimes I write stuff like this and wonder if *anybody* finds it useful :).

  4. Andy Adams

    Awesome. I really appreciate you stopping by and letting me know! I’ll write technical articles with confidence from now on :).

Comments are closed.