Doctrine DQL最大N个分组

Question

这是我Symfony2项目中的两个表格：

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

我希望选择两个事件中最受欢迎的照片，其外观类似于：

+------------+------------+---------+  
|  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查询？我尝试了很多方法但总是出错。

- httpete

Answer 1

使用DQL实现此操作，可以采用以下方法。如果使用子查询的方法来处理，则难以使用Doctrine进行操作，因此我使用greatest-n-per-group方法来处理，而不使用聚合函数。 SQL DEMO

$DM      = $this->get( 'Doctrine' )->getManager();
$DQL     = 'SELECT a 
        FROM AppBundle\Entity\Photo a
        LEFT JOIN AppBundle\Entity\Photo b WITH a.event = b.event AND a.likes < b.likes
        WHERE b.id IS NULL
        ORDER BY a.likes DESC
        ';
$query   = $DM->createQuery( $DQL );
$results = $query->getResult();
foreach ( $results as $result ) {
    echo '<pre>';
    print_r($result->getEvent()->getId().' - photo'.$result->getId().' - '. $result->getLikes() );
    echo '</pre>';
}

请注意，我已经在Doctrine 2和Symfony 2.8中进行了测试。

- M Khalid Junaid

Answer 2

我没有使用DQL找到合适的答案，但是有一种方式可以通过Doctrine使用所谓的本地查询来处理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;
    }
}

这里提供了文档链接:原生查询，如果答案不能正常工作。

- Maxime Lequain

Answer 3

在您的查询中，您正在连接到ss.Id，但是您在子查询中没有选择该列，因此ss.id不存在。此外，您的内部连接是到一个名为p2.event e2的表。我认为您打算只使用“event e2”。

SELECT p.id
FROM Photo p
INNER JOIN (
            SELECT p2.likes, e2.id
            FROM Photo p2
            INNER JOIN event e2
            GROUP BY e2.id
           ) ss ON ( p.event_id = ss.id AND p.likes = ss.likes )

这里唯一的变化是我在你的子查询中添加了e2.id。但是这个查询仍然没有解决你的问题。你想要最多赞的那一个。所以你应该把p2.likes改成MAX(p2.likes)。

SELECT p.id
FROM Photo p
INNER JOIN (
            SELECT MAX(p2.likes) AS Likes, e2.id
            FROM Photo p2
            INNER JOIN event e2
            GROUP BY e2.id
           ) ss ON ( p.event_id = ss.id AND p.likes = ss.likes )

进一步考虑，如果两张图片的点赞数相同，那么使用这个查询将返回两个结果。你可以考虑使用CROSS APPLY（假设DQL支持）来避免这种情况发生。

SELECT e.id, p.id
FROM Events E
CROSS APPLY (
              SELECT TOP 1 p.id
              FROM photos p 
              WHERE p.event_id = e.id
              ORDER BY Likes DESC 
            ) p

- Aushin

---

你的答案是 SQL 还是 DQL？ 我得到了同样的错误："SELECT MAX（p2.likes），': Error: Identification Variable （在联接路径表达式中使用，但在之前未定义。" - httpete

哦，对不起。MAX(p2.likes) AS Likes。必须给它一个名字likes。我编辑了第二个查询。这是SQL语言。我只是打赌DQL至少支持这里的第二个查询。 - Aushin

你的猜测是错误的，我仍然得到相同的错误。我期望的是 DQL 而不是 SQL。 - httpete

非常抱歉浪费了您的时间！ - Aushin

Answer 4

虽然我不是DQL的专家，但如果你想避免使用聚合函数，你可以采用SQL Select only rows with Max Value on a Column中描述的第二种方法：

select p1.*
from photos p1
left outer join photos p2
on (p1.event_id = p2.event_id and p1.likes < p2.likes)
where p2.event_id is null;

- Adriano Carneiro

---

错误：在连接路径表达式中使用了未定义的标识变量AppNameBundle:Photo。 :( - httpete