Pagination in Doctrine - count the number of records using SQL_CALC_FOUND_ROWS (MySQL)
Background
Not so long ago, due to production needs, I met with the wonderful Symfony 2 framework, which uses the powerful popular library Doctrine 2 to work with the database, which includes two components: ORM (Object relational mapper) and DBAL (Database Abstraction Layer). ORM gives an application the ability to communicate with a database in the language of objects, 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 when developing complex business applications, but at the same time it also imposes a number of restrictions related 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 already behind the scenes. I faced 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 request, if we remove the LIMIT restriction from it.
Task
The time came when I ran into the boundaries of the object query language DQL (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 was no limit on the number of rows as a result (LIMIT) , this function is SQL_CALC_FOUND_ROWS. A similar problem always arises when it is necessary to make a pagination of some information. Of course, for this you can execute an additional SELECT COUNT (*) query, without LIMIT, which would return the necessary 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 be executed. The database also works more quickly (based on official documentation) Of course, one can argue for a long time that this method makes the application dependent on the DBMS, but have you seen many web applications that would change the DBMS like gloves?
Actually, methods for solving the pagination problem in Doctrine ORM were already described in one of the Habr-articles , but that was the first Doctrine, and I work with the second, and the solution turned out, it seemed to me, 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 to specify the SQL_CALC_FOUND_ROWS keyword after the SELECT statement, telling MySQL that it will have to calculate the size of the result, without taking into account the LIMIT limit. To get the required size, after executing the query, you need to execute another query: SELECT FOUND_ROWS (), which will return the cherished number. However, I want to note that the second query does not lead to re-fetching the data, since MySQL already knows the information about the total number of rows when the first query was executed, here we just read this information.
So, as described in the article I referred to above, when trying to insert the word SQL_CALC_FOUND_ROWS into the text of a DQL query:
$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();
...
?>
type SQL is generated: This query, naturally, causes an error, since Doctrine expects to see the name of the table field at this place (more precisely, the field of the entity object).
SELECT SQL_CALC_FOUND_ROWS AS o__0 FROM News...
Decision
Doctrine 2 has a built-in query parser, which, based on a DQL query, builds a tree (Abstract Syntax Tree), which, in turn, is converted to an SQL query. SQL generation is provided by a mechanism called Custom AST walkers (I call them "crawlers"). There are two types of crawlers: Output walker and Tree walker. The output walker (there can be only one) is directly responsible for generating the SQL text of the query, and the Tree walker (can be as many as you like) are used to traverse the AST tree and modify it before the SQL is generated.
It is the Output walker, and can be used to generate platform-specific requests. In this case, I did the following - expanded the standard SqlWalker class by overriding the WalkSelectClause method as follows:
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: That's all. I hope that my experience will be useful to someone.
$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);
...
?>