Связанные таблицы и limit

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

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

Поэтому, на мой взгляд, удобнее использовать метод [URL=http://www.yiiframework.com/doc/api/CActiveRecord#getRelated-detail]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).
Теги:
связанные таблицы, limit
Добавлено: 22 Апреля 2018 11:25:28 Добавил: Андрей Ковальчук Нравится 0
Добавить
Комментарии:
Нету комментариев для вывода...