Join with OneToMany relation

Question

I am trying to query an entity with a OneToMany relation but it doesn't work as the subquery DQL is not well converted.

I expect this code :

$subquery = $manager->createQueryBuilder();
$subquery
    ->select('s.occupant')
    ->from('MyBundle:Stay', 's')
    ->where('s.dateDeparture IS NULL')
;

$qb
    ->where($qb->expr()->notIn('o.id', ':subQuery'))
    ->setParameter('subQuery', $subquery->getDQL())
;

to produce :

WHERE o0_.id NOT IN (
    SELECT s.occupant_id FROM Stay s WHERE s.date_departure IS NULL
)

But instead, I have this :

WHERE o0_.id NOT IN (
    'SELECT s.occupant FROM MyBundle:Stay s WHERE s.dateDeparture IS NULL'
)

Here are the problems :
- The subquery is encapsulated between commas
- The SQL fields are not translated from their names in the entity (occupant, dateDeparture) to their MySQL equivalent (occupant_id, date_departure)
- The entity name is used (MyBundle:Stay) and is not converted to its SQL equivalent (Stay)

My other queries work perfectly, as well as the main query encapsulating this one.

I also tried to use the OneToMany relation to do this, as there is an Occupant.stays relation, but I couldn't make it work either.

Here is my Occupant class :

class Occupant
{
    ...

    /**
     * @ORM\OneToMany(targetEntity="EmmausBundle\Entity\Stay", mappedBy="occupant", cascade={"persist"})
     * @ORM\OrderBy({"dateArrival" = "DESC"})
     */
    private $stays;

    ...

}

And my Stay class :

class Stay
{
    ...

    /**
     * @ORM\ManyToOne(targetEntity="Occupant", inversedBy="stays")
     * @ORM\JoinColumn(name="occupant_id", referencedColumnName="id")
     */
    private $occupant;

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="date_departure", type="datetime", nullable=true)
     */
    private $dateDeparture;

    ...
}

Thanks for you help !

Answer 1

Thanks to this answer I found the solution :

$qb
    ->where(
        $qb->expr()->notIn(
            'o.id',
            $manager->createQueryBuilder()
                ->select('IDENTITY (s.occupant)')
                ->from('EmmausBundle:Stay', 's')
                ->where('s.dateDeparture IS NULL')
                ->getDQL()
            )
        )
    ;