MySQL SELECT where boolean field is NULL or false

Question

OK. I must be missing something very simple here. I just want to return all the records from the table where user_id matches (easy!) And the fields are "paid", either NULL, or 0. In my paid field there is TinyInt (1).

The code for my CakePHP model 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 ) )); 

CakePHP generated SQL looks 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 that this should work. The problem is that SQL will return rows containing 0 in the paid column, but will never return NULL.

Is there a way to return zero and NULL in one clock cycle without using 'or'?

Thanks in advance.

Answer 1

If your field is TINYINT (1), you should not have any NULL values. . If you do, run a quick query to replace any default NULL values, and set the database field to a default value of 1 or 0.

Then your code should be:

 $workingRecord = $this->find('first',array( 'conditions'=>array( 'Subscription.user_id'=>$userId, 'Subscription.paid' => 0 ) )); 

The reason why it does not work, do you think:

According to the MySQL Documentation on "NOT" :

Logical NOT. Computes the value 1 if the operand is 0, 0, if the operand is not zero, and NOT NULL returns NULL.

So - in your case you say "NOT 1" , which MySQL translates to " = 0" .

Answer 2

When you leave a table join with a logical field, the result set may contain NULL, even if the joined table schema does not allow it.

So, in the general case, I would suggest:

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