如何使用 Doctrine (DQL) 限制左连接的结果 [英] How to limit results of a left-join with Doctrine (DQL)
问题描述
在 symfony 2 应用程序中,我有 2 个由 oneToMany 关系(用户和集合点)映射的实体.我正在尝试搜索我的用户实体,并为找到的每个用户加入最后一次集合.这个想法是这样的:
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');
我应该得到如下结果:
1, Ben Tooch, 2014-10-15 18:45:00
7、约翰·斯诺,2014-10-16 17:15:00
...
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.
推荐答案
当我添加更多的列来获取时,我不得不找到另一种方法来做到这一点.我终于得到了一个有效的 DQL 查询:
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 AppYourBundleEntityRendezvous 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');
这篇关于如何使用 Doctrine (DQL) 限制左连接的结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!