MySQL SELECT where boolean field is NULL or false

Question

OK I must be missing something really simple here. I just want to return all records from a table where the user_id matches (easy!) and the 'paid' field is either NULL or 0. My 'paid' field is a TinyInt (1).

My CakePHP model code is:

$workingRecord = $this->find('first',array(
    'conditions'=>array(
        'Subscription.user_id'=>$userId,
        array('not' => array('Subscription.paid' => true)) // Not True, to catch both false or NULL values
    )
));

The SQL generated by CakePHP is like this:

SELECT `Subscription`.`id`, `Subscription`.`paid` FROM `subscriptions` AS `Subscription` WHERE `Subscription`.`user_id` = 3 AND NOT (`Subscription`.`paid` = '1') LIMIT 1

Common sense would say this should work. The problem is that that SQL will return rows containing 0 in the 'paid' column, but will never return NULLs.

Is there any way to return zero's and NULLs in the one stroke, without using 'or'?

Thanks in advance.

Answer 1

When you left join a table with a boolean field, the result set may contain a NULL value even though the joined table's schema does not permit it.

So as a general case I would suggest:

where `Subscription`.`paid` = 0 or `Subscription`.`paid` IS NULL