Yii Framework Forum: Filter For Null In Cdbcriteria - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Filter For Null In Cdbcriteria Rate Topic: ***** 1 Votes

#1 User is offline   FlavorFlav 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 40
  • Joined: 03-May 11

Posted 24 October 2012 - 07:39 PM

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?
0

#2 User is offline   redguy 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 774
  • Joined: 02-July 10
  • Location:Central Poland

Posted 25 October 2012 - 01:16 AM

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

red
0

#3 User is offline   FlavorFlav 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 40
  • Joined: 03-May 11

Posted 25 October 2012 - 02:29 AM

View Postredguy, on 25 October 2012 - 01:16 AM, said:

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



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)
0

#4 User is offline   Rajith R 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 865
  • Joined: 20-April 11
  • Location:India

Posted 25 October 2012 - 02:46 AM

check this

$this->find(array(
     'condition' => 'city = :city',
     'params' => array(':city' => NULL)
));



this will work

whats the problem with $city ?

IS NULL not needed.
Rajith Ramachandran,
Wiwo inc.
| Mobile: 919995504508
0

#5 User is offline   redguy 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 774
  • Joined: 02-July 10
  • Location:Central Poland

Posted 25 October 2012 - 02:55 AM

View PostFlavorFlav, on 25 October 2012 - 02:29 AM, said:

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)

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

red
0

#6 User is online   Keith 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,498
  • Joined: 04-March 10
  • Location:UK

Posted 25 October 2012 - 03:05 AM

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...
0

#7 User is offline   FlavorFlav 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 40
  • Joined: 03-May 11

Posted 25 October 2012 - 06:30 AM

View PostKeith, on 25 October 2012 - 03:05 AM, said:

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...


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

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

0

#8 User is online   Keith 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,498
  • Joined: 04-March 10
  • Location:UK

Posted 25 October 2012 - 06:33 AM

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.
0

#9 User is offline   FlavorFlav 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 40
  • Joined: 03-May 11

Posted 25 October 2012 - 06:41 AM

View PostKeith, on 25 October 2012 - 06:33 AM, said:

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.


oh you are right.. thanks
0

#10 User is offline   phtamas 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 527
  • Joined: 26-February 11
  • Location:Mezőtúr, Hungary

Posted 25 October 2012 - 09:02 AM

View PostFlavorFlav, on 25 October 2012 - 06:30 AM, said:

so i tihnk i will use this:
$this->find(array(
 	'condition' => $city === null ? 'city IS NULL' : 'city = :city',
 	'params' => array(':city' => $city)
));



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



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

#11 User is offline   Aliko 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 8
  • Joined: 29-November 13

Posted 09 July 2014 - 12:19 PM

View Postredguy, on 25 October 2012 - 02:55 AM, said:

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



thank you!
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users