Query Language Doctrine get Max / Latest Row Per Group

Question

I try and cannot translate my relatively simple SQL statement to one that will work in Doctrine.

This is an SQL statement that works as needed when working with 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

Here's the 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"
);

This error is currently:

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

The table itself is pretty simple: id, name, score, platform, dateCreated

There are several entries with the same name but different scores. I want to show only "high marks" for each name. I tried for a day or two, with no luck. Can someone point me in the right direction?

Answer 1

, , greatest-n-per-group. , , . 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 

DEMO

, DQL, , DQL SQL

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

- , symfony 2.8, DEMO

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

- , , symfony , , , , .

Answer 2

, .

$kb = $em->createQuery(
"SELECT a 
FROM ShmupBundle:Score a
INNER JOIN ShmupBundle:Score b ON 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");

Answer 3

 $name = $em->getRepository('AppBundle:BlogPost')->getMaxId();

-

 public function getMaxId()
{
$qb = $this->createQueryBuilder('u');
$qb->select('u, MAX(id) as idMax');  
return $qb->getQuery()->getSingleResult();
}

Answer 4

• MySQL :. ShmupBundle:Score , .. - , ?
• GROUP BY, WHERE. GROUP BY b.name.
• GROUP BY b.name, a.name, .