mysql 查询到 querybuilder

Question

有没有一种方法可以使查询生成器类似于 Mysql 中的下一个查询生成器:

"SELECT a.* FROM fsa_areas ORDER BY (SELECT count(*) FROM fsa_areas a1 WHERE a1.Responsible = a.Responsible) DESC LIMIT 1;

我正在尝试这个:

$em->getRepository('FSABundle:FsaAreas')->createQueryBuilder('u')
        ->select('u.name, u.responsible')
        ->where('u.enabled = true')
        ->orderBy('  ','DESC')
        ->getQuery()->getResult();

但我不知道是否可以在 Querybuilder 中执行与 Mysql 中类似的 OrderBy 操作？

Answer 1

您可以像这样重写查询:

SELECT a.* FROM fsa_areas a
LEFT JOIN fsa_areas a1 ON(a1.responsible = a.responsible)
GROUP BY a.id
ORDER BY COUNT(a1.id) DESC;

并将其转换为查询生成器:

$entityManager
        ->getRepository(FsaAreas::class)
        ->createQueryBuilder('a, COUNT(a1) AS HIDDEN cnt')
        ->select('a')
        ->leftJoin(FsaAreas::class, 'a1', Expr\Join::WITH, 'a1.responsible = a.responsible')
        ->groupBy('a')
        ->orderBy('cnt', 'DESC')
        ->getQuery()
        ->getResult();