How to limit results of a left-join with Doctrine

问题:

In a symfony 2 application, I have 2 entities mapped by a oneToMany relation (user and rendezvous). I'm trying to search into my user entity and join the last rendezvous for each user found. The idea is something like that :

    $qb = $this->createQueryBuilder('p');

    $qb->select('p.id, p.last_name, p.first_name')
       ->leftJoin('p.rendezvous', 'i')
            ->select('i.date')
            ->where('i.user = p.user')
            ->orderBy('i.date', 'DESC')
            ->setFirstResult(0)
            ->setMaxResults(1)                
       ->where('p.user IN ('.$users.')')
       ->orderBy('p.last_name', 'ASC')
       ->addOrderBy('p.first_name', 'ASC');

I should have results like :

1, Ben, Tooch, 2014-10-15 18:45:00
7, John, Snow, 2014-10-16 17:15:00
...

I tried to use the paginator function but without any success.
Thank you very much for your help.

回答1:

As I add some more columns to get, I had to find another way to do it. I finally got a working DQL query :

$qb->select('p.id, p.last_name, p.first_name, r.date, r.othercolumn')
    ->leftJoin('p.rendezvous', 'r', 'WITH', 'p.id = r.user AND r.date = (SELECT MAX(r2.date) FROM \App\YourBundle\Entity\Rendezvous as r2 WHERE r2.user = p.id)')               
    ->where('p.user IN ('.$users.')')
    ->orderBy('p.last_name', 'ASC')
    ->addOrderBy('p.first_name', 'ASC')
    ->groupBy('p.id');

回答2:

Have you tried outputing the generated SQL and running it?

I'm not sure if you can use maxresults and firstResult in a left join like this, but if your idea is just to recover users and their last rendezvous, you could use max(i.date) and group by p.id, p.last_name, p.first_name.

But if you want to page it, simply join the two tables and order by i.date.

Hope it helps!