通过getSingleScalarResult()和groupBy()获取计数查询结果-异常 [英] Get count query results via getSingleScalarResult() and groupBy() - exception

问题描述

我想获取查询的结果(限制为10个)+计算可能的结果.我知道也有类似的问题和答案.

I want get results of my query (with limit 10) + count possible results. I know there is similar questions and answers.

例如此处

但是，如果我尝试计数可能的行(通过 getSingleScalarResult())，我将获得例外:查询返回了多行.更改查询或使用其他结果函数，如getScalarResult().

but if i trying get count possible rows (via getSingleScalarResult()) i will get excepton: The query returned multiple rows. Change the query or use a different result function like getScalarResult().

    $query = $repository
        ->createQueryBuilder('t')
        ->select('COUNT(t.katId)', 't.hotel', 't.title', 't.desc', 'picture', 'MIN(t.price) AS price');

    $query->where('t.visible = (:visible)')->setParameter('visible', 1);

    // + some wheres, where in, more than....

    $query->groupBy('t.hotel');
    $query->setMaxResults(10);

    echo $query->getQuery()->getSingleScalarResult();
    exit();

我只需要一个整数即可代表查询的所有结果.我如何获得此计数数字?一次射入db的理想选择.

I just need one integer whitch represent all results from my query. How can i get this count number? Ideal in one shot to db.

如果我删除 $ query-> groupBy('t.hotel'); 并在选择中仅保留-> select('count(t.katId)'); 然后生效.但是我需要 groupBy ，因为它可以真正计数结果.

if i remove $query->groupBy('t.hotel'); and in select keep only ->select('count(t.katId)'); then it work. But i need groupBy because it makes real count of results.

解决方案

我将其划分为两个查询，以便-为了获得结果，在尝试任何计数信息之前，我将更改回滚到状态，并克隆了该查询(在设置 setMaxResults 和 groupBy )，更改选择(保留所有位置)并获取计数信息.

I divided it on two queries so - to get results i rolled back changes to state before trying any count information, and make clone this query (before set setMaxResults and groupBy), change select (keep all wheres) and get count information.

如果有人提供更好的解决方案，我将不胜感激

1. 获取结果:

1. Get results:

• 从选择中删除了COUNT()
• 要求结果更改为正常" -> getArrayResults

获取数量:

$q = clone $query;

$q->select('count(distinct t.hotel) as count');
$r = $q->getQuery()->getArrayResult();

echo $r[0]['count'];
exit();

推荐答案

如果需要，请保留groupBy:

If you need keep the groupBy:

$query = $repository->createQueryBuilder('t')
$query->select('COUNT(t.katId)', 't.hotel', 't.title', 't.desc', 'picture', 'MIN(t.price) AS price');
$query->from(ENTITY STRING, 't', 't.hotel');  //here defined your array result key
$query->where('t.visible = (:visible)')->setParameter('visible', 1);
$query->groupBy('t.hotel');
$query->setMaxResults(10);

echo $query->getQuery()->getScalarResult();
exit();

新的编辑作品?

这篇关于通过getSingleScalarResult()和groupBy()获取计数查询结果-异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！