Doctrine 查询语言获取每组最大/最新行 [英] Doctrine Query Language get Max/Latest Row Per Group

问题描述

我正在尝试将我相对简单的 SQL 语句转换为可以在 Doctrine 中使用的语句，但失败了.

I am trying and failing to translate my relatively simple SQL statement into one that will work within Doctrine.

这是 SQL 语句，它在针对我的数据库运行时按要求工作:

This is the SQL statement, which works as required when run against my database:

SELECT a.*
 FROM score a
 INNER JOIN (
  SELECT name, MAX(score) AS highest
  FROM score
  GROUP BY name
 ) b
 ON a.score = b.highest AND a.name = b.name
 GROUP BY name
 ORDER BY b.highest DESC, a.dateCreated DESC

这是迄今为止的 DQL 尝试:

Here's the DQL attempt thus far:

$kb = $em->createQuery(
    "SELECT a 
    FROM ShmupBundle:Score a
    INNER JOIN a.name ShmupBundle:Score b WITH a.score = b.score AND a.name = b.name GROUP BY b.name
    WHERE a.platform='keyboard'
    GROUP BY a.name
    ORDER BY b.score DESC, a.dateCreated DESC"
);

当前出现此错误的原因:

Which is currently giving this error:

[Semantical Error] line 0, col 73 near 'ShmupBundle:Score': Error: Class ShmupBundle\Entity\Score has no association named name

表格本身非常简单:id、姓名、分数、平台、创建日期

The table itself is pretty simple: id, name, score, platform, dateCreated

有多个条目具有相同的名称，但分数不同.我只想显示每个名称的高分".我一直在尝试一两天，但没有运气.有人能指出我正确的方向吗?

There are multiple entries with the same name, but different scores. I want to show only the "high score" per name. I've been trying on and off for a day or two now, with no luck. Can anyone point me in the right direction?

推荐答案

您尝试使用学说进行的查询与 greatest-n-per-group.使用子查询然后与主查询连接会使事情变得复杂，以处理学说.所以下面是重写的 SQL 版本，以在不使用任何聚合函数的情况下获得相同的结果:

The query you are trying to do with doctrine is related to greatest-n-per-group. To use a sub query and then join with main query get things complicated to handle with doctrine. So below is the rewritten SQL version to get the same results without use of any aggregate functions:

SELECT 
  a.* 
FROM
  score a 
  LEFT JOIN score b 
    ON a.name = b.name 
    AND a.score < b.score 
WHERE b.score IS NULL 
ORDER BY a.score DESC 

演示

将上述查询等价转换为学说或 DQL 很容易，以下是上述 SQL 的 DQL 版本:

To convert above query equivalent to doctrine or DQL is easy, below is the DQL version of above SQL:

SELECT a 
FROM AppBundle\Entity\Score a
    LEFT JOIN AppBundle\Entity\Score b 
    WITH a.name = b.name 
    AND a.score < b.score
WHERE b.score IS NULL
ORDER BY a.score DESC

或者使用查询构建器，您可以使用 演示架构

Or with query builder you can write something like i have tested below with symfony 2.8 using the DEMO Schema

$DM   = $this->get( 'Doctrine' )->getManager();
$repo = $DM->getRepository( 'AppBundle\Entity\Score' );
$results = $repo->createQueryBuilder( 'a' )
                ->select( 'a' )
                ->leftJoin(
                    'AppBundle\Entity\Score',
                    'b',
                    'WITH',
                    'a.name = b.name AND a.score < b.score'
                )
                ->where( 'b.score IS NULL' )
                ->orderBy( 'a.score','DESC' )
                ->getQuery()
                ->getResult();

另一个想法是使用您在数据库中的查询创建一个视图，并在 symfony 中创建一个实体，将视图名称放在表注释中，然后开始调用您的实体，它将给出您的查询返回的结果，但不推荐这种方法只是临时修复.

Another idea would be create a view using your query in database and in symfony create an entity put the view name in table annotation and just start calling your entity it will give the results returned by your query but this approach is not recommended just a temporary fix.

这篇关于Doctrine 查询语言获取每组最大/最新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！