Meta query with boolean true/false value

Question

I'm trying to show all rental properties, first by all properties that have not been rented, and then by all properties that are currently rented. There is a custom post type 'rent' with custom post meta for price rented (_price_rented) which is a checkbox (returns either true or false... true if it HAS been rented). I need to change the query to show all properties with the available (non-rented) properties appearing first and then the rented properties appearing.

Here is my query:

$ts_properties = new WP_Query( 
    array( 
    'post_type' => 'rent', 
    'paged' => $paged, 
    'posts_per_page' => -1,
    'meta_key' => '_price_rented',
    'orderby' => 'meta_value',
    'order' => 'DESC',
    'meta_query' => array(
        array(
        'key' => '_price_rented',
        'value' => false,
        'type' => 'BOOLEAN',
        ),
    ) 
) 
);

For some reason this query shows all properties that HAVE been rented. When I switch the value from 'false' to 'true' in the meta_query it doesn't show any properties.

So, then I thought, the return value is either false (for properties that ARE rented) or NULL (for properties that are NOT rented), but am not sure how to query for a NULL result (not false), I added a 'compare' argument to the meta_query and set the value to '!=' but that didn't work either.

EDIT: var_dump returns the following for an available, non-rented apartment: string(0) "" and for a non available, rented, apartment: string(1) "1"

Answer 1

TL;DR: This problem probably mostly happens when a boolean field is created as optional. You can fix it either by making it required, or using a more complex query to retrieve the default case.

More details:

There are two data representation problems going on here: one is which data values are being used to represent true/false and the other is whether or not the field is being stored at all if it is the default (usually false) value.

Part 1: I looked at the SQL generated by WP_Meta_Query for comparisons to true and false, and found that for true it substitutes '1' and for false '' (the empty string). So whatever you write into the database needs to agree with that if you are going do queries comparing to actual true and false values. In particular, you don't want to write '0' for false. It might be more foolproof to write and test for 0 and 1 instead (and many form builders do that). But check to see what is being written to the database and keep that in mind when building your query.

Part 2: Assuming that false is the default value, finding records whose value is true is easy:

... 'meta_key' => 'my_key', 'meta_value' => 1 (or true)

But the other side is challenging: there might be a false value, or there might not be any value at all. This can happen if the value was listed as optional in a form --- then so long as the user does not explicitly set it or change it, it will not be added to the database. Note that if you are only using get_post_meta it will work just fine this way: returning a false value and returning no value will accomplish the same thing.

But when you are using WP_Query, it isn't so easy. (Or if it is, I haven't figured out how yet).

You have two (or maybe three) options:

1. Make sure that the field is always explicitly initialized to a real value. In some form builders, you do this by making the field required and giving it a default value. Then you can test ...'meta_value' => 0 reliably.

2. Do two queries, the first which tests for a false value and the second which tests for no value. These can be combined into a single WP_Query like this:

   meta_query => array(
      'relation' => 'OR',
       array(
           'key'     => 'my_key',
           'value'   => 0,
           'compare' => '='
       ),
       array(
           'key'     => 'my_key',
           'compare' => 'NOT EXISTS',
       ),
   )
   

This is probably not an efficient query. Depending on a lot of factors, it might be better to return all objects and filter them in your own code.

3. It is possible to use 'no value' to mean false. To do this, whenever the value should be set to false, you have to delete the meta value instead of updating it.

In that case, a single 'NOT EXISTS' query will reliably return the correct objects. (I don't think many form builders or plugins support this behavior, so I'd only use it in purely custom code.)

Answer 2

WP_Meta_Query is a somehow "not so stable" part in core and if you don't pay verrry much attention it can easily break from being confused.

When you're doing a new WP_Query() and have meta_query => array() arguments or its single key/value pair equivalents, then new WP_Meta_Query() jumps in, instantly followed by parsing.

$this->meta_query = new WP_Meta_Query();
$this->meta_query->parse_query_vars( $q );

Allowed values

When you query meta data, then there's bool option. And if you'd use it, then it would fall back to CHAR, which the default value as the array of allowed values is:

'NUMERIC', 'BINARY', 'CHAR', 'DATE', 'DATETIME', 'DECIMAL', 'SIGNED', 'TIME', 'UNSIGNED'

where NUMERIC will be reset to SIGNED.

Debugging

There're numerous filters that can affect the post save process, so the first thing to do is checking the different values inside some loop:

var_dump( get_post_meta( get_the_ID(), '_price_rented', true ) );

Then, depending on the return value, you'll either have to use SIGNED, if the result is 0 or 1, or "true" or "false" if the result is a string. If it really is boolean, then I'd still suggest to use string just to make sure it passes $GLOBALS['wpdb'], which can only pass %s string and %d digit through.

Additional Notes

As I just updated the Codex entry for WP_Meta_Query today, I saw that there're lots of different outputs (adding numerous amounts of unneeded JOINS, which are discussed on Trac here and here without a single patch moved into core) possible. (Follow up ticket for AND parts here) Point is that it is possible to use a combination of meta_* arguments alongside the meta_query array and its subarrays. The result is pretty much unknown unless you dump it, so IMHO you're better off using either the one or the other way of adding inputs. Especially when you're only using meta_key, as this results in a "key only query" in some cases.

Solution

As pointed out in the comments:

(...) var_dump returns the following for an available, non-rented apartment: string(0) "" and for a non available, rented, apartment: string(1) "1"

Now the meta_query has to use

'meta_query' => array( 'relation' => 'OR', array(
    'meta_key'     => '_price_rented',
    'meta_value'   => '1',
    'meta_compare' => '='
) );

If you want to get the "non available, rented apartments" or use '!=' to retrieve the "non rented" apartments.

Note: Possible values for meta_compare are '=', '!=', '>', '>=', '<', '<=', 'LIKE', 'NOT LIKE', 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN', 'NOT EXISTS', 'REGEXP', 'NOT REGEXP' or 'RLIKE'. Default value is '='.

Answer 3

I faced the same problem and after an hour of search found the "NOT EXISTS" and "EXISTS" value ( only in WP >= 3.5 ). So no need to ask for a meta value just check if the meta_key exist :

'meta_key'     =>   '_price_rented'  ,
'meta_compare' =>   'NOT EXISTS'     ,

It's working perfectly for me.