Doctrine 2 WHERE IN clause using a collection of entities
Solution 1
A pull request I made about this has been merged into Doctrine ORM 2.5, so you can simply do this now:
$q = $this->createQueryBuilder('v')
->select('v')
->andWhere('v.workingHours IN (:workingHours)')
->setParameter('workingHours', $workingHours);
;
The latest version of Doctrine now allows collection parameters and will automatically make use of the primary key of each of the collection entries.
Solution 2
Try to set IDs as parameter
$ids = array();
foreach($workingHours as $w) {
$ids[] = $w->getId();
}
Then
$q = $this->createQueryBuilder('v')
->select('v')
->andWhere('v.workingHours IN (:workingHours)')
->setParameter('workingHours', $ids);
;
Solution 3
I suggest using DQL in this way:
$qb = $this->createQueryBuilder('v')
->andWhere($qb->expr()->in('v.workingHours', $ids));
;
And let Doctrine handle the expression & quotation work for you.
Jaik Dean
Updated on November 06, 2020Comments
-
Jaik Dean over 3 years
I'm attempting to build a query in Doctrine 2 that finds all
Vacancy
entities which are related to any of the givenVacancyWorkingHours
entities.The
Vacancy
entity looks as follows:/** * Vacancy * * @ORM\Table(name="vacancy") * @ORM\Entity(repositoryClass="JaikDean\CareersBundle\Entity\VacancyRepository") */ class Vacancy { /** * @var integer * * @ORM\Column(name="id", type="integer") * @ORM\Id * @ORM\GeneratedValue(strategy="AUTO") */ private $id; /** * @var VacancyWorkingHours * * @ORM\ManyToOne(targetEntity="VacancyWorkingHours", inversedBy="vacancies") * @ORM\JoinColumn(name="vacancy_working_hours_id", referencedColumnName="id") **/ private $workingHours; /* Other fields and methods are inconsequential */ }
My query currently looks as follows, but returns no results because of the where clause. In this example,
$workingHours
is aDoctrine\Common\Collections\ArrayCollection
instance containing a number ofVacancyWorkingHours
entities$q = $this->createQueryBuilder('v') ->select('v') ->andWhere('v.workingHours IN (:workingHours)') ->setParameter('workingHours', $workingHours->toArray()); ;
-
Jaik Dean over 10 yearsThanks, I might end up going down that route. The particular WHERE IN clause is actually part of a bigger query which is being built up based on various other conditions, so using the query builder is easier from an organisational point of view.
-
Jaik Dean over 10 yearsThanks, that works. It seems strange/wrong to have to reference the key field though rather than letting Doctrine handle it.
-
Michaël Perrin over 9 years@JaikDean Yes indeed that's weird, and that's why I proposed a patch to enhance this so that it works with entities rather than ids. It has been merged and will be available in Doctrine 2.5 : github.com/doctrine/doctrine2/pull/590
-
Alexey B. almost 9 years@MichaëlPerrin Good news!