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?

score 10 · Accepted Answer · edited Aug 06 '18 at 21:07

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

Vitalii · Answer 2 · 2017-11-06T16:10:42.700

2

You can get count by accessing to EntityPersister::count(array $criteria)

For example:

$count = $doctrineEntityManager
        ->getUnitOfWork()
        ->getEntityPersister(Entity::class)
        ->count($criteria);

edited Nov 06 '17 at 16:10

answered Aug 17 '16 at 06:59

Vitalii

1,137
14
25

Nisarg · Answer 3 · 2013-06-28T06:43:58.237

-1

May be this can be help you

Doctrine::getTable('TableName')->createQuery('t')
->where('Your Condition') ->execute() ->rowCount();

OR

Doctrine::getTable('TableName')->createQuery('t')
->where('Your Condition') ->count();

I preferred SECOND ONE

You can do like this,

$q = Doctrine_Query::create()
        ->select('ss.*')
        ->from('SalarySurvey ss')
        ->where('ss.user_id=?', $user_id)
        ->groupBy('created_at')
        ->execute();
$totalData = $q->count();

For the LIMIT

$q = Doctrine_Query::create()
        ->select('u.username, p.phonenumber')
        ->from('User u')
        ->leftJoin('u.Phonenumbers p')
        ->limit(20);

echo $q->getSqlQuery();

The Output of this query is like this ---

SELECT
    u.id AS u__id,
    u.username AS u__username,
    p.id AS p__id,
    p.phonenumber AS p__phonenumber
FROM user u
    LEFT JOIN phonenumber p
        ON u.id = p.user_id
LIMIT 20

For more details look at check here

edited Jun 28 '13 at 06:43

answered Jun 27 '13 at 14:46

Nisarg

3,024
5
32
54

What about the limit in query? – DonOfDen Jun 27 '13 at 19:43
My Question is how can I retrieve the total record count and also limit in the same query.. Your query either gives me LIMIT. I want a single query which tell the total number of count and limit the output. As I mentioned above.. either it might be two query but it should not be running the same query again and again.. – DonOfDen Jun 28 '13 at 04:39
1

do u want count() and limit() in a single query? – Nisarg Jun 28 '13 at 05:28
1

Okey, Right now i haven't resources to test but please try like this, `$q = Doctrine_Query::create() ->select('u.username, p.phonenumber') ->from('User u') ->leftJoin('u.Phonenumbers p') ->limit(20); $totalData = $q->count();` – Nisarg Jun 28 '13 at 05:59
Fatal error: Call to undefined method Doctrine\ORM\QueryBuilder::count() I am getting the above error. I am using setMaxResults($limit) instead of count... – DonOfDen Jun 28 '13 at 06:25
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/32541/discussion-between-tomphp-and-nisarg-patel) – DonOfDen Jun 28 '13 at 06:28
Why did this answer get 3 upvotes, when it is about Doctrine1 and not Doctrine2 as was questioned? – Denis V Nov 02 '13 at 07:49

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

3 Answers3

Linked