Doctrine 2 中的行数 [英] Count of rows in Doctrine 2

问题描述

我正在尝试从具有 ID、游戏(外键)和日期的 Comment 表中检索评论.

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:

致命错误:未捕获的异常DoctrineORMNoResultException"，消息为尽管预计至少有一行，但未找到查询结果.

Fatal error: Uncaught exception 'DoctrineORMNoResultException' 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 DoctrineORMQueryResultSetMapping;

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.

希望这会有所帮助.

这篇关于Doctrine 2 中的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！