How to use NOT IN in a condition

Published on and tagged with cakephp  model  sql

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

Here the solution for older CakePHP versions:

$this->User->findAll('User.id NOT IN (1, 2, 3)');
$this->User->findAll(array('NOT' => array('User.id' => array(1, 2, 3))));

The findAll() method has been removed in newer CakePHP versions (1.3.x). The solution is now:

$this->User->find('all', array('conditions' => array('NOT' => array('User.id' => array(1, 2, 3)))));

Update 2010-10-20: Adapted code snippet for CakePHP 1.3.x

21 comments baked

  • Dejan Ranisavljevic

    Why is this solution better? I don’t see a point …

  • dz

    the second one is useful if you have data in array form already. you do not have to transform them to text.

  • http://getopenid.com/nik

    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

  • Brandon

    Is there a way to do “OR” conditions with the array format?

  • othman ouahbi

    Brandon, same syntax as NOT

  • cakebaker

    @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:

    WHERE `User`.`id` NOT 'IN (1, 2, 3)'
    

    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/

  • moeffju

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

  • cakebaker

    @moeffju: Good luck with your application :)

  • Diego

    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.

  • cakebaker

    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.

  • Diego

    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

  • cakebaker

    @Diego: Thanks for the link!

  • moeffju

    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?

  • cakebaker

    @moeffju: I am not aware of such a tutorial…

  • ohcibi

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

  • cakebaker

    @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:

    $this->User->find('all', array('conditions' => array('NOT' => array('User.id' => array(1, 2, 4)))));
    

    Hope that helps!

  • Luiz Lopes

    This worked with what I am trying to do. I’m using

    $this->set('agents',$this->Contract->User->find('list',array(
    			'fields' => array('User.id','User.username'),
    			'conditions' => array('User.active =' => '1',
    							'NOT' => array(
    								'User.username' => array('admin',$this->Auth->user('username'))
    							))
    			))
    );

    Which produces the following:
    SELECT `User`.`id`, `User`.`username` FROM `users` AS `User` WHERE `User`.`active` = 1 AND NOT (`User`.`username` IN ('admin', 'user'))

  • cakebaker

    @Luiz: Thanks for your example!

  • Moe Sweet

    Second way is the CAKE WAY.
    It is useful when you already have the whole long cake-conditions and want to append the IN condition in find() and paginate() functions.

  • cakebaker

    @Moe: Yep, using the array syntax is the Cake way. And in CakePHP 1.3.x the string approach will no longer work…

  • SQL mit CakePHP und der Bedingung NOT IN - Ralf Hohoff

Bake a comment




(for code please use <code>...</code> [no escaping necessary])

© daniel hofstetter. Licensed under a Creative Commons License