Using an IN list in a condition
In SQL there exists a nice concept called IN list that allows you to rewrite an SQL statement like
SELECT x FROM y WHERE x = 1 OR x = 2 OR x = 3;
to
SELECT x FROM y WHERE x IN (1, 2, 3);
If you want to use this concept in CakePHP, you have two possibilities. You can use the string syntax:
$this->User->findAll('User.id IN (1, 2, 3)');
Or you can use the array syntax which generates the IN list automatically:
$this->User->findAll(array('User.id' => array(1, 2, 3)));
Very nice and handy.
Thank you!
@pdaether: I am glad it is useful for you :)
couple of day ago i was looking for the solution to this problem and i used a workaround :
$this->User->findAll(’User.id IN (’.implode(’,',$userIds).’)');
where $userIds is an array of user Ids.
btw. html is avaible in comments ?:) it whould be nice to write it somewhere near the comment form.
Thanks for sharing this one Daniel. I never needed it, but it sure is one of those things that are ‘good to know’ ; ).
Good post!
I had to search a lot to find this gift and solve a problem!
I have to filters my blog’s posts by associated tag with hasAndBelongsToMany Cake’s association.
I solved it with:
$this->Post->findAll(”Post.id IN ( SELECT tag_id FROM posts_tags WHERE tag_id = $myTagId )”);
Bye!
@Marco: Thanks! I am glad you could solve your problem!
How would I do a NOT IN? array(’not’ => array(’User.id’ => array(1,2,3)))?
@moeffju: Yes, or $this->User->findAll(’User.id NOT IN (1, 2, 3)’);
CakePHP findAll の条件に IN 演算子を使う…
CakePHP の findAll の条件に IN 演算子を使いたく、
$this->model->findAll(array(”field IN (1,2,3)”));
としたら、
SQL * FROM model WHERE field IN ‘(1,2,3)’
のような SQL が発行されて当然エラーになってしまった…