Filter For Null In Cdbcriteria

Yii 1.1 General Discussion
1

hi!

i have a mysql table like this:

id| name |_city

0_|John|_London

1_|Nick|_Berlin

2_|Eva|_<NULL>

3_|_Bryan|_London

4_|Jack|_London

5_|Jane|_<NULL>

the city-col can be null.

when i use cdbcriteria for filtering the records with city i have done something like this:




$this->find(array(

     'condition' => 'city = :city',

     'params' => array(':city' => $city)

));

now i want filter for a city (can be NULL!!).

examples:

  • $city=‘London’ result {John,Brian,Jack}

  • $city=‘Berlin’ result {Nick}

  • $city=NULL result {Eva,Jane}

the problem is, this does not work with NULL values becasue mysql use different syntax to check NULL-values:

  • normal => city="London"

  • with NULL => city IS NULL

any idea how i can do this in a nice way?

2

so why don’t you just write it in ‘condition’ expression:




$this->find(array(

     'condition' => 'city = :city OR city IS NULL',

     'params' => array(':city' => $city)

));
3

the problme with this OR is, i will get EVERYTIME the NULL-fields.

example:

$city=‘Berlin’ result {Nick,Eva,Jane}

Nick (city = :city) + Eva,Jane (city IS NULL)

4

check this


$this->find(array(

     'condition' => 'city = :city',

     'params' => array(':city' => NULL)

));

this will work

whats the problem with $city ?

IS NULL not needed.

5

ah, ok. so you could use conditional:




$criteria = $city == null ? array( 'condition' => 'city = :city', 'params' => array(':city'=>$city) ) : array( 'condition' => 'city IS NULL' );

$this->find($criteria);
6

No, in MySQL at least, null comparisons must be performed with the IS keyword. Using ‘=’, null compares against anything (including null) as null, so won’t return the records.

You could use this slightly hacky solution:




$this->find(array(

     'condition' => $city === null ? 'city IS :city' : 'city = :city',

     'params' => array(':city' => $city)

));

Otherwise, you could simply use two different find statements, dependent on whether $city is null.

EDIT:

Ah, ninja’d…

7

i was hoping for a more smart solution, but i think this your proposal is a good idea, thanks :smiley:

i do not really unterstand why MySQL use this strange NULL-value-handling

so i tihnk i will use this:




$this->find(array(

     'condition' => $city === null ? 'city IS NULL' : 'city = :city',

     'params' => array(':city' => $city)

));
8

The behavior is at least defined very clearly here.

I think the whole point is that NULL is undefined, so it makes no sense for a standard comparison to compare two NULL values as equal. It’s undefined, so the comparison is essentially undefined.

Also, your version isn’t quite right, because you’ll require no parameters for the null version and one for the non-null version, but you always provide one parameter. That’s why I bound the variable in both conditions.

9

oh you are right… thanks

10

Try




$this->findByAttributes(array('city' => $city));

It will both handle NULL values properly and do parameter binding automatically.

11

thank you!

12

I just looked at the class CDbCriteria (if you want to use CDbCriteria

the code says you can use

$criteria->addInCondition(‘city’, null);

The original class (snippet) is this:

[i]public function addInCondition($column,$values,$operator=‘AND’)

{


	if((&#036;n=count(&#036;values))&lt;1)


		&#036;condition='0=1'; // 0=1 is used because in MSSQL value alone can't be used in WHERE


	elseif(&#036;n===1)


	{


		&#036;value=reset(&#036;values);[/i]


		[b]if(&#036;value===null)


			&#036;condition=&#036;column.' IS NULL';


		else[/b]

…