I'm trying to rewrite this command (which works in phpMyAdmin) into Symfony and Doctrine.

SELECT stand.name, if(count(checkins.id)>0,true,false) as pocet_navstev FROM stand left join (select * from checkin where checkin.nickname_id=10) as checkins on (stand.id=checkins.stand_id) GROUP by stand.id;

I tried writing it like this:

 $mycheckins = $entityManager->createQueryBuilder("sub")
            ->select("checkin.id, checkin.Nickname, checkin.Stand")
            ->from("App\Entity\Checkin", "checkin")
            ->where("checkin.Nickname=10");        
                        
        $stands = $entityManager->createQueryBuilder("main")
            ->select("stand.id", "stand.Name", "count(checkin.id)")
            ->from("App\Entity\Stand", "stand")
            ->leftJoin(sprintf("(%s)", $mycheckins->getDQL()), "checkins", "WITH", "stand.id=checkins.Stand")            
            ->groupBy("stand.id")
            ->getQuery()
            ->getResult();

But after running I get an error:

[Semantical Error] line 0, col 80 near 'JOIN (SELECT': Error: Subquery is not supported here

If I don't call ->getQuery(), but pull the SQL using getDQL(), I get this:

SELECT stand.id, stand.Name, count(checkin.id) FROM App\Entity\Stand stand LEFT JOIN (SELECT checkin.id, checkin.Nickname, checkin.Stand FROM App\Entity\Checkin checkin WHERE checkin.Nickname=10) checkins WITH stand.id=checkins.Stand GROUP BY stand.id

How can I please rewrite my PHP code to make it work?

I'm trying to rewrite this command (which works in phpMyAdmin) into Symfony and Doctrine.

SELECT stand.name, if(count(checkins.id)>0,true,false) as pocet_navstev FROM stand left join (select * from checkin where checkin.nickname_id=10) as checkins on (stand.id=checkins.stand_id) GROUP by stand.id;

I tried writing it like this:

 $mycheckins = $entityManager->createQueryBuilder("sub")
            ->select("checkin.id, checkin.Nickname, checkin.Stand")
            ->from("App\Entity\Checkin", "checkin")
            ->where("checkin.Nickname=10");        
                        
        $stands = $entityManager->createQueryBuilder("main")
            ->select("stand.id", "stand.Name", "count(checkin.id)")
            ->from("App\Entity\Stand", "stand")
            ->leftJoin(sprintf("(%s)", $mycheckins->getDQL()), "checkins", "WITH", "stand.id=checkins.Stand")            
            ->groupBy("stand.id")
            ->getQuery()
            ->getResult();

But after running I get an error:

[Semantical Error] line 0, col 80 near 'JOIN (SELECT': Error: Subquery is not supported here

If I don't call ->getQuery(), but pull the SQL using getDQL(), I get this:

SELECT stand.id, stand.Name, count(checkin.id) FROM App\Entity\Stand stand LEFT JOIN (SELECT checkin.id, checkin.Nickname, checkin.Stand FROM App\Entity\Checkin checkin WHERE checkin.Nickname=10) checkins WITH stand.id=checkins.Stand GROUP BY stand.id

How can I please rewrite my PHP code to make it work?

• php
• sql
• symfony
• doctrine-orm
• createquery

1 Answer 1

For its implicit "Why?" part, your question relates to
questions 9086546, 9831985, 29243636, 34768821, 45708320, 53867867,
or Doctrine issues #2305, #3542, #7116, or OpenClassRooms discussion.

In essence: you can't do it in DQL which is object-oriented (and needs to handle full objects, apart from the final result that can be "denormalized" to single fields); either you'll have to:

1. do it in SQL instead of DQL
   using the Connection instead of the EntityManager
   (and manually hydrating if you want results as objects)
2. or find a functionally equivalent way of doing it which does not use FROM (SELECT
   (for example, users having asked the aforementioned questions could in fine resort to EXISTS or IN)

Good news, for the "How can I?" part of your question, in your particular case (as you presented it), both options apply:

1. You do not need objects, only scalar types: SQL is a possibility
2. if I read correctly, you could directly LEFT JOIN to Checkin from the main query, adding the = 10 to the join condition.