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

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

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

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

В моем примере с игровым сайтом как раз возникла такая ситуация. Есть две таблицы, с играми (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.

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

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

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