sql - Doctrine DQL 每组最大 n

Question

这是我的 Symfony2 项目的 2 个表:

+-----------+     +----------------------------+ 
|  EVENT    |     |         PHOTO              |
+-----------+     +------+-----------+---------+
|    id     |     | id   | event_id  |  likes  |
+-----------+     +------+-----------+---------+
|     1     |     |  1   |    1      |   90    |
|     2     |     |  2   |    1      |   50    |
+-----------+     |  3   |    2      |   20    |
                  |  4   |    2      |   10    |
                  +------+-----------+---------+

我想选择最喜欢的照片的 2 个事件，看起来像:

+------------+------------+---------+  
|  event_id  |  photo_id  |  likes  |  
+------------+------------+---------+
|     1      |     1      |    90   |
+------------+----------------------+
|     2      |     3      |    20   |
+------------+----------------------+

SQL 解决方案在此处( SQL Select only rows with Max Value on a Column)进行了解释，可能是:

SELECT p.event_id, p.likes, p.id
FROM photo p
INNER JOIN(
    SELECT event_id, max(likes) likes
    FROM photo
    GROUP BY event_id
) ss on p.event_id = ss.event_id and p.likes = ss.likes

对此的 DQL 查询是什么？我尝试了很多东西，但总是出错。

Answer 1

我没有设法使用 DQL 找到合适的答案，但是通过学说，有一种方法可以使用他们所谓的 Native Query 来处理 SQL 查询。

我设法使用 Native Query 模块和 Symfony 2 创建了您的 SQL 示例的工作示例

use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query\ResultSetMapping;

class PhotoRepository extends EntityRepository
{
    public function findSomeByEvent()
    {
        $rsm = new ResultSetMapping;
        $rsm->addEntityResult('theNameOfYourBundle:Photo', 'p');
        $rsm->addFieldResult('p', 'id', 'id');
        $rsm->addFieldResult('p', 'likes', 'likes');
        $rsm->addFieldResult('p', 'event', 'event');

        $sql = 'SELECT p.event_id, p.likes, p.id
                FROM Photo p
                INNER JOIN(
                    SELECT event_id, max(likes) likes
                    FROM Photo
                    GROUP BY event_id
                ) ss on p.event_id = ss.event_id and p.likes = ss.likes';
        $query = $this->_em->createNativeQuery($sql, $rsm);

        $resultats = $query->getArrayResult();

        return $resultats;
    }
}

这是文档的链接: Native Query ，如果答案没有按预期工作