How to use the in statement in DQL in Doctrine 2.0

Question

I have the following query that uses an IN statement.

$ids = array(1,2,3);
$query = 'select o from Organisation o where o.id in (:ids)';
$this->_entityManager->createQuery($query)
            ->setParameter('ids', implode(', ', $ids))

Doctrine is not returning any results, I think it is because of something wrong in the conversion that Doctrine does for the passed parameter $ids which is an array.

How to make it work?

Answer 1

Try passing the array itself to ->setParameter(...) instead of imploding it into a string.

Answer 2

I used this (setParameter didn't seem to work for me):

$em->createQuery('SELECT users FROM Entities\User users WHERE users.id IN (:ids)')
->setParameters(array('ids' => $ids));

http://redbeardtechnologies.wordpress.com/2011/07/01/doctrine-2-dql-in-statement/

Answer 3

I'm struggling with the IN statement too, using the $query->expr()->in() construct...

Try:

$em->createQuery(“SELECT users FROM Entities\User users WHERE users.id IN (':ids')”)
->setParameters(array(‘ids’ => $ids));

I think the simple quotes around your parameters in the IN() part are necessary...

Answer 4

I solved this:

$con = $this->getEntityManager();
$query = $con->createQuery("SELECT cl
                            FROM BackendBundle:classifieds cl 
                            INNER JOIN BackendBundle:locations lo WITH cl.locationId = lo.id
                            INNER JOIN BackendBundle:municipality mu WITH lo.municipalId = mu.id
                            WHERE cl.verified = false AND mu.id = ".$munId." AND cl.locationId NOT IN (:ids) ");
$query->setParameters(array('ids' => $locsIds));
return $query->getResult();