CODEWITHSUNDEEP
sqlsymfonydoctrinedqlnative-sql
fly LFC
fly LFC

Reputation: 101

Symfony & Doctrine : DQL Max() with groupBy

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.

Upvotes: 1

Views: 1252

Answers (1)

Frank B
Frank B

Reputation: 3697

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.

Upvotes: 0

Related Questions