如何使用 Doctrine 查询语言 (DQL) 检索每组的最大或最新行？

学说查询语言：每组的最大或最新行

问题：

将 SQL 语句转换为学说查询语言(DQL) 检索每个的最大或最新行组。

SQL 语句：

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尝试：

$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

解决方案：

DQL 尝试尝试使用名为“name”的关联，该关联在分数中不存在

重写的 SQL 语句：

为了避免使用聚合函数，SQL 语句可以重写为：

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：

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

查询构建器版本：

使用查询构建器，查询可以编写为：

$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();

以上是如何使用 Doctrine 查询语言 (DQL) 检索每组的最大或最新行？的详细内容。更多信息请关注PHP中文网其他相关文章！