Home > Database > Mysql Tutorial > How to Retrieve the Maximum or Latest Row per Group in Doctrine Query Language (DQL)?

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

Susan Sarandon
Release: 2024-11-25 19:46:11
Original
297 people have browsed it

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
Copy after login

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"
);
Copy after login

Error:

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

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 
Copy after login

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
Copy after login

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();
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template