使用带有Doctrine的MAX（）子查询

Question

I'm trying to convert the following native sql query to a querybuilder instance for Doctrine 2 but without any success.

SELECT r1.*
FROM reservations r1
INNER JOIN (
    SELECT user, max(occurrence) AS max
    FROM reservations
    WHERE team = 'team-id'
    AND canceled = 0
    GROUP BY user
) r2
ON r1.occurrence = r2.max AND r1.user = r2.user
WHERE r1.team = 'team-id'
AND r1.canceled = 0
AND r1.occurrence >= '2018-10-08'
ORDER BY occurrence DESC;

I've tried creating a query builder while using the INNER JOIN, but that's not possible, because it doesn't allow subqueries on that point. I have even tried a ->where($qb->expr()->in()), but that didn't work. So I'm a little bit out of options right now.

What I would like to accomplish is to get the last made reservation for all users within a specific team.

Can someone with experiences working with Doctrine please help me?

Answer 1

Doctrine has many annoying limitations, and you've hit on one of the most frustrating - the lack of support for subqueries.

That being said, if you rewrite your query, and dive into the Doctrine source, there is a workaround - create a self join instead.

It's necessary to instantiate and add your own Join instance to the builder, because you're unlikely to have the necessary relations defined on your Reservation entity to be able to use a defined relationship for this.

The following is untested, because you haven't provided your table schema or any sample data, but the theory is there, and I've used similar queries to work around the issue in the past.

// ReservationRepository.php

use AppBundle\Entity\Reservation;
use Doctrine\ORM\Query\Expr\Join;

return $this->createQueryBuilder('r')
    ->add('join', [
        new Join(Join::LEFT_JOIN, Reservation::class, 'r1', 'WITH', 'r.user = r1.user AND r.team = r1.team AND r1.canceled = :canceled AND r.occurrence < r1.occurrence')
    ], true)
    ->where('r.team = :team')
    ->andWhere('r.canceled = :canceled')
    ->andWhere('r.occurrence >= :occurrence')
    ->andWhere('r1.id IS NULL')
    ->orderBy('r.occurrence', 'DESC')
    ->setParameter('team', $team)
    ->setParameter('canceled', 0)
    ->setParameter('occurrence', '2018-10-08')
    ->getQuery()
    ->getResult();