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();
Improve this question
2
  • 1
    If you just keep count in your select ->select('COUNT(t.katId)'); it's work ? just for test Commented Apr 4, 2017 at 8: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" }...... Commented Apr 4, 2017 at 9:15

2 Answers 2

Reset to default
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 ?

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

If you need keep getSingleScalarResult you must delete groupBy. If you need keep groupBy you must replace singleScalar by getScalarResult. Working ?
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] =.....
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.
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).
and maybe is better for result array key use -> select('count(t.katId) as myNiceKey', 't.hotel'...)
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.

Improve this answer

1 Comment

I was try keep in select only count(t.katId) + remove setMaxResults(10) and get $query->getQuery()->getSingleScalarResult(); but same exception.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.