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?

Mitten

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

Habile

Answer 2

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

For example:

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

Downpipe

Answer 3

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

Fulmer

What about the limit in query? – Mitten

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.. – Mitten

do u want count() and limit() in a single query? – Fulmer

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

Fatal error: Call to undefined method Doctrine\ORM\QueryBuilder::count() I am getting the above error. I am using setMaxResults($limit) instead of count... – Mitten

let us continue this discussion in chat – Mitten

Why did this answer get 3 upvotes, when it is about Doctrine1 and not Doctrine2 as was questioned? – Trstram