原则DQL每组最大n [英] Doctrine DQL greatest-n-per-group

问题描述

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

Here are 2 tables of my Symfony2 project :

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

我想选择2个最喜欢的照片，它看起来像是：

I would like to select the 2 events with its most liked photo, which would look like :

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

此处介绍了SQL解决方案（），并且可能是：

The SQL solution is explained here (SQL Select only rows with Max Value on a Column) and could be :

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查询？我尝试了很多事情，但总是出错。

推荐答案

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

I didn't manage to find an appropriate answer using DQL but there is a way, through doctrine, to process SQL query using what they call Native Query.

我设法使用本机查询创建了一个SQL示例示例模块和Symfony 2

I managed to create a working example of your SQL sample using the Native Query module and Symfony 2

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;
    }
}

以下是文档的链接：本地查询（如果答案无效）按预期

Here's a link to the documentation: Native Query, if the answer is not working as intended

这篇关于原则DQL每组最大n的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！