How to limit left join results with Doctrine (DQL)
In symfony 2 application, I have 2 objects mapped by oneToMany relationship (user and rendezvous). I am trying to search a custom entityand join the latest rendezvous for each user found. The idea is this:
$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, Toh, 2014-10-15 18:45:00
7, John, Snow, 2014-10-16 17:15:00
...
I tried using the paginator function, but with no success. Thank you so much for your help.
source to share
When I add a few more columns I needed to find another way to do it. Finally, I 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');
source to share
Have you tried to dump the generated SQL and run it?
I'm not sure if you can use maxresults and firstResult on a left join like this, but if your idea is to restore users and their last rendezvous, you can use max (i.date) and group by p. id, p.last_name, p.first_name.
But if you want to post it, just join the two tables and order by i.date.
Hope it helps!
source to share