Using a arraycollection as a parameter in a doctrine query

Question

I have a ArrayCollection of status objects that I would now like to use as the IN parameter in a WHERE clause for a doctrine query. Here's my query code:

$query = $repository->createQueryBuilder('r')
                ->join('r.applicationStatus', 's')
                ->where('r.submitted IS NOT NULL')
                ->andWhere('r.created >= :date')                  
                ->andWhere('r.created < :date2')
                ->andWhere('s IN (:status)') // Here's the In statement
                ->orderBy('r.created', 'DESC')                
                ->setParameter('date', $appSearch->getDateFrom())
                ->setParameter('date2', $end)
                ->setParameter('status', $appSearch->getApplicationStatus()) //Here's the array collection
                ->getQuery();

However the query is returning zero records. For it to work I have to manually iterate through the $appSearch->getApplicationStatus() arraycollection and grab the status id's in a new array for the query to yield correct results at the moment - which feels very inefficient.

What am I doing wrong?

Answer 1

You should do something like that:

$statusId = array();

foreach ($appSearch->getApplicationStatus() as $status) {
    $statusId[] = $status->getId();
}

// ... Your query:
->andWhere('s.id IN (:status)')
->setParameter('status', $statusId)

In fact I think that Doctrine can't filter something by giving an object, it is not capable to compare it, so you need to use a field of these objects, here the id in my example... and comparing integer is lighter for your database !