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 !

Please, add your entity class to this question. If your entity field names are different from names of the attributes in the database then you have to use annotation in your entity class to "connect" fileds to the right attributes. — NutCracker, Aug 19 '17 at 08:35

score 0 · Accepted Answer · answered Aug 22 '17 at 13:39

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

Join with OneToMany relation

1 Answers1