How to Retrieve the Maximum or Latest Row per Group in Doctrine Query Language (DQL)?

Doctrine Query Language: Maximum or Latest Row per Group

Problem:

Translating a SQL statement to Doctrine Query Language (DQL) to retrieve the maximum or latest row for each group.

SQL Statement:

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 Attempt:

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

Error:

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

Solution:

The DQL attempt attempts to use an association named 'name', which does not exist in the Score entity.

Rewritten SQL Statement:

To avoid using aggregate functions, the SQL statement can be rewritten as:

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 

Equivalent 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

Query Builder Version:

Using the query builder, the query can be written as:

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

The above is the detailed content of How to Retrieve the Maximum or Latest Row per Group in Doctrine Query Language (DQL)?. For more information, please follow other related articles on the PHP Chinese website!