Doctrine 2 WHERE IN clause using a collection of entities

Question

I'm attempting to build a query in Doctrine 2 that finds all Vacancy entities which are related to any of the given VacancyWorkingHours 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 a Doctrine\Common\Collections\ArrayCollection instance containing a number of VacancyWorkingHours entities

$q = $this->createQueryBuilder('v')
    ->select('v')
    ->andWhere('v.workingHours IN (:workingHours)')
    ->setParameter('workingHours', $workingHours->toArray());
;

Lauranlaurance

Answer 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.

Deutzia

Answer 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);
;

Kithara

Thanks, that works. It seems strange/wrong to have to reference the key field though rather than letting Doctrine handle it. – Lauranlaurance

@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 – Semipermeable

@MichaëlPerrin Good news! – Kithara

Answer 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.

Chongchoo

Answer 4

I think the DQL will work better for that.

$em = $this->getDoctrine()->getEntityManager();
$query = $em->createQuery(
    'SELECT v
    FROM YourAppYourBundle:YourEntity v // exemple AcmeexampleBundle:YourEntity
    WHERE v.workingHours IN :workingHours'
)->setParameter('workingHours', $workingHours->toArray());

$vacancies = $query->getResult();

Convocation

Thanks, 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. – Lauranlaurance