How to deal with "IN" in WHERE-clause in Doctrine2
Solution 1
Be aware that this only works for numbered parameters, and not named parameters.
$searchParameters = array(108919, 108920);
$dql = "SELECT t Entities\Table t WHERE t.field IN (?1)";
$q = $em->createQuery($dql)
->setParameter(1, $searchParameters);
$result = $q->execute();
Solution 2
The following should work fine:
$searchParameters = array(108919, 108920);
$dql = "SELECT t Entities\Table t WHERE t.field IN (?1)";
$q = $em->createQuery($dql)
->setParameter(1, $searchParameters);
$result = $q->execute();
You can pass in an array, without using implode() and doctrine will handle it properly (as a list of integers).
Note: if you're already working with the string '108919, 108920' -- you will need to use the explode and trim functions.
This is also mentioned here: How to use the in statement in DQL in Doctrine 2.0
Solution 3
The following should work as expected (for an arbitrary numer of arguments to the IN
clause)
$params = array(1 => 108919, 2 => 108920);
$qb = $em->createQueryBuilder();
$qb->select(array('t'))
->from('Entities\Table', 't')
->where($qb->expr()-> in('t.field', array_map(function($p) { return '?'.$p; }, array_keys($params)))
->setParameters($params);
$q = $qb->getQuery();
$r = $q->getResult();
Related videos on Youtube
John W.
Updated on May 13, 2022Comments
-
John W. almost 2 years
Here is the dql-query
$dql = "SELECT t Entities\Table t WHERE t.field IN (?1)"; $q = $em->createQuery($dql) ->setParameter(1, '108919,108920'); $result = $q->execute();
if i pass parameters through setParameter doctrine returns only first result, but if i put them directly into the dql-query it returns 2 results (this is correct):
$dql = "SELECT t Entities\Table t WHERE t.field1 IN (108919,108920)";
How to deal with "IN" in WHERE-clause through setParameter?
-
alex.pilon about 12 yearsThis works for me and thus I would assume is the correct answer to this question.
-
Jon Skarpeteig over 11 yearsI was tearing my hair out trying to figure out why I couldn't get array parameters to work - replacing named parameter with numbered saved my day :-)
-
Jeroen van den Broek about 10 yearsI just came across this question and tried it with a named parameter anyway. Seems like somewhere between this answer and now, Doctrine was changed to allow this construction for named parameters as well.