CODEWITHSUNDEEP
mysqlyiiyii-cactiverecordcactiverecord
krisanalfa
krisanalfa

Reputation: 6438

CActiveRecord Find By Attributes Where Field Is Null

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:

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

Upvotes: 1

Views: 2154

Answers (2)

Criesto
Criesto

Reputation: 1991

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'

Upvotes: 3

topher
topher

Reputation: 14860

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).')';

Upvotes: 1

Related Questions