Doctrine Query Language get Max/Latest Row Per Group

Question

I am trying and failing to translate my relatively simple SQL statement into one that will work within Doctrine.

This is the SQL statement, which works as required when run against 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 thus far:

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

Which is currently giving this error:

[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 multiple entries with the same name, but different scores. I want to show only the "high score" per name. I've been trying on and off for a day or two now, with no luck. Can anyone point me in the right direction?

Can you share the entity ShmupBundle:Score ? – albert Sep 14 '17 at 23:14 — albert, Sep 14 '17 at 23:14

score 24 · Accepted Answer · edited Jul 19 '18 at 12:31

The query you are trying to do with doctrine is related to greatest-n-per-group. To use a sub query and then join with main query get things complicated to handle with doctrine. So below is the rewritten SQL version to get the same results without use of any aggregate functions:

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

To convert above query equivalent to doctrine or DQL is easy, below is the DQL version of above 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

Or with query builder you can write something like i have tested below with symfony 2.8 using the DEMO Schema

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

Another idea would be create a view using your query in database and in symfony create an entity put the view name in table annotation and just start calling your entity it will give the results returned by your query but this approach is not recommended just a temporary fix.

This worked perfectly, thank you. I had a feeling there was a simpler way of going about it, my hacked together SQL query being the result of someone who's not done PHP in years just trying to make it work. — Douglas, Sep 16 '17 at 02:40

score 2 · Answer 2 · answered Sep 08 '17 at 07:11

2

Inner Join Statement needs first argument as a table, that is a semantic error in your query.

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

answered Sep 08 '17 at 07:11

Oscar

1,929
3
16
31

This gives me an error: "[Syntax Error] line 0, col 90: Error: Expected Doctrine\ORM\Query\Lexer::T_WITH, got 'ON'". I tried then changing ON to WITH, and it returns "Expected end of string, got 'WHERE'". – Douglas Sep 08 '17 at 14:39
No, I'm not familiar with how to do inner joins like this with that syntax. – Douglas Sep 13 '17 at 04:06
Its very easy. take a look on this http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/query-builder.html#join-clauses – Oscar Sep 13 '17 at 06:57
I'm sorry, I'm really not finding it easy at all. I've been banging my head against this wall for days to no avail. Nothing I do seems to work. :( – Douglas Sep 14 '17 at 17:00

M Maavia · Answer 3 · 2021-07-01T10:41:23.150

use this in class

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

in repository you can use something like

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

each entity come with some default repository functions either we defined or not if we wish to add some extra functionality we do write down custom functions in repository class. like i want to add one more function getMaxId i will write down in repository to access this function.

for getting max or min from each group we can do with given query

select * from (select * from mytable order by `Group`, age desc, Person) x group by `Group

this is not good way to fetch max from each group as we need to write down sub query for that. other than that we have Row_number() function

 SELECT sd.* FROM ( SELECT sale_person_id,sale_person_name,no_products_sold,commission_percentage,sales_department,ROW_NUMBER() OVER(PARTITION BY sale_person_id ORDER BY no_products_sold DESC) rowNumber FROM sales_department_details  )sd  WHERE sd.rowNumber =1;

here you find out all work arounds

Please add some explanation to your answer such that others can learn from your answer. How does this method group the scores by name? — Nico Haase, Apr 21 '21 at 14:42

score -1 · Answer 4 · answered Sep 15 '17 at 02:00

-1

MySQL does not understand the : syntax. If ShmupBundle:Score is supposed to be a database and table, then use .. If Doctrine is supposed to replace it with something, then what does it do with it?
There can be only one GROUP BY clause, and it must be after the WHERE clause. Try removing the GROUP BY b.name.
There is no need to GROUP BY both b.name and a.name since they are equal.

answered Sep 15 '17 at 02:00

Rick James

135,179
13
127
222

`ShmupBundle:Score` notation is used in Symfony projects sometimes. – SteveB May 22 '18 at 20:24

Doctrine Query Language get Max/Latest Row Per Group

4 Answers4

Linked

Related