How to use SQL_CALC_FOUND_ROWS with Zend \ Db \ TableGateway

Question

How to get SQL_CALC_FOUND_ROWS using 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 want to get the total number of records in the same query used in fetchAll .

Answer 1

It looks like Zend Framework 2.1.4 has support for specifying a quantifier. This allows you to use SQL_CALC_FOUND_ROWS in the selected object. The only thing that seemed complicated to me was that the Zend Zend \ Db \ Sql \ Select class would not generate the correct SQL for you if you did not specify a table. This becomes and arises during the subsequent selection to retrieve FOUND_ROWS (). I updated your code below to include what I will use. I combined my project implementation into my code, so if something doesn’t work, maybe because I’m mistaken for something, but overall it works for me (not as desirable as I would like).

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

}