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?

Sybio · Accepted Answer · 2013-01-08 15:53:26Z

10

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 !

answered Jan 8, 2013 at 15:53

Sybio

8,6853 gold badges47 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

spdionis Over a year ago

ArrayCollection class has the (very convenient) toArray method to do the same thing.

darkbluesun Over a year ago

It's a shame, since Doctrine can accept an object as a parameter for an eq() comparison, but doesn't seem to like an array of objects.

Petter Friberg · Accepted Answer · 2016-10-08 17:10:14Z

0

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();
    }
}

edited Oct 8, 2016 at 17:10

Petter Friberg

21.8k10 gold badges67 silver badges116 bronze badges

answered Oct 8, 2016 at 16:44

Kefi Ramzi

1

Collectives™ on Stack Overflow

Using a arraycollection as a parameter in a doctrine query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related