Symfony & Doctrine : DQL Max() with groupBy

Question

I'm working on Symfony 3.2 and i'm trying to do a "simple" Query. This is the SQL version of the query that works (tested directly in PHPmyAdmin)

SELECT s.*
FROM pl_statsheet s
INNER JOIN (
SELECT day, MAX(points) AS points
FROM pl_statsheet
GROUP BY day
) ps 
ON s.day = ps.day AND s.points = ps.points

Unfortunelty, I can't "convert" it to work with Symfony. Would appreciate some help please. This is what i've done so far. In my repository

$query = $this->getEntityManager()
    ->createQuery(
        'SELECT s
         FROM PlayoffBundle:Statsheet s
         INNER JOIN (
            SELECT day, MAX(points) AS points
            FROM PlayoffBundle:Statsheet
            GROUP BY day
        ) AS ps 
        ON s.day = ps.day AND s.points = ps.points'
     )
     ->getResult();

And this the Error Symfony returns

QueryException: [Semantical Error] line 0, col 55 near '( SELECT': Error: Class '(' is not defined.

Thanks for any help. I am still a beginner with Symfony ;)

Update.

Since i'm not that conformatable with native SQL. I decided to do this in 2 queries. 1/ With native SQL, i'll get the IDs with the max(points) 2/ With ORM, i'll just do a findById($arrayIDs). Doing like that because I have ManyToMany relations and it's easier for me to get the full data

So it almost works, but for a reason I can't undestand, my second query gives null like this :

Statsheet {#968 ▼
 -id: 20
 -stats: null
 -points: null
 -day: null
 -player: null
-game: null

}

So I did a few test. I know that ID 20 is a max value

For example, this :

dump($em->getRepository('PlayoffBundle:Statsheet')->find(19));

--> will give all the data correctly.

dump($em->getRepository('PlayoffBundle:Statsheet')->find(20));

--> will give what I just posted a few lines above (null everywhere exept for id)

But if I do a ->find(20) before the NativeSQL it gives me the data correctly.

I don't know if my explanation are clear enough. If needed, i can provide a screenshot of the dump() messages and the code of my controller/repository/entity.

StatsheetRepository.php

public function getBestPickId()
{

    $sql = 'SELECT s.* FROM pl_statsheet s INNER JOIN ( SELECT day, MAX(points) AS points FROM pl_statsheet GROUP BY day) ps ON s.day = ps.day AND s.points = ps.points';

    $rsm = new ResultSetMapping;
    $rsm->addEntityResult('PlayoffBundle:Statsheet', 's');
    $rsm->addFieldResult('s', 'id', 'id');
    $query = $this->getEntityManager()->createNativeQuery($sql, $rsm);
    $picks = $query->getResult();

    foreach($picks as $pick){
        $ids[] = $pick->getId();
    }
    return $ids;
}

And this is my controller

        $bestpicksIds = $em->getRepository('PlayoffBundle:Statsheet')->getBestPickId();
    $bestpicks = $em->getRepository('PlayoffBundle:Statsheet')->findById($bestpicksIds);

    dump($em->getRepository('PlayoffBundle:Statsheet')->find(20));
    dump($em->getRepository('PlayoffBundle:Statsheet')->find(19));
    dump($bestpicks);

So let me explain quickly . ID 19 is not a best pick but 20 is. So the ->find(19) gives all the data needed but ->find(20) and dump($bestpicks) only gives the Ids and the rest of the data is NULL Here is what my code does right now (maybe it'll help understand): Dump Symfony

I know the problem comes from the native SQL query. I don't know why it affects my next query. So that's for the update on my situation. I know what to fix and hopefully i'll come back with the solution soon ;)

I just want to add, that i've completed my nativeSQL by adding those lines :

    $rsm->addFieldResult('s', 'points', 'points');
    $rsm->addFieldResult('s', 'stats', 'stats');
    $rsm->addFieldResult('s', 'day', 'day');

but that doesn't seem to work, so far, with a foreign key such as game $rsm->addMetaResult('s', 'game_id', 'game');

Still working on it.

score 0 · Answer 1 · answered Dec 17 '16 at 12:54

0

This kind of queries will not work in DQL.

You have a couple of choices:

a) use Doctrine DBAL instead of ORM. DBAL is almost the same as working with mysql prepared statements. Results come in array's and not in objects as with ORM.

b) use Native SQL where you have to define the entities and properties that will be filled manually.

answered Dec 17 '16 at 12:54

Frank B

3,667
1
16
22

Thanks ! I went for the Native SQL way since all my project is already using ORM. I have it working but I have some issue with the result of my next query. I'll edit my main message with more info. – fly LFC Dec 17 '16 at 16:08
Maybe you can add the code that uses native queries – Frank B Dec 18 '16 at 14:22
Hi , sry for not answering any earlier but i was away the last couple of days. So I've find out where the problem is. Since I was only trying to get the "id" with my native SQL, it somehow tells symfony to only take the id for the next request. (Not sure you get what i'm saying.) Anyway. I've fixed this by selecting the whole row in the native SQL. I'll update the message ASAP – fly LFC Dec 20 '16 at 14:13

Symfony & Doctrine : DQL Max() with groupBy

1 Answers1