Doctrine查询使用LIMIT查找MySQL中的Result总数 [英] Doctrine Query to find total number of Result in MySQL with LIMIT

问题描述

我正在尝试在应用LIMIT时获取特定查询的总行数。我在PHP / MySQL中成功找到了答案，但是我无法在Zend / Doctrine中转换逻辑。我正在使用Doctrine 2.3 / Zend 1.12。

我不想使用两个不同的查询来查找结果：

PHP代码

 <？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; 
？> 
  

另外我在MySQL查询中尝试了以下内容：

尝试使用Union：

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

尝试选择：

pre> SELECT *，（来自`user`的SELECT COUNT（*），其中`user_country` ='US'）作为Count
来自`user`，其中`user_country` ='US'LIMIT 10;


以上尝试都需要Count（）：

有些人可以帮我找到解决方案。

原则：

  $ 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）; 
  

如何将上述原则代码转换成上述PHP结果查询的一些东西？有可能吗？

解决方案

有一个分页功能，内置于2.2，并做了类似于'寻求：

http://docs.doctrine-project.org/en/latest/tutorials/pagination.html

但我不相信它使用 SQL_CALC_FOUND_ROWS ：它执行两个（或三个，取决于您如何配置它）单独的查询以获取结果，这通常是正确的方法。

如果您真的坚持使用MySQL功能，我认为您需要使用原始SQL和结果集映射。以下是一个例子：

计数在Doctrine 2中的行

---

在完全独立的笔记中，测试是否 SQL_CALC_FOUND_ROWS 实际上值得使用您的特定查询。 Count在MySQL中进行了优化，可以像您正在进行的查询一样。特别看到这个问题：

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?

解决方案

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

http://docs.doctrine-project.org/en/latest/tutorials/pagination.html

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

这篇关于Doctrine查询使用LIMIT查找MySQL中的Result总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！