[FIXED] How to compare against fields that are `null` (empty)?

Issue

I have a table in a CakePHP 3 application called downloads which has a column called master. The field type is set to TINYINT(1)

I can find any records where downloads.master == 1 like this:

$query = $this->Downloads->find()->where(['master' => true]);

But Cake won't let me query for ones where downloads.master !== 1. None of these work, and all return an empty array/object when the query is executed:

$query = $this->Downloads->find()->where(['master' => false]);
$query = $this->Downloads->find()->where(['master' => 0]);
$query = $this->Downloads->find()->where(['master' => null]);
$query = $this->Downloads->find()->where(['master' => '']);

What do you use as the condition to make this possible? My thinking was that it should be false since that's the opposite to true, but as with most things in CakePHP 3 they like to make it more complicated than necessary...

I've examined the records in my table using phpMyAdmin and there are indeed both records where master == 1 and master == null so it's not a case of there's zero results to return.

Solution

A column being NULL is not the same as being 0 (ie false-ish from the point of view of the ORM in case of a boolean-ish column type). If you want to compare against NULL, then you must issue a query with IS NULL, which is a SQL/DBMS requirement, not a CakePHP requirement.

CakePHP however requires you to be specific about what you want to do, as passing null does not neccesarily have to mean that you want to compare against SQL NULL, depending on the context.

Long story short, use the IS operator:

where(['master IS' => null])

Similarly use IS NOT for a negated condition. You can also pass user input as the value, the ORM will test the value and convert the IS and IS NOT operators into = and != respectively in case a non-null value is being passed.

See also

• Cookbook > Database Access & ORM > Query Builder > Automatic IS NULL Creation

Answered By - ndm