SQL_CALC_FOUND_ROWS + zf2

Question

I am using a simple select statement using SQL_CALC_FOUND_ROWS in zf2. The code would look like as follows and uses quantifier.

$select = $this->getSlaveSql()->select('posts');
$select->quantifier(new Expression('SQL_CALC_FOUND_ROWS'));
$select->columns([
       'total'=>new Expression("FOUND_ROWS()"),
       '*'
]);

And the generated sql is as below

SELECT SQL_CALC_FOUND_ROWS FOUND_ROWS() AS `total`, `posts`.* FROM `posts`

screenshot:

But for some reasons, found_rows is always returned as 0 and I do not want to add a second query for pagination. Please help.

Answer 1

FOUND_ROWS() is normally used in a subsequent query. I doubt you can run it in the same one that you select data in. See the example in the docs: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

Also note that SQL_CALC_FOUND_ROWS makes no sense in a query without a limit.