Already a customer? Please sign in.

Querying posts with a false or NULL meta value

by Andy Adams on April 24, 2012 / 8 comments

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.

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.