Doctrine DQL greatest-n-per-group

Question

Here are 2 tables of my Symfony2 project :

+-----------+     +----------------------------+ 
|  EVENT    |     |         PHOTO              |
+-----------+     +------+-----------+---------+
|    id     |     | id   | event_id  |  likes  |
+-----------+     +------+-----------+---------+
|     1     |     |  1   |    1      |   90    |
|     2     |     |  2   |    1      |   50    |
+-----------+     |  3   |    2      |   20    |
                  |  4   |    2      |   10    |
                  +------+-----------+---------+

I would like to select the 2 events with its most liked photo, which would look like :

+------------+------------+---------+  
|  event_id  |  photo_id  |  likes  |  
+------------+------------+---------+
|     1      |     1      |    90   |
+------------+----------------------+
|     2      |     3      |    20   |
+------------+----------------------+

The SQL solution is explained here (SQL Select only rows with Max Value on a Column) and could be :

SELECT p.event_id, p.likes, p.id
FROM photo p
INNER JOIN(
    SELECT event_id, max(likes) likes
    FROM photo
    GROUP BY event_id
) ss on p.event_id = ss.event_id and p.likes = ss.likes

What would be the DQL query for that ? I tried many things but always get errors.

score 2 · Answer 1 · answered Apr 24 '13 at 14:34

I didn't manage to find an appropriate answer using DQL but there is a way, through doctrine, to process SQL query using what they call Native Query.

I managed to create a working example of your SQL sample using the Native Query module and Symfony 2

use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query\ResultSetMapping;

class PhotoRepository extends EntityRepository
{
    public function findSomeByEvent()
    {
        $rsm = new ResultSetMapping;
        $rsm->addEntityResult('theNameOfYourBundle:Photo', 'p');
        $rsm->addFieldResult('p', 'id', 'id');
        $rsm->addFieldResult('p', 'likes', 'likes');
        $rsm->addFieldResult('p', 'event', 'event');

        $sql = 'SELECT p.event_id, p.likes, p.id
                FROM Photo p
                INNER JOIN(
                    SELECT event_id, max(likes) likes
                    FROM Photo
                    GROUP BY event_id
                ) ss on p.event_id = ss.event_id and p.likes = ss.likes';
        $query = $this->_em->createNativeQuery($sql, $rsm);

        $resultats = $query->getArrayResult();

        return $resultats;
    }
}

Here's a link to the documentation: Native Query, if the answer is not working as intended

score 2 · Answer 2 · answered Sep 15 '17 at 06:57

To do this using DQL you can use below approach, to handle same using sub query approach would be difficult to do with doctrine so i use greatest-n-per-group approach without use of aggregate function

SQL DEMO

$DM      = $this->get( 'Doctrine' )->getManager();
$DQL     = 'SELECT a 
        FROM AppBundle\Entity\Photo a
        LEFT JOIN AppBundle\Entity\Photo b WITH a.event = b.event AND a.likes < b.likes
        WHERE b.id IS NULL
        ORDER BY a.likes DESC
        ';
$query   = $DM->createQuery( $DQL );
$results = $query->getResult();
foreach ( $results as $result ) {
    echo '<pre>';
    print_r($result->getEvent()->getId().' - photo'.$result->getId().' - '. $result->getLikes() );
    echo '</pre>';
}

Please note i have tested this with doctrine 2 and symfony 2.8

Aushin · Answer 3 · 2013-04-23T16:38:18.200

In your query you are joining to ss.Id but you have not selected that column in your subquery, so ss.id does not exist. Also your inner join is to a table you called p2.event e2. I think you intended for just "event e2"

SELECT p.id
FROM Photo p
INNER JOIN (
            SELECT p2.likes, e2.id
            FROM Photo p2
            INNER JOIN event e2
            GROUP BY e2.id
           ) ss ON ( p.event_id = ss.id AND p.likes = ss.likes )

The only change here is that I added e2.id to your subquery select. This query still does not solve your problem. You want the one with the most likes. So you should make p2.likes into MAX(p2.likes)

SELECT p.id
FROM Photo p
INNER JOIN (
            SELECT MAX(p2.likes) AS Likes, e2.id
            FROM Photo p2
            INNER JOIN event e2
            GROUP BY e2.id
           ) ss ON ( p.event_id = ss.id AND p.likes = ss.likes )

Further, consider that if two pictures are tied for likes, you will return two results using this query. You might consider using CROSS APPLY (assuming DQL has it) to avoid something like this.

SELECT e.id, p.id
FROM Events E
CROSS APPLY (
              SELECT TOP 1 p.id
              FROM photos p 
              WHERE p.event_id = e.id
              ORDER BY Likes DESC 
            ) p

Is your answer SQL or DQL ? I get same error "SELECT MAX(p2.likes),': Error: Identification Variable ( used in join path expression but was not defined before." — httpete, Apr 23 '13 at 16:10
Oh I'm sorry. MAX(p2.likes) AS Likes. Have to give it the name likes. I edited the second query. It is in SQL. I'm just betting that DQL supports at least the second query here. — Aushin, Apr 23 '13 at 16:38
Your bet is wrong, I am still getting the same error. I am expecting DQL and not SQL. — httpete, Apr 23 '13 at 16:50

score 0 · Answer 4 · edited May 23 '17 at 12:25

0

Not an expert in DQL, but if you are avoiding the use of aggregate functions, you can always use the second approach described in SQL Select only rows with Max Value on a Column:

select p1.*
from photos p1
left outer join photos p2
on (p1.event_id = p2.event_id and p1.likes < p2.likes)
where p2.event_id is null;

edited May 23 '17 at 12:25

Community

1
1

answered Apr 23 '13 at 15:55

Adriano Carneiro

57,693
12
90
123

Error: Identification Variable AppNameBundle:Photo used in join path expression but was not defined before. :( – httpete Apr 23 '13 at 16:19

Doctrine DQL greatest-n-per-group

4 Answers4

Linked