如何使用 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中文網其他相關文章！