Doctrine pagination - count the number of records using SQL_CALC_FOUND_ROWS (MySQL)

Prehistory

Not so long ago, due to production needs, I became acquainted with the wonderful Symfony 2 framework, which uses a powerful popular library for working with a database - Doctrine 2, which includes two components: ORM (Object relational mapper) and DBAL (Database Abstraction) Layer). ORM provides the application with the ability to communicate with the database in the object language, and DBAL, in turn, is a lower-level way to access data by writing queries based on the PDO php library. ORM provides many advantages in developing complex business applications, but at the same time imposes a number of limitations due to the fact that the developer does not have to write SQL queries directly - ORM Doctrine offers its own object-oriented query language, which is converted to familiar SQL is already behind the scenes. I encountered one of these limitations, and I want to share how I successfully overcame it. It will be about getting the total number of records returned by the query, if you remove the LIMIT from it.

Task

The moment came when I rested against the boundaries of the DQL object language (Doctrine Query Language) - I wanted to use one function provided by MySQL, namely, counting the number of records returned by the query, as if there were no limit on the number of rows in the result (LIMIT) This function is SQL_CALC_FOUND_ROWS. A similar problem always arises when it is necessary to make a paginal output of some information. Of course, for this you can perform an additional SELECT COUNT (*) query, without LIMIT, which would return the required total number of records, but I was interested in the first method, since it seems to me more correct, because it does not require an additional query to DB and works more quickly (based on official documentation ). You can, of course, argue for a long time that such a method makes the application dependent on the DBMS, but have you seen many web applications that change the DBMS like gloves?
Actually, solutions to the pagination problem in ORM Doctrine have already been described in one of the habr articles , but this was the first Doctrine, and I work with the second, and the solution turned out to be more elegant, thanks to some innovations.
So, I repeat the problem described by my predecessor: in order to get the total number of records returned by the query, you need after the SELECT statement, specify the keyword SQL_CALC_FOUND_ROWS, telling MySQL that it will have to calculate the size of the result, not taking into account the LIMIT limit. To get the required size, after executing the query, you must perform another query: SELECT FOUND_ROWS (), which will return the treasured number. However, I want to note that the second query does not result in the repeated sampling of data, since the information on the total number of rows is already known by MySQL even when the first query is executed, here we simply read this information.
So, as described in the article to which I referred above, when I try to insert the word SQL_CALC_FOUND_ROWS into the DQL query text:

<?php
$query = $em->createQuery("SELECT SQL_CALC_FOUND_ROWS n FROM MyProject\Entity\News n WHERE n.date > '2011-01-01'");
$query->setMaxResults(10);
$news = $query->getResult();
...
?>

Generated SQL type:

SELECT SQL_CALC_FOUND_ROWS AS o__0 FROM News...

This query, of course, causes an error, since Doctrine expects to see the name of the table field in this place (more precisely, the field of the entity object).
')

Decision

Doctrine 2 has a built-in query parser, which builds a tree (Abstract Syntax Tree) based on a DQL query, which in turn is converted to a SQL query. SQL generation is provided by a mechanism called Custom AST walkers (I call them "crawlers"). There are two types of “walkers”: Output walker and Tree walker. The output walker (there can be only one) is directly responsible for generating the SQL text of the request, and the Tree walker (maybe as many as you like) is used to bypass the AST tree and modify it before SQL is generated.
It is the Output walker that can be used to generate platform-specific queries. In this case, I did the following: I expanded the standard class SqlWalker by redefining the WalkSelectClause method as follows:

<?php

namespace MyProject\Entity\Walkers;

use Doctrine\ORM\Query\SqlWalker;

class MysqlPaginationWalker extends SqlWalker {

/**
* Walks down a SelectClause AST node, thereby generating the appropriate SQL.
*
* @param $selectClause
* @return string The SQL.
*/
public function walkSelectClause($selectClause)
{
$sql = parent::walkSelectClause($selectClause);

if ($this->getQuery()->getHint('mysqlWalker.sqlCalcFoundRows') === true) {
if ($selectClause->isDistinct) {
$sql = str_replace('SELECT DISTINCT', 'SELECT DISTINCT SQL_CALC_FOUND_ROWS', $sql);
} else {
$sql = str_replace('SELECT', 'SELECT SQL_CALC_FOUND_ROWS', $sql);
}
}

return $sql;
}
}
...
?>

Then the application code will change as follows:

<?php
$query = $em->createQuery("SELECT n FROM MyProject\Entity\News n WHERE n.date > '2011-01-01'");
$query->setMaxResults(10);

// Doctrine,
// , ,
$query->setHint(
\Doctrine\ORM\Query::HINT_CUSTOM_OUTPUT_WALKER,
MyProject\\Entity\\Walkers\\MysqlPaginationWalker'
);
$query->setHint("mysqlWalker.sqlCalcFoundRows", true);

$news = $query->getResult();

// ,
$totalCount = $em->getConnection()->query('SELECT FOUND_ROWS()')->fetchColumn(0);
...
?>


That's all. I hope that someone will benefit from my experience.