Doctrine Query Language 获取每组的最大/最新行

2022-01-16 00:00:00 php mysql symfony doctrine greatest-n-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 ShmupBundleEntityScore has no association named name

表格本身非常简单:id, name, score, platform, 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?

推荐答案

您尝试对教义执行的查询与 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 AppBundleEntityScore a
    LEFT JOIN AppBundleEntityScore 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( 'AppBundleEntityScore' );
$results = $repo->createQueryBuilder( 'a' )
                ->select( 'a' )
                ->leftJoin(
                    'AppBundleEntityScore',
                    '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.

相关文章