CODEWITHSUNDEEP
symfonydoctrine-orm
user1958578
user1958578

Reputation: 63

Using a arraycollection as a parameter in a doctrine query

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?

Upvotes: 6

Views: 5300

Answers (2)

Kefi Ramzi
Kefi Ramzi

Reputation: 1

its work fine in contrioller .

$users =  $em->getRepository('EventekUserBundle:User')->getQueryf($events->getSecretariats()->toArray()) ;

in repositroy =>

class UserRepository extends \Doctrine\ORM\EntityRepository
{

    public function getQueryFromCollection( $users)
    {
        $qb = $this->createQueryBuilder('u')
            ->select('u')
            ->andWhere('u in (:users)')
            ->setParameter('users',  $users);
        return $qb->getQuery();
    }
}

Upvotes: 0

Sybio
Sybio

Reputation: 8645

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 !

Upvotes: 10

Related Questions