Pull to refresh

Пагинация в Doctrine — считаем количество записей с помощью SQL_CALC_FOUND_ROWS (MySQL)

Reading time 4 min
Views 14K

Предыстория


Не так давно, в связи с производственной необходимостью, я познакомился с замечательным фреймворком Symfony 2, в котором для работы с базой данных используется мощная популярная библиотека — Doctrine 2, включающая в себя два компонента: ORM (Object relational mapper) и DBAL (Database Abstraction Layer). ORM предоставляет приложению возможность общаться с базой данных на языке объектов, а DBAL, в свою очередь, представляет собой более низкоуровневый способ доступа к данным посредством написания запросов, основанный на php-библиотеке PDO. ORM предоставляет множество преимуществ при разработке сложных бизнес-приложений, но в то же время налагает и ряд ограничений, связанных с тем, что разработчику не приходится писать непосредственно SQL-запросы — ORM Doctrine предлагает свой собственный, объектно-ориентированный язык запросов, который преобразуется в привычный SQL уже за кадром. С одним из таких ограничений я и столкнулся, и хочу поделиться, каким образом я его успешно преодолел. Речь пойдёт о получении общего количества записей, возвращаемых запросом, если убрать из него ограничение LIMIT.

Задача


Наступил момент, когда я упёрся в границы объектного языка запросов DQL (Doctrine Query Language) — я хотел использовать одну функцию, предоставляющуюся MySQL, а именно — подсчёт числа записей, возвращаемых запросом, как если бы отсутствовало ограничение на количество строк в результате (LIMIT), эта функция — SQL_CALC_FOUND_ROWS. Подобная задача возникает всегда при необходимости сделать постраничный вывод некоторой информации. Конечно же, для этого можно выполнить дополнительный запрос SELECT COUNT(*), без LIMIT'а, который вернул бы необходимое общее число записей, но меня интересовал первый способ, так как он мне кажется более правильным, потому что не требует выполнения дополнительного запроса к БД и работает более быстро (основываюсь на официальной документации). Можно, конечно, долго спорить, что такой способ делает приложение зависимым от СУБД, но много ли вы видели веб-приложений, которые бы меняли СУБД как перчатки?
Собственно, способы решения проблемы пагинации в ORM Doctrine, уже были описаны в одной из хабра-статей, но то был первый Doctrine, а я работаю со вторым, и решение получилось, как мне показалось, более изящное, благодаря некоторым нововведениям.
Итак, повторю проблему, описанную моим предшественником: для того чтобы получить общее количество записей, возвращаемых запросом, необходимо после инструкции SELECT, указать ключевое слово SQL_CALC_FOUND_ROWS, говорящее MySQL о том, что ему придётся подсчитать размер результата, без учёта ограничения LIMIT. Чтобы получить требуемый размер, после выполнения запроса необходимо выполнить другой запрос: SELECT FOUND_ROWS(), который и вернёт заветное число. Однако, хочу заметить, что второй запрос не приводит к повторной выборке данных, так как информация об общем количестве строк уже известна MySQL ещё при выполнении первого запроса, здесь мы просто эту информацию считываем.
Так вот, как и было описано в статье, на которую я сослался выше, при попытке вставить в текст запроса DQL слово SQL_CALC_FOUND_ROWS:

<?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();
...
?>

генерируется SQL типа:

SELECT SQL_CALC_FOUND_ROWS AS o__0 FROM News...

Этот запрос, естественно, вызывает ошибку, так как Doctrine ожидает увидеть на этом месте название поля таблицы (точнее поле объекта-сущности).

Решение


Doctrine 2 имеет встроенный парсер запросов, который на основании DQL-запроса строит дерево (Abstract Syntax Tree), которое, в свою очередь преобразуется в SQL-запрос. Генерацию SQL обеспечивает механизм, называемый Custom AST walkers (я называю их «обходчиками»). Существует два типа «обходчиков»: Output walker и Tree walker. Output walker (может быть только один) отвечает непосредственно за генерацию SQL-текста запроса, а Tree walker (может быть сколько угодно) используются для обхода дерева AST и его модификации до того, как будет сгенерирован SQL.
Именно Output walker, и может быть использован для генерации платформо-зависимых запросов. В данном случае, я поступил следующим образом — расширил стандартный класс SqlWalker, переопределив метод WalkSelectClause следующим образом:

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

Тогда код приложения изменится следующим образом:

<?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);
...
?>


Вот и всё. Надеюсь, что кому-нибудь пригодится мой опыт.
Tags:
Hubs:
+5
Comments 5
Comments Comments 5

Articles