3

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

for example here

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

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.

EDIT:

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.

SOLUTION

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.

I will be grateful if someone offers better solution

  1. Get results:

    • removed COUNT() from select
    • asking for results changed to 'normal' ->getArrayResults

  2. Get count:

    $q = clone $query;

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

echo $r[0]['count'];
exit();
Community
  • 1
  • 1
Lajdák Marek
  • 2,969
  • 8
  • 29
  • 58
  • 1
    If you just keep count in your select ->select('COUNT(t.katId)'); it's work ? just for test – François MARTIN-BROSSAT Apr 04 '17 at 08:58
  • Nope. I keep in select only `count(t.katId)` and try `->getResult()` and `->getArrayResult()` but i get array with a lot of indexes and values like array(144) { [0]=> array(1) { [1]=> string(3) "111" } [1]=> array(1) { [1]=> string(3) "226" } [2]=> array(1) { [1]=> string(3) "151" }...... – Lajdák Marek Apr 04 '17 at 09:15

2 Answers2

1

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

Edit : New edit works ?

François MARTIN-BROSSAT
  • 131
  • 8
  • If you need keep getSingleScalarResult you must delete groupBy. If you need keep groupBy you must replace singleScalar by getScalarResult. Working ? – François MARTIN-BROSSAT Apr 04 '17 at 10:01
  • If i use solution 'keep the groupBy' (`->getScalarResult()` returns array so no echo but print_r()) i get the same array as with `->getArrayResult` something like: Array ( [0] => Array ( [1] => 111 ) [1] => Array ( [1] => 226 ) [2] => Array ( [1] => 151 ) [3] =..... – Lajdák Marek Apr 04 '17 at 10:09
  • 1
    I try to defined your result array key because the SQL request is correct, but the key don't display the name of hotel... try the new edit. – François MARTIN-BROSSAT Apr 04 '17 at 10:29
  • We are move on :) Array result key is not significant for me. But count your query show me how many rows with same `t.hotel` for result `t.hotel` i have in table. So in result i have 10 hotels and in count i see how much records `t.hotel` i have for same value `t.hotel` in table... but my point was see how many results rows i will get from this select (one integer) without pull all rows (without limit). – Lajdák Marek Apr 04 '17 at 11:50
  • and maybe is better for result array key use `-> select('count(t.katId) as myNiceKey', 't.hotel'...)` – Lajdák Marek Apr 04 '17 at 12:00
0

You are only interested in COUNT(t.katId), so you should drop other returned fields 't.hotel', 't.title', etc. The result will then contain a single return value (single scalar result), so $query->setMaxResults(10) is not needed.

CptSadface
  • 61
  • 3
  • I was try keep in select only `count(t.katId)` + remove `setMaxResults(10)` and get `$query->getQuery()->getSingleScalarResult();` but same exception. – Lajdák Marek Apr 04 '17 at 09:20