Yii PHP framework: связанные таблицы и limit

Владимир | | MySQL, PHP, Web разработка, Yii.

yii active record limit

Давно я собирался написать этот пост, практически сразу после выхода цикла о создании игрового сайта, но как-то всё время откладывал.

В новых версиях фреймворка Yii (1.1.х) немного изменился принцип использования встроенной библиотеки для работы с базой данных, точнее она стала по-другому формировать SQL запросы при использовании связанных таблиц.

Речь идет об этом изменении.


По умолчанию для всех отношений, включенных в 'жадную' загрузку, будет сгенерировано и выполнено одно выражение с использованием JOIN. Если в основной таблице есть опции запроса LIMIT или OFFSET, то сначала будет выполнен этот запрос, а затем другой SQL-запрос, который возвращает все связанные объекты. Раньше, в версии 1.0.x, по умолчанию вызывалось N+1 SQL-запросов, если 'жадная' загрузка включала N отношений HAS_MANY или MANY_MANY.
(перевод взят отсюда)

В моем примере с игровым сайтом как раз возникла такая ситуация. Есть две таблицы, с играми (ygs_games) и их жанрами (ygs_types). Отношение между таблицами многие-ко-многим.

При этом необходимо выводить игры определённого жанра с разбивкой на страницы (пагинацией), т.е. использовать в запросе limit и offset.

Yii позволяет сформировать запросы на получение этих данных двумя способами, которые называются: «жадная» загрузка и «ленивая» загрузка. Первый предполагает формирование одного запроса, в котором будут получены все необходимые данные. В этом запросе будут использованы объединения (JOINs). Во втором случае используется несколько запросов. Сначала выбираются нужные записи из первой таблицы (первый запрос), затем — данные из связанной таблицы (для каждой записи из первой таблицы выполняется дополнительный запрос).

Проблема, с которой я столкнулся.

В старых версиях для этих целей можно было использовать следующий код.

$criteria=new CDbCriteria;
$criteria->condition = 't_id=:t_id';
$criteria->params = array(':t_id'=>$_GET['type_id']);
$criteria->with = array('ygs_types'=>array('together'=>true));

$pages=new CPagination(Games::model()->published()->count($criteria));
$pages->pageSize=self::PAGE_SIZE;
//этот метод добавляет параметры limit и offset в объект $criteria, т.е. в запрос
$pages->applyLimit($criteria);

$models=Games::model()->findAll($criteria);

здесь t_id — первичный ключ в таблице жанров.
ygs_types — название элемента в массиве, который возвращает метод relations() модели (этот элемент просто описывает отношение многие-к-многим).

При этом формировался один запрос по методу «жадной» загрузки, который возвращал все необходимые данные.

Но в новых версиях Yii этот код не работает.

Дело в том, что как только мы указываем LIMIT или OFFSET в запросе, который включает связанные таблицы, библиотека Yii разбивает запрос на два. Сначала выполняется запрос только к первой таблице и именно к нему применяется LIMIT.

SELECT `t`.`g_id` AS `t0_c0`, ... FROM `ygs_games` `t`  WHERE ((g_state=0) AND
(t_id=:t_id)) LIMIT 10. Bind with parameter :t_id='2'

И сразу же возникает ошибка.

Column not found: 1054 Unknown column 't_id' in 'where clause'

Причина ошибки в том, что yii пытается вставить параметр для поля t_id, которого нет в таблице ygs_games. Таблица ygs_types будет присоединена в следующем запросе (с помощью JOIN), но LIMIT применяется именно в первом запросе к первой таблице, а нужно, чтобы он применялся к результату объединённого запроса.

Отключить это поведение, судя по всему, нельзя.

Но можно использовать «ленивую» загрузку, и при этом будет выполняться также два запроса.

Первым запросом мы находим нужный жанр в таблице ygs_types.

$type = Types::model()->findByPk($_GET['type_id']);

Тут выполняется следующий запрос

SELECT * FROM `ygs_types` `t` WHERE `t`.`t_id`=8 LIMIT 1

Затем, получаем связанные с этим жанром игры.
Здесь есть нюанс. Вызов

$type->ygs_games

нам не подходит, т.к. мы не сможем указать параметры, например тот же limit (если нужно его изменять).

