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

11 мая, 2010
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). Во втором случае используется несколько запросов. Сначала выбираются нужные записи из первой таблицы (первый запрос), затем – данные из связанной таблицы (для каждой записи из первой таблицы выполняется дополнительный запрос).

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

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

  1. $criteria=new CDbCriteria;
  2. $criteria->condition = 't_id=:t_id';
  3. $criteria->params = array(':t_id'=>$_GET['type_id']);
  4. $criteria->with = array('ygs_types'=>array('together'=>true));
  5.  
  6. $pages=new CPagination(Games::model()->published()->count($criteria));
  7. $pages->pageSize=self::PAGE_SIZE;
  8. //этот метод добавляет параметры limit и offset в объект $criteria, т.е. в запрос
  9. $pages->applyLimit($criteria);
  10.  
  11. $models=Games::model()->findAll($criteria);

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

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

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

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

  1. SELECT `t`.`g_id` AS `t0_c0`, … FROM `ygs_games` `t`  WHERE ((g_state=0) AND
  2. (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.

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

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

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

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

  1. $type->ygs_games

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

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

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

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

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

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

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

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

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

  1. public function actionShowGames()
  2. {
  3.     if (isset($_GET['type_id']) && is_numeric($_GET['type_id'])) {
  4.         $criteria = new CDbCriteria;
  5.        
  6.         $type = Types::model()->findByPk($_GET['type_id']);
  7.         //нужно использовать "ленивую" загрузку (не использовать with)
  8.         //иначе не получится указать limit для связанной таблицы
  9.         $params = array(
  10.             'limit'=>self::PAGE_SIZE,
  11.             'condition'=>'g_state='.Games::PUBLISHED,
  12.             'order'=>'g_added DESC',
  13.         );
  14.         //настраиваем пагинацию
  15.         if (isset($_GET['page']) && is_numeric($_GET['page'])) {
  16.             $params['offset'] = ($_GET['page'] – 1) * self::PAGE_SIZE;
  17.         }
  18.         $pages=new CPagination(count($type->getRelated('ygs_games')));
  19.         $pages->pageSize=self::PAGE_SIZE;
  20.        
  21.         $games = $type->getRelated('ygs_games',false, $params);
  22.        
  23.         $this->render('showGames'
  24.             ,array('games'=>$games, 'pages'=>$pages));
  25.     } else {
  26.         $this->redirect('/games/list');
  27.     }
  28. }

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

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

архив с исходным кодом

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

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

Понравилась статья? Подписывайтесь на продолжение rss link !

Или на мой твиттер twitter link

]]>

Добавьте эту страницу в google.com bobrdobr.ru del.icio.us technorati.com linkstore.ru news2.ru rumarkz.ru memori.ru moemesto.ru

]]>

Опубликовано в MySQL, PHP, Web разработка, Yii View Comments

]]>
  • Дмитрий
    пытаюсь переписать код под 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. Постараюсь в ближайшее время проверить.
  • Помнится писал я по этому поводу в багтрекер. Макаров сначала записал это в фичи, а теперь со всем этим поведением что-то хотят сделать, но только уже в версии 1.1.3
  • По-моему, такие переходы не добавляют популярности фреймворку. Такое
    чувство, что с бетой работаешь.
  • По-моему, такие переходы не добавляют популярности фреймворку. Такое
    чувство, что с бетой работаешь.
  • А причем тут лимит?
  • Если в основной таблице есть опции запроса LIMIT или OFFSET, то сначала будет выполнен этот запрос, а затем другой SQL-запрос, который возвращает все связанные объекты.

    При этом в первом запросе нельзя задать условия для присоединяемой таблицы.
  • disqus... прикольно.
  • Мне тоже нравится :)
  • Я у себя на сайте тоже прикрутил. И уведомление на почту пришло, что на коммент ответили)))
  • Мне больше всего нравиться, что прямо из почты можно ответить :)
  • Еще круче))
  • BerdArt
    спасибо за статью.
    а подскажите плиз, есть ли возможность, например, после выполнения запроса вывести сам запрос? (что-то по типа last_query в CodeIgniter)
  • Да, есть. Для этого нужно сделать несколько настроек в конфиге. Пример
    здесь
  • Это больная тема, я ограничиваюсь "ручным" присоединением таблицы через join в критерии. Недостатка с подсчетами для пагинации нет.
  • Я вообще, когда первый раз столкнулся с проблемой, просто написал нужный sql запрос ;) Но, хотелось по максимуму использовать встроенную библиотеку. Наверное, во многих случаях, ваше решение окажется предпочтительнее.
  • А какой метод быстрее?
    "жадная" загрузка или "ленивая" загрузка?
  • Смотря как построена БД, какие индексы. Лучше всего - сделать бенчмарк под свои потребности и на конкретном сервере смотреть. С left join могут быть проблемы из-за ограничений в конфиге(мне как то попадалось). Так что лучше тестить, тестить и ещё раз тестить
  • Однозначного ответа нет, нужно сравнивать сами запросы. В данном случае разницы практически нет, т.к. все-равно выполняется два запроса одинаковой сложности (во втором два inner join).
blog comments powered by Disqus ]]>