How to use SQL_CALC_FOUND_ROWS with Zend\Db\TableGateway

Question

How to get SQL_CALC_FOUND_ROWS with Zend\Db\TableGateway without using direct low level queries with raw 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);
        });
    }
}

I wish to get total number of records in a same query used in fetchAll.

Answer 1

Looks like Zend Framework 2.1.4 has support to specify a quantifier. This enables you to use the SQL_CALC_FOUND_ROWS in a select object. One thing I did find tricky to work around is that Zend's Zend\Db\Sql\Select class will not generate the correct SQL for you if you did not specify a table. This becomes and issue when executing the subsequent select to retrieve the FOUND_ROWS(). I've updated your code below to include what I would use. I've merge my project implementation into your code, so if something does not work, its probably because I mistype something, but overall it works for me (not as desirable as I would want).

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

}