drl92080 2013-11-26 06:51
浏览 43
已采纳

如何使用Doctrine @ ORM在一个DQL查询中执行多个计数?

I am trying to do multiple counts in one DQL query using Doctrine2 ORM.

Here is what my query looks like:

$em = $this->getDoctrine()->getManager();
$query = $em->createQuery('

SELECT      COUNT(u.id) AS users,
            COUNT(t.id) AS teams,
            COUNT(p.id) AS posts,
            COUNT(c.id) AS comments
    FROM    ProjectUserBundle:User u,
            ProjectTeamBundle:Team t,
            ProjectPostBundle:Post p,
            ProjectCommentBundle:Comment c,
            ');
$counts = $query->getSingleResult();

It returns me the following result: {"users":"2648940","teams":"2648940","posts":"2648940","comments":"2648940"}

It must be something like: {"users":"17","teams":"5","posts":"190","comments":"321"}

Someone can tell me where I do it wrong?

  • 写回答

1条回答 默认 最新

  • duanju6788 2013-11-26 10:27
    关注

    With your query, DQL mixes the tables instead of counting rows in each table. The result is an erroneous number of rows. It can't explain why and how, but it always fail when I use more than one COUNT() function in a query.

    To count the right number of rows, you have to use sub-queries:

    $em = $this->getDoctrine()->getManager();
    $query = $em->createQuery('
    
    SELECT      COUNT(U.id) AS users,
                (SELECT COUNT(T) FROM ProjectUserBundle:Team T) AS teams,
                (SELECT COUNT(P) FROM ProjectUserBundle:Post P) AS posts,
                (SELECT COUNT(C) FROM ProjectUserBundle:Comment C) AS comments
        FROM    ProjectUserBundle:User U
                ');
    
    $counts = $query->getSingleResult();
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 ros的rviz仿真机器人
  • ¥15 关于#linux#的问题(输入输出错误):出现这个界面接着我重新装系统,又让修电脑的师傅帮我扫描硬盘(没有问题)用着用着又卡死(相关搜索:固态硬盘)
  • ¥15 cv::resize不同线程时间不同
  • ¥15 web课程,怎么做啊😭没好好听课 根本不知道怎么下手
  • ¥15 做一个关于单片机的比较难的代码,然后搞一个PPT进行解释
  • ¥15 python提取.csv文件中的链接会经常出现爬取失败
  • ¥15 数据结构中的数组地址问题
  • ¥15 maya的mel里,怎样先选择模型A,然后利用mel脚本自动选择有相同名字的模型B呢。
  • ¥15 Python题,根本不会啊
  • ¥15 会会信号与系统和python的来