Примечание. Можно, конечно, указать параметры в массиве, который возвращает метод relations, но этот метод имеет свои недостатки. Например, чтобы изменить параметры (тот же limit), их придется хранить в отдельном массиве, и его нужно будет объединять с массивом, который возвращает relations().

Поэтому, на мой взгляд, удобнее использовать метод getRelated, в его третьем параметре можно передать массив с настройками.
Код будет выглядеть так.

$params = array(
        'limit'=>self::PAGE_SIZE,
        'condition'=>'g_state='.Games::PUBLISHED,
        'order'=>'g_added DESC',
);
$games = $type->getRelated('ygs_games',false, $params);

При этом Yii формирует следующий запрос (self::PAGE_SIZE = 10)

SELECT `ygs_games`.`g_id` AS `t1_c0`, ... FROM `ygs_games` `ygs_games` INNER JOIN
`ygs_games_types` `ygs_games_ygs_games` ON
(`ygs_games_ygs_games`.`gt_type_id`=:ypl0) AND
(`ygs_games`.`g_id`=`ygs_games_ygs_games`.`gt_game_id`) WHERE (g_state=0)
ORDER BY g_added DESC LIMIT 10. Bind with parameter :ypl0='8'

Т.е. именно то, что нам нужно.

Тут есть один недостаток — усложняется код настройки пагинации. Мы должны вручную установить параметры limit и offset.

Полностью метод, формирующий страницу с играми одного жанра выглядит так.

public function actionShowGames()
{
	if (isset($_GET['type_id']) && is_numeric($_GET['type_id'])) {
		$criteria = new CDbCriteria;
		
		$type = Types::model()->findByPk($_GET['type_id']);
		//нужно использовать "ленивую" загрузку (не использовать with)
		//иначе не получится указать limit для связанной таблицы
		$params = array(
			'limit'=>self::PAGE_SIZE,
			'condition'=>'g_state='.Games::PUBLISHED,
			'order'=>'g_added DESC',
		);
		//настраиваем пагинацию
		if (isset($_GET['page']) && is_numeric($_GET['page'])) {
			$params['offset'] = ($_GET['page'] - 1) * self::PAGE_SIZE;
		}
		$pages=new CPagination(count($type->getRelated('ygs_games')));
		$pages->pageSize=self::PAGE_SIZE;
		
		$games = $type->getRelated('ygs_games',false, $params);
		
		$this->render('showGames'
			,array('games'=>$games, 'pages'=>$pages));
	} else {
		$this->redirect('/games/list');
	}
}

Обратите внимание на строки 16 и 18. В них мы устанавливаем смещение (offset) и создаём объект CPagination. Последнему передаём количество всех игр данного жанра. Для этого просто используем функцию count.

Если кому-то захочется поэкспериментировать, выкладываю архив с изменённым примером.

Source

Чтобы удобнее было сравнивать работу библиотеки, я не удалял старый код создания страниц с жанрами (GamesController.php метод actionList). Новый метод находится в контроллере TypesController.php (метод actionShowGames).

Если есть вопросы или замечания, пишите. Мне будет очень интересно обсудить эту тему 😉

  • А какой метод быстрее?
    «жадная» загрузка или «ленивая» загрузка?

  • Это больная тема, я ограничиваюсь «ручным» присоединением таблицы через join в критерии. Недостатка с подсчетами для пагинации нет.

  • simplecoding

    Однозначного ответа нет, нужно сравнивать сами запросы. В данном случае разницы практически нет, т.к. все-равно выполняется два запроса одинаковой сложности (во втором два inner join).

  • simplecoding

    Я вообще, когда первый раз столкнулся с проблемой, просто написал нужный sql запрос 😉 Но, хотелось по максимуму использовать встроенную библиотеку. Наверное, во многих случаях, ваше решение окажется предпочтительнее.

  • BerdArt

    спасибо за статью.
    а подскажите плиз, есть ли возможность, например, после выполнения запроса вывести сам запрос? (что-то по типа last_query в CodeIgniter)

  • ksergey94

    disqus… прикольно.

  • Да, есть. Для этого нужно сделать несколько настроек в конфиге. Пример
    здесь

  • Мне тоже нравится 🙂

  • Я у себя на сайте тоже прикрутил. И уведомление на почту пришло, что на коммент ответили)))

  • Мне больше всего нравиться, что прямо из почты можно ответить 🙂

  • Еще круче))

  • А причем тут лимит?

  • Если в основной таблице есть опции запроса LIMIT или OFFSET, то сначала будет выполнен этот запрос, а затем другой SQL-запрос, который возвращает все связанные объекты.

    При этом в первом запросе нельзя задать условия для присоединяемой таблицы.

  • Помнится писал я по этому поводу в багтрекер. Макаров сначала записал это в фичи, а теперь со всем этим поведением что-то хотят сделать, но только уже в версии 1.1.3

  • Смотря как построена БД, какие индексы. Лучше всего — сделать бенчмарк под свои потребности и на конкретном сервере смотреть. С left join могут быть проблемы из-за ограничений в конфиге(мне как то попадалось). Так что лучше тестить, тестить и ещё раз тестить

  • По-моему, такие переходы не добавляют популярности фреймворку. Такое
    чувство, что с бетой работаешь.

  • По-моему, такие переходы не добавляют популярности фреймворку. Такое
    чувство, что с бетой работаешь.

  • Alexy4b

    Это проверка регистрации комментария без регистрации проекте «DISQUS». Сообщение за номером 1.

    • Alexy4b

      Это проверка регистрации комментария без регистрации проекте «DISQUS». Сообщение за номером 2.

  • Дмитрий

    пытаюсь переписать код под dataProvider, ниже привожу свой код


    $dataProvider=new CActiveDataProvider('Games', array(
    'criteria'=>$criteria,
    'pagination'=>array('pageSize'=>10,'pageVar'=>'page'),
    ));

    foreach ($dataProvider->getData() as $key=>$game) {
    //расшифровываем жанры игр (по коду в поле g_type)
    //$models[$key]->g_types = $this->_decodeTypes($game->g_type);
    $dataProvider->setKeys($key)->g_types = $this->_decodeTypes($game->g_type);
    }

    всё работает отлично, за исключением расшифрования жанров, тут я заменил ваш код

    $models[$key]->g_types = $this->_decodeTypes($game->g_type);

    на свой

    $dataProvider->setKeys($key)->g_types = $this->_decodeTypes($game->g_type);

    или я не правильно присваиваю?

  • Попробуйте использовать свойство data вместо вместо метода setKeys.

  • Дмитрий

    пробывал, делал так
    $dataProvider->setData($key)->g_types = $this->_decodeTypes($game->g_type);

    но ничего не вышло, ошибок вроде не было, да и данные не записывались

    думаю эта штука с dataProvider не пройдёт. Пришлось свой класс пагинатора переделывать под ваш вариант

  • Я в прошлом комментарии советовал обращаться к свойству data (объект CDataProvider) напрямую. Этот вариант тоже не сработал?

  • Дмитрий

    честно даже не разобрался как это сделать, но я решил вернуться к вашему варианту в этом посте где вы использовали getRelated, хотелось узнать можно ли както из этого примера применить класс CSort и с помощью него сортировать по разным полям, к примеру по имени игр, по жанру ? если можно можете дать небольшой пример, просто сам недавно сталкнулся с этим фреймворком.

  • Да, CSort использовать можно. По-сути он добавляет в запрос параметры сортировки. Пример есть здесь в комментариях.

    Я все-таки не пойму, почему не получилось использовать атрибут data. Постараюсь в ближайшее время проверить.

  • Николай

    возникла проблема такая же (версия Yii 1.12) — неправильно лимит считался. Решил проблему обходным путем — используя group и having, где первый брался по первичному ключу одному, а второй устанавливал условие типа count(id) BETWEEN $start AND $end. Конечно, такой способ слегка кривоват, зато наиболее прост.

    • Николай

      хотя не, ошибка, так неправильно работает, зато работает просто group по первичному ключу, и обычный лимит как свойство CDbCriteria объекта

  • Спасибо. Вот только в случае, если данные у вас динамические, то limit offset удет возвращать дубликаты. Я использую курсоры — http://plutov.by/post/cursor_pagination

    • Не однозначная ситуация. Например, посетитель переходит на страницу номер 2 (/page/2), а фактически оказывается на 3-ей, т.к. добавились записи в начало списка. Вы решили только часть проблемы — исключили повтор записей, которые есть на странице 1, но посетитель не знает, что при возврате на страницу 1 он увидит новые записи.