教义查询语言每组最多/最新行 [英] 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"
);

当前出现此错误:

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

表本身非常简单: id，名称，分数，平台，dateCreated

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?

推荐答案

您要尝试使用的查询与

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

或者使用查询生成器，您可以使用> DEMO模式

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.

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