Yesterday, moeffju asked in a comment to the post “Using an IN list in a condition” how to use NOT IN in SQL queries generated by CakePHP. A good question as the solution is not that obvious (at least if you use the array syntax for conditions).
First the obvious solution:
$this->User->findAll('User.id NOT IN (1, 2, 3)');
And here the solution using the array syntax:
$this->User->findAll(array('NOT' => array('User.id' => array(1, 2, 3))));
Why is this solution better? I don’t see a point …
the second one is useful if you have data in array form already. you do not have to transform them to text.
I think it’s also possible to use
$this->User->findAll(array(‘User.id’=>’NOT IN (1, 2, 3)’);
so the important part is that first parameter is column name.
I saw this in the groups and it’s working to me. I am with 1.2 of course.
Regards
Is there a way to do “OR” conditions with the array format?
Brandon, same syntax as NOT
@all: Thanks for your comments!
@Dejan: A second advantage of the array syntax is that the data is automatically escaped.
@Nik: Your approach doesn’t work here with the latest version of 1.2 from svn. The generated SQL is:
which causes an SQL error.
@Brandon: Yes, that’s possible. You can find an example at http://cakebaker.42dh.com/2007/01/23/sugar-icing-on-the-cake/
Thanks for the replies. It seems that Cake is quite consistent, which is a good thing. Despite being a Cake newbie, my first impulse on how to use NOT IN was correct. I’m curiously awaiting my first run-ins with AJAXy In-Place editing and building a Moderation system in Cake :)
@moeffju: Good luck with your application :)
If you have a few ids in your array it is fin to use IN, but if you have a considerable amount of data, try turning it to ors or ands, because mysql will optimize it better and your queries will run smoother.
Diego: Do you have a link for that? I just thought an IN list is syntactical sugar which behind the scenes is resolved to ORs.
I didn’t had any information other than experimentation to backup the assertion I made earlier when I posted the comment, but after some research and reading the MySql manual I found something similar maybe not the exact same thing but it may be of interest to some of us.
How to optimize IN/ANY Subquery
href=”http://dev.mysql.com/doc/refman/5.0/en/in-subquery-optimization.html
@Diego: Thanks for the link!
Oh well, development was fine as long as everything was read-only, but actually saving data and implementing a system where users can suggest edits and a moderator can accept or deny them… that sucks. Are there any good tutorials on saving huge models with lots of related models?
@moeffju: I am not aware of such a tutorial…
even if
$this->User->findAll(array(’User.id’=>’NOT IN (1, 2, 3)’);would work it makes no sense at all.
the point is, that we want to keep our 1,2,3 in an array and not transform them into text….
@ohcibi: Well, the second approach mentioned in the article uses an array ;-)
Since I wrote this article the find(‘all’) syntax has been introduced and so the modified example looks like:
Hope that helps!
This worked with what I am trying to do. I’m using
Which produces the following:
SELECT `User`.`id`, `User`.`username` FROM `users` AS `User` WHERE `User`.`active` = 1 AND NOT (`User`.`username` IN ('admin', 'user'))@Luiz: Thanks for your example!