Symfony与Doctrine：含子查询LEFT JOIN的SQL转DQL问题

原SQL语句（在phpMyAdmin中可正常运行）：

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;

尝试用Doctrine Query Builder改写的代码：

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

运行后报错：

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

通过getDQL()生成的语句：

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

Doctrine的DQL不支持在JOIN子句中直接嵌套子查询作为数据源，可通过以下几种方式改写：

方法1：将子查询逻辑移到JOIN的ON条件中 #

把原SQL中子查询的筛选条件直接放到LEFT JOIN的关联条件里，效果和原SQL一致，同时符合DQL语法规则：

$stands = $entityManager->createQueryBuilder("stand")
    ->select("stand.id", "stand.Name", "CASE WHEN COUNT(checkin.id) > 0 THEN TRUE ELSE FALSE END AS pocet_navstev")
    ->from("App\Entity\Stand", "stand")
    ->leftJoin("App\Entity\Checkin", "checkin", "WITH", "stand.id = checkin.Stand AND checkin.Nickname = 10")
    ->groupBy("stand.id")
    ->getQuery()
    ->getResult();

方法2：使用原生SQL查询 #

直接复用原SQL语句，通过Doctrine执行原生查询，适合需要完全保留原SQL结构的场景：

$sql = "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";

$stands = $entityManager->getConnection()->executeQuery($sql)->fetchAllAssociative();

注意：原生SQL返回的是数组而非实体对象，会失去ORM的类型转换和实体映射能力。

方法3：利用实体关联简化查询 #

如果Stand和Checkin实体已配置双向关联（比如Stand实体中定义了$checkins关联属性），可以用关联直接改写：

$stands = $entityManager->createQueryBuilder("stand")
    ->select("stand.id", "stand.Name", "CASE WHEN COUNT(checkin.id) > 0 THEN TRUE ELSE FALSE END AS pocet_navstev")
    ->leftJoin("stand.checkins", "checkin", "WITH", "checkin.Nickname = 10")
    ->groupBy("stand.id")
    ->getQuery()
    ->getResult();

这种写法更符合ORM使用习惯，代码更简洁。

内容的提问来源于stack exchange，提问作者Kamil Pošvic