mysql - 如何在 Zend\Db\TableGateway 中使用 SQL_CALC_FOUND_ROWS

Question

如何使用 Zend\Db\TableGateway 获取 SQL_CALC_FOUND_ROWS，而不使用原始 SQL 的直接低级查询？

class ProductTable {
    protected $tableGateway;

    /**
     * Set database gateway
     *
     * @param TableGateway $tableGateway - database connection
     * @return void
     */
    public function __construct(TableGateway $tableGateway) {
        $this->tableGateway = $tableGateway;
    }


    /**
     * Fetch all products
     *
     * @param integer $page - page of records
     * @param integer $perpage - records per page
     * @return void
     */
    public function fetchAll($page = 1, $perpage = 18) {
        return $this->tableGateway->select(function (Select $select) use ($page, $perpage) {
            $select
                ->limit($perpage)
                ->offset(($page - 1) * $perpage);
        });
    }
}

我希望获得 fetchAll 中使用的同一查询中的记录总数。

Answer 1

看起来 Zend Framework 2.1.4 支持指定量词。这使您能够在选择对象中使用 SQL_CALC_FOUND_ROWS。我确实发现很难解决的一件事是，如果您没有指定表，Zend 的 Zend\Db\Sql\Select 类将不会为您生成正确的 SQL。这在执行后续选择以检索 FOUND_ROWS() 时成为问题。我已经在下面更新了您的代码以包含我将使用的内容。我已将我的项目实现合并到您的代码中，所以如果某些地方不起作用，可能是因为我输错了某些东西，但总的来说它对我有用(不像我想要的那样理想)。

use Zend\Db\Sql\Expression;
use Zend\Db\Sql\Select;

class ProductTable {
protected $tableGateway;

/**
 * Set database gateway
 *
 * @param TableGateway $tableGateway - database connection
 * @return void
 */
public function __construct(TableGateway $tableGateway) {
    $this->tableGateway = $tableGateway;
}


/**
 * Fetch all products
 *
 * @param integer $page - page of records
 * @param integer $perpage - records per page
 * @return void
 */
public function fetchAll($page = 1, $perpage = 18) {
    $result = $this->tableGateway->select(function (Select $select) use ($page, $perpage) {
        $select
            ->quantifier(new Expression('SQL_CALC_FOUND_ROWS'))
            ->limit($perpage)
            ->offset(($page - 1) * $perpage);
    });

    /* retrieve the sql object from the table gateway */
    $sql = $this->tableGateway->getSql();

    /* create an empty select statement passing in some random non-empty string as the table.  need this because Zend select statement will
    generate an empty SQL if the table is empty. */
    $select = new Select(' ');

    /* update the select statement specification so that we don't incorporate the FROM clause */
    $select->setSpecification(Select::SELECT, array(
        'SELECT %1$s' => array(
            array(1 => '%1$s', 2 => '%1$s AS %2$s', 'combinedby' => ', '),
            null
        )
    ));

    /* specify the column */
    $select->columns(array(
        'total' => new Expression("FOUND_ROWS()")
    ));

    /* execute the select and extract the total */
    $statement = $sql->prepareStatementForSqlObject($select);
    $result2 = $statement->execute();
    $row = $result2->current();
    $total = $row['total']';

    /* TODO: need to do something with the total? */

    return $result;
}