Get total number of rows by using 'SQL_CALC_FOUND_ROWS' and Doctrine NativeQuery

Question

I have a simple query which selects entities and uses limit statement. I am using Doctrine NativeQuery because I have FIELD() function in sql query, and I need a collection of objects as a result. That query works.

However I need also a total number of records, so I use SQL_CALC_FOUND_ROWS in the first query. After the first gets the result I create another ResultSetMapping, another $nativeQuery, execute SELECT FOUND_ROWS() AS found_rows and I keep getting total number of '1'.

$rsm = new ResultSetMapping();

$rsm->addEntityResult('\\MyCompany\\Administration\\Domain\\Model\\Applicant\\Applicant', 'a');

$rsm->addFieldResult('a', 'first_name', 'firstName');
$rsm->addFieldResult('a', 'last_name', 'lastName');

$query = $this->em->createNativeQuery('SELECT SQL_CALC_FOUND_ROWS * FROM recruitment_applicant ORDER BY FIELD(id,5,15,8,17,2,1,16,9,7,11,6,10,12,13,14,18)', $rsm);

$result = $query->getResult(); // this result is ok



$sqlCountRows = "SELECT FOUND_ROWS() AS found_rows";

$countRowsRsm = new ResultSetMapping();

$countRowsRsm->addScalarResult('found_rows', 'foundRows');

$countRowsQuery = $this->em->createNativeQuery($sqlCountRows,$countRowsRsm);

$rowsCount = $countRowsQuery->getResult();

$total = $rowsCount[0]['foundRows']; // result is '1' when it should be '16'

I used this example.

score 2 · Accepted Answer · answered Dec 17 '13 at 16:07

You don't have to use native query. FIELD() is really very easy to implement as a custom DQL function:

Read DQL User Defined Functions and How to Register Custom DQL Functions on Doctrine/Symfony documentation.

FIELD() implementation:

use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\Parser; 
use Doctrine\ORM\Query\SqlWalker;

class Field extends FunctionNode
{
    private $field = null;
    private $values = array();

    public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);

        $this->field = $parser->arithmeticPrimary();

        while (count($this->values) < 1 || $parser->getLexer()->lookahead['type'] !== Lexer::T_CLOSE_PARENTHESIS) {
            $parser->match(Lexer::T_COMMA);
            $this->values[] = $parser->arithmeticPrimary();
        }

        $parser->match(Lexer::T_CLOSE_PARENTHESIS); 
    }

    public function getSql(SqlWalker $sqlWalker)
    {
        $values = array();
        foreach ($this->values as $value) {
            $values[] = $value->dispatch($sqlWalker);
        }

        return sprintf('FIELD(%s, %s)', $this->field->dispatch($sqlWalker), implode(', ', $values));
    }
}

You won't event need a count query. However, if you'd need COUNT(*) query you can easily clone your original query and use CountWalker to create count query from select query.

score 0 · Answer 2 · answered Dec 17 '13 at 15:52

I found out what might be a cause of the problem: Symfony2 profiler, queries section, shows total of 22 queries executed. My first query gets run third in a row and my second query, the one to return the number of rows gets executed 13th. SQL_CALC_FOUND_ROWS works if SELECT FOUND_ROWS() is run immediately after the first query.

Get total number of rows by using 'SQL_CALC_FOUND_ROWS' and Doctrine NativeQuery

2 Answers2