Doctrine Query to find total number of Result in MySQL with LIMIT

Question

I am trying to fetch the total number of rows found for specific query when LIMIT is applied. I successfully found the answer in PHP/MySQL, But I am not able to conver the logic in Zend/Doctrine. I am working with Doctrine 2.3/Zend 1.12.

I dont want to use two different Queries to find the result:

PHP CODE:

<?php
$con = mysql_connect('localhost', 'root', '');
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("myproject", $con);

$sql = "SELECT SQL_CALC_FOUND_ROWS * FROM `user` WHERE `user_country`='us' LIMIT 10";
$result = mysql_query($sql);
$sql = "SELECT FOUND_ROWS() AS `found_rows`;";
$rows = mysql_query($sql);
$rows = mysql_fetch_assoc($rows);
$total_rows = $rows['found_rows'];
echo $total_rows;
?>

Also I tried the Following in MySQL Query:

Try with Union:

    SELECT COUNT( * ) FROM  `user` WHERE  `user_country` =  'US' 
UNION SELECT `user_id` FROM `user` WHERE `user_country` = 'US' LIMIT 10;

Try with Select:

    SELECT  *,(SELECT COUNT(*) from `user` where `user_country`='US') as Count 
from `user` where `user_country`='US' LIMIT 10;

Both the Above try takes time for Count():

Can some one help me to find the solution..

Doctrine:

$qry = $this->manager()->createQueryBuilder()
                    ->from($this->entity, 'e')
                    ->select('e');
$qry->where('e.user_country = :country');
$qry->setParameter('country', 'us');
$limit='10';
$qry->setMaxResults($limit);

How can I convert the above doctrine code some thing like the above PHP result Query? Is it possible?

Answer 1

There is a pagination feature, which is built-in in 2.2, and does something similar to what you're seeking:

https://www.doctrine-project.org/projects/doctrine-orm/en/latest/tutorials/pagination.html#pagination

But I do not believe it uses SQL_CALC_FOUND_ROWS: it does two (or three, depending on how you configure it) separate queries to get the results, and that is frequently the correct way to proceed.

If you really insist on using the MySQL feature, I think you need to use raw SQL and a result set mapping. Here's an example:

Count of rows in Doctrine 2

---

On a completely separate note, test whether SQL_CALC_FOUND_ROWS is actually worth using for your particular query. Count is well optimized in MySQL for queries like the one you're doing. See this question in particular:

Which is fastest? SELECT SQL_CALC_FOUND_ROWS FROM `table`, or SELECT COUNT(*)