主义行中的行数2 [英] Count of rows in Doctrine 2
问题描述
我正在尝试从表中注释具有ID,游戏(外键)和日期的注释。
I'm trying to retrieve comments from a table Comment which has id, game (a foreign key) and date.
每次我要求提供意见时获取3个评论按照指定的游戏的日期排序,我想知道是否有更多的意见以后显示。为此,我写了两个函数,第一个返回三个注释:
Every time I ask for comments I want to get 3 comments sorted by date for a specified game and I want to know if there is more comments to show later. For that, I've written two functions, the first one returns the three comments:
public function getRecentComments($offset,$id) {
$dql = "SELECT c FROM Comment c
WHERE c.game = ?1
ORDER BY c.date DESC";
$query = $this->getEntityManager()->
createQuery($dql)->
setParameter(1, (int)$id)->
setMaxResults(3)->
setFirstResult($offset);
return $query->getResult();
第二个返回我稍后可以得到的评论数。这个功能的原因是我们显示一个按钮更多的意见不是。这是第二个功能:
And the second one returns the number of comments I could get later. The reason of this function is wehter show a button "More comments" or not. This is the second function:
public function moreComments($offset,$id) {
$dql = "SELECT COUNT(c.id) FROM Comment c
WHERE c.game = ?1
ORDER BY c.date DESC";
$query = $this->getEntityManager()
->createQuery($dql)
->setParameter(1, (int)$idPartido)
->setFirstResult($offset+3)
->setMaxResults(1)
->getSingleScalarResult();
return $query;
}
但第二个功能对下一个错误不起作用:
But the second function doesn't work for the next error:
致命错误:未捕获的异常'Doctrine\ORM\NoResultException'与消息'没有找到结果查询虽然预计至少有一行。
Fatal error: Uncaught exception 'Doctrine\ORM\NoResultException' with message 'No result was found for query although at least one row was expected.
我认为这是由于使用setFirstResult和count()。
Which I think it is due to use setFirstResult and count().
所以,我使用了
public function moreComments($offset,$id) {
$dql = "SELECT c FROM Comentario c
WHERE c.partido = ?1
ORDER BY c.fecha DESC";
$query = $this->getEntityManager()
->createQuery($dql)
->setParameter(1, (int)$idPartido)
->setFirstResult($offset+3)
->setMaxResults(1)
->getSingleScalarResult();
return sizeof($query);
}
这显然是坏的,因为我不能得到只有一个计数。我如何正确地写第二个功能?
Which obviously is bad written because I shouldn't get the data for only a count. How could I write the second function correctly?
提前感谢
推荐答案
如果你只使用MySQL,那么你可以利用它的 FOUND_ROWS()
功能。
If you will only be using MySQL, then you can take advantage of its FOUND_ROWS()
function.
这将需要使用本机查询,这很可能会妨碍您使用MySQL以外的数据库的能力,但在我的经验中工作得很好。
This will require using native queries, which will most likely hinder your ability to use a DB other than MySQL, but it works quite well in my experience.
我已经使用了如下所示的成功案例。
I have used something like the following with great success.
use Doctrine\ORM\Query\ResultSetMapping;
public function getRecentComments($offset, $id) {
$sql = "SELECT SQL_CALC_FOUND_ROWS * FROM Comment c
WHERE c.game = ?
ORDER BY c.date DESC
LIMIT ?,3";
$rsm = new ResultSetMapping();
$rsm->addEntityResult('Comment', 'c');
$rsm->addFieldResult('c', 'id', 'id');
$rsm->addFieldResult('c', 'game_id', 'game_id');
$rsm->addFieldResult('c', 'date', 'date');
$query = $this->getEntityManager()->createNativeQuery($dql, $rsm);
$query->setParameters(array(
(int)$id,
(int)$offset
));
$results = $query->getResult();
// Run FOUND_ROWS query and add to results array
$sql = 'SELECT FOUND_ROWS() AS foundRows';
$rsm = new ResultSetMapping();
$rsm->addScalarResult('foundRows', 'foundRows');
$query = $this->getEntityManager()->createNativeQuery($sql, $rsm);
$foundRows = $query->getResult();
$results['foundRows'] = $foundRows[0]['foundRows'];
return $results;
}
从上述函数获取结果数组后,我提取foundRows元素到一个单独的变量,取消设置它(即 unset($ results ['foundRows'])
),然后继续使用数组正常。
After getting the results array from the above function, I extract the 'foundRows' element to a separate variable, unset it (i.e., unset($results['foundRows'])
), and then continue using the array as normal.
希望这有帮助。
这篇关于主义行中的行数2的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!