CActiveRecord Find By Attributes Where Field Is Null

Question

In Yii 1.1.*, how to find all data (via CActiveRecord implementation) where an attributes is NULL, kinda like:

Foo::model()->findAllByAttributes(['bar' => 'baz', 'qux' => [null, '']]);

It DOES NOT work because it produces query: WHERE (bar = 'baz') AND (qux IN (, ''))

I want to find all Foo records where:

• "bar" field equals with "baz"
• AND qux field IS NULL or equals with empty string

I can do it with findAll, but how about if I want to use findAllByAttributes method? Thanks.

Answer 1

You can pass the condition for quz as an additional parameter into findAllByAttributes:

Foo::model()->findAllByAttributes(['bar' => 'baz'], "quz IS NULL OR quz = ''")

You can't use IN with null values unless you replace CDbCommandBuilder with your own implementation. CActiveRecord::findAllByAttributes calls CDbCommandBuilder::createColumnCriteria which in turn calls CDbCommandBuilder::createInCondition if the column values are an array.

From the source code values are cast into the column type and quoted afterwards and passed through implode resulting in the null being treated as php's null not mysql's null:

foreach($values as &$value)
{
    $value=$column->typecast($value);
    if(is_string($value))
        $value=$db->quoteValue($value);
}
...
return $prefix.$column->rawName.' IN ('.implode(', ',$values).')';

Answer 2

Something like:

$cityModel = Cities::model()->findAllByAttributes(array("citny_name"=>"d"), "state_id IS NULL OR state_id = ''");

The executed query:

SELECT * FROM `cities` `t` WHERE `t`.`citny_name`=:yp0 AND
(state_id IS NULL OR state_id = ''). Bound with :yp0='d'