Пагинация в Doctrine (Использование SQL_CALC_FOUND_ROWS)
Начиная от версии 4.0 в СУБД MySQL появилась достаточно удобная возможность подсчета количества всех подходящих под запрос записей, когда количество записей ограничивается LIMIT’ом. При работе с поиском в БД, а так же при выборках из таблиц с большим количеством записей такой функционал просто необходим. В этой статье я расскажу, как можно использовать эту возможность в ORM Doctrine
Сразу хотелось бы сказать, что данный способ пагинации был выбран, т.к. необходимо было реализовать совместимость с текущим проектом, где он используется.
Синтаксис
В запросе SELECT перед списком столбцов необходимо указать опцию SQL_CALC_FOUND_ROWS. Вот начало описания синтаксиса конструкции SELECT.
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr, … …
Таким образом, выполняя запрос SELECT SQL_CALC_FOUND_ROWS СУБД подсчитает полное число строк, подходящих под условие запроса, и сохранить это число в памяти. Естественно, имеет смысл запрос SELECT SQL_CALC_FOUND_ROWS только при использовании ограничения (LIMIT). Сразу после выполнения запроса на выборку для получения количества записей нужно выполнить еще один SELECT-запрос: SELECT FOUND_ROWS ();. В результате MySQL вернет одну строку с одним полем, в котором и будет храниться число строк.
Пример самих запросов:
SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE number > 100 LIMIT 10;
SELECT FOUND_ROWS();
Первый запрос вернет (выведет) 10 строк таблицы tbl_name, для которых выполняется условие number > 100. Второй вызов команды SELECT возвратит количество строк, которые возвратила бы первая команда SELECT, если бы она была написана без выражения LIMIT. Хотя при использовании команды SELECT SQL_CALC_FOUND_ROWS, MySQL должен пересчитать все строки в наборе результатов, этот способ все равно быстрее, чем без LIMIT, так как не требуется посылать результат клиенту.
Подробнее об использовании этой конструкции можно прочитать в посте kurtkrut’а и у Валерия Леонтьева.
Задача
Итак для пагинации выборки Doctrine можно использовать следующие способы:
- Выбирать все записи из базы, а потом определать по условию нужные нам. Надеюсь вы так делать не будете 🙂
- Использовать два запроса. Первый без LIMIT-части с count(id) для определения количества записей, и второй с LIMIT непосредственно для выборки. Очень многие делают пагинацию таким образом. Его мне и посоветовал использовать omez и не заморачиваться. Но в моем проекте я уже использовал п.4
- Использовать Doctrine_Pager, который в принципе делает тоже самое, что и в п.2
- Использовать в SELECT запросе выражение SQL_CALC_FOUND_ROWS
С Doctrine_Pager всё в принципе ясно. Вот пример:
// Определяем параметры постраничного вывода
$currentPage = 1; // Текущая страница
$resultsPerPage = 50; // Количество результатов на страницу// Создаем объект пагинатора на основе DQL-запроса
$pager = new Doctrine_Pager(
Doctrine_Query::create()
->from( ‘User u’ ),
$currentPage,
$resultsPerPage
);
Дальше мы получаем в объекте именно те записи, которые нам нужны для текущей страницы. Подробнее можно прочитать здесь.
Проблема
А вот с использованием SQL_CALC_FOUND_ROWS всё не так просто. Несмотря на критику этого способа пагинации (здесь и здесь), а вернее из соображений совместимости с текущим проектомвстала необходимость использовать SQL_CALC_FOUND_ROWS в Doctrine. Но тут начались сплошные свистопляски. При использовании в select() для DQL(Doctrine Query Language) запрос генерился следующий.
Из DQL
$q = Doctrine_Query::create()
->select(‘SQL_CALC_FOUND_ROWS *’)
->from(‘User u’);
Получался следующий SQL
SELECT SQL_CALC_FOUND_ROWS AS o__0 FROM User …
вот это «AS o__0» всё портило 🙂 Doctrine воспринимал SQL_CALC_FOUND_ROWS как название поля.
Решение
Не буду рассказывать про многочасовые пляски с бубном и ковыряния в недрах Doctrine, лучше напишу о том, как я решил эту проблему. Во-первых нам необходимо было как-то прокинуть SQL_CALC_FOUND_ROWS через DQL и DBAL в SQL-запрос. Это решилось с помощью использования собственного класса запроса, и изменения функций parseSelect($dql) и _buildSqlQueryBase()
<?php
class MyDoctrine_Query extends Doctrine_Query
{
public function parseSelect($dql)
{…
// check for DISTINCT keyword
if ($first === ‘DISTINCT’) {
$this->_sqlParts[‘distinct’] = true;$refs[0] = substr($refs[0], ++$pos);
}
/* Здесь мы добавляем проверку на существованеи в запросе SQL_CALC_FOUND_ROWS
и если он есть, устанавливаем в true значение одноименного параметра запроса
*/if ($first === ‘SQL_CALC_FOUND_ROWS’) {
$this->_sqlParts[‘sql_calc_found_rows’] = true;
$refs[0] = substr($refs[0], ++$pos);
}
…
}protected function _buildSqlQueryBase()
{
switch ($this->_type) {
case self::DELETE:
$q = ‘DELETE FROM ‘;
break;
case self::UPDATE:
$q = ‘UPDATE ‘;
break;
case self::SELECT:
$distinct = ($this->_sqlParts[‘distinct’])? ‘DISTINCT ‘: ”;
/* А здесь собственно добавляем выражение в запрос */
$sql_calc_found_rows = ($this->_sqlParts[‘sql_calc_found_rows’])? ‘SQL_CALC_FOUND_ROWS ‘: ”;
$q = ‘SELECT ‘ .$sql_calc_found_rows .’ ‘. $distinct. implode(‘, ‘, $this->_sqlParts[‘select’]). ‘ FROM ‘;
break;
}
return $q;
}
}
?>
Дальше необходимо указать Doctrine использовать наш класс запроса.
$manager = Doctrine_Manager::getInstance();
require_once(dirname(__FILE__). ‘/lib/doctrine_extra/MyDoctrine/Query.php’);
$manager->setAttribute(Doctrine::ATTR_QUERY_CLASS, ‘MyDoctrine_Query’);
После этого для выполнения DQL с использованием SQL_CALC_FOUND_ROWS достаточно выполнить указать его в select() части.
$q = Doctrine_Query::create()
->select(‘SQL_CALC_FOUND_ROWS *’)
->from(‘User u’)
->limit(10);
Теперь внимательный читатель спросит, а как же получить то самое количество записей которое мы получили бы без LIMIT части.
Для этого пишем свой EventListener:
<?php
class MyDoctrine_EventListener_SqlCalcFoundRows extends Doctrine_EventListener {private static $foundRows = null;
/*Вызывается тутже после выполнения запроса, что нам и надо.*/
public function postQuery(Doctrine_Event $event) {
$pdo = Doctrine_Manager::connection()->getDbh();
$sql = «SELECT FOUND_ROWS()»;
$stmt = $pdo->query($sql);
$result = $stmt->fetch();
$count = $result[‘FOUND_ROWS()’];
self::$foundRows = (int)$count;
}/* Возвращает значение количества найденных записей */
public static function getFoundRowsCount() {
return self::$foundRows;
}
}
?>
Подключаем его к системе:
require_once(dirname(__FILE__). ‘/lib/doctrine_extra/MyDoctrine/EventListener/SqlCalcFoundRows.php’);
$conn->addListener(new MyDoctrine_EventListener_SqlCalcFoundRows());
И теперь количество записей можно получить следующей командой:
MyDoctrine_EventListener_SqlCalcFoundRows::getFoundRowsCount();
Очень удобно интегрировать эту функцию в коллекию Dосtrine.
$q = Doctrine_Query::create()
->select(‘SQL_CALC_FOUND_ROWS *’)
->from(‘User u’)
->where(‘u.status = «active»)
->limit(10)
->offset(5);
$allCount = $users->getFoundRowsCount();
Для этого необходимо также отнаследовать Doctrine_Collection
<?php
class MyDoctrine_Collection extends Doctrine_Collection
{
/**
* Возвращает кол-во резщультатов предыдущего запроса с выражением SQL_CALC_FOUND_ROWS
*/
public function getFoundRowsCount() {
if(in_array(‘MyDoctrine_EventListener_SqlCalcFoundRows’, get_declared_classes())) {
return MyDoctrine_EventListener_SqlCalcFoundRows::getFoundRowsCount();
} else {
return NULL;
}
}
}
?>
и подключить её в bootstrap’е
require_once(dirname(__FILE__). ‘/lib/doctrine_extra/MyDoctrine/Collection.php’);
$manager->setAttribute(Doctrine::ATTR_COLLECTION_CLASS, ‘MyDoctrine_Collection’);
Надеюсь, что если кому-то понадобится использовать SQL_CALC_FOUND_ROWS, он найдет эту статью, и не наступит на те же грабли, что и я 🙂
Если интересно, буду писать ещё на тему, как я допиливаю Doctrine.
P.S. в разработке помогал omez.
помогла надеюсь
пока еще с таким не столкнулся но теперь знаю куда смотреть 🙂
https://sudonull.com/post/182508-Pagination-in-Doctrine-Using-SQL_CALC_FOUND_ROWS
Английский перевод поста