Пагинация в 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 можно использовать следующие способы:

  1. Выбирать все записи из базы, а потом определать по условию нужные нам. Надеюсь вы так делать не будете 🙂
  2. Использовать два запроса. Первый без LIMIT-части с count(id) для определения количества записей, и второй с LIMIT непосредственно для выборки. Очень многие делают пагинацию таким образом. Его мне и посоветовал использовать omez и не заморачиваться. Но в моем проекте я уже использовал п.4
  3. Использовать Doctrine_Pager, который в принципе делает тоже самое, что и в п.2
  4. Использовать в 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.

3 Comments

  1. помогла надеюсь
    пока еще с таким не столкнулся но теперь знаю куда смотреть 🙂

Leave a Comment