Реляционная Active Record

Мы уже рассмотрели использование Active Record (AR) для выбора данных из одной таблицы базы данных. В этом разделе мы расскажем, как использовать AR для объединения нескольких связанных таблиц и получить объединенный набор данных.

Для использования реляционной AR рекомендуется, чтобы все связи отношения первичный-внешний ключ были четко определены для объединяемых таблиц. Это помогает поддерживать связность и целостность данных.

Для наглядности примеров в данном разделе мы будем использовать схему базы данных, представленную на этой диаграмме сущность-отношение (ER).

Диаграмма ER

Информация: Поддержка ограничений по внешнему ключу различна в разных СУБД. SQLite < 3.6.19 не поддерживает ограничений, но вы, тем не менее, можете их объявить при создании таблиц. Движок MySQL MyISAM не поддерживает внешние ключи.

1. Объявление отношения ¶

Перед тем, как использовать AR для реляционных запросов, нам необходимо объяснить AR, как AR-классы связаны друг с другом.

Отношение между двумя AR-классами напрямую зависит от отношений между соответствующими таблицами базы данных. С точки зрения БД, отношение между таблицами A и В может быть трех типов: один-ко-многим (например, tbl_user и tbl_post), один-к-одному (например, tbl_user и tbl_profile) и многие-ко-многим (например, tbl_category и tbl_post). В AR существует четыре типа отношений:

• BELONGS_TO: если отношение между А и В один-ко-многим, значит В принадлежит А (например, Post принадлежит User);

• HAS_MANY: если отношение между таблицами А и В один-ко-многим, значит у А есть много В (например, у User есть много Post);

• HAS_ONE: это частный случай HAS_MANY, где А может иметь максимум одно В (например, у User есть только один Profile);

• MANY_MANY: это отношение соответствует типу отношения многие-ко-многим в БД. Поскольку многие СУБД не поддерживают непосредственно тип отношения многие-ко-многим, требуется ассоциированная таблица для преобразования отношения многие-ко-многим в отношения один-ко-многим. В нашей схеме базы данных, этой цели служит таблица tbl_post_category. В терминологии AR отношение MANY_MANY можно описать как комбинацию BELONGS_TO и HAS_MANY. Например, Post принадлежит многим Category, а у Category есть много Post.

Объявляя отношение в AR, мы переопределяем метод relations() класса CActiveRecord. Этот метод возвращает массив с конфигурацией отношений. Каждый элемент массива представляет одно отношение в следующем формате:

'VarName'=>array('RelationType', 'ClassName', 'ForeignKey', …дополнительные параметры)

где VarName — имя отношения, RelationType указывает на один из четырех типов отношения, ClassName — имя AR-класса, связанного с данным AR-классом, а ForeignKey обозначает один или несколько внешних ключей, используемых для связи. Кроме того, можно указать ряд дополнительных параметров, о которых расскажем чуть позже.

В коде ниже показано, как объявить отношение между классами User и Post.

class Post extends CActiveRecord
{
    …
    public function relations()
    {
        return array(
            'author'=>array(self::BELONGS_TO, 'User', 'author_id'),
            'categories'=>array(self::MANY_MANY, 'Category',
                'tbl_post_category(post_id, category_id)'),
        );
    }
}
 
class User extends CActiveRecord
{
    …
    public function relations()
    {
        return array(
            'posts'=>array(self::HAS_MANY, 'Post', 'author_id'),
            'profile'=>array(self::HAS_ONE, 'Profile', 'owner_id'),
        );
    }
}

Информация: Внешний ключ может быть составным, то есть состоять из двух и более столбцов. В данном случае имена столбцов следует разделить запятыми и либо передать их в одном строковом параметре, либо в виде массива array('key1','key2'). Если требуется указать свою связь первичного ключа с внешним, задать её можно в виде array('fk'=>'pk'). Для составных ключей это будет array('fk_c1'=>'pk_c1','fk_c2'=>'pk_c2'). Для типа отношения MANY_MANY имя ассоциативной таблицы также должно быть указано во внешнем ключе. Например, отношение categories в модели Post обозначено внешним ключом tbl_post_category(post_id, category_id). При объявлении отношения в AR-классе для каждого отношения в класс неявно добавляется свойство. После выполнения реляционного запроса соответствующее свойство будет заполнено связанным(-и) экземпляром(-ами) AR. Например, если $author представляет AR-экземпляр User, то можно использовать $author->posts для доступа к связанным экземплярам Post.

2. Выполнение реляционного запроса ¶

Самый простой способ выполнить реляционный запрос — считать реляционное свойство AR-класса. Если ранее к этому свойству никто не обращался, то будет инициирован реляционный запрос, который соединит связанные таблицы и оставит только данные, соответствующие первичному ключу текущего экземпляра AR. Результат запроса будет сохранен в свойстве как экземпляр(-ы) связанного класса. Этот подход также известен, как «отложенная загрузка» (lazy loading), т.е. реляционный запрос осуществляется только в момент первого обращения к связанным объектам. Пример ниже показывает использование этого подхода:

// получаем запись с ID=10
$post=Post::model()->findByPk(10);
// Получаем автора записи. Здесь будет выполнен реляционный запрос.
$author=$post->author;

Информация: Если для отношения не существует связанного экземпляра, то соответствующее свойство будет null для отношений BELONGS_TO и HAS_ONE или пустым массивом для HAS_MANY и MANY_MANY. Стоит отметить, что отношения HAS_MANY и MANY_MANY возвращают массивы объектов и обращаться к их свойствам необходимо в цикле, иначе можно получить ошибку «Trying to get property of non-object».

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

Этот подход заключается в загрузке всех связанных экземпляров AR одновременно с основным экземпляром AR. Реализуется этот подход путем использования в AR метода with() в связке с методом find или findAll. Например:

$posts=Post::model()->with('author')->findAll();

Код выше вернет массив экземпляров Post. В отличие от отложенной загрузки, свойство author в каждой записи заполнено связанным экземпляром User еще до обращения к свойству. Вместо выполнения объединяющего запроса для каждой записи, жадная загрузка получает все записи вместе с авторами в одном объединяющем запросе!

В методе with() можно указать множество имен отношений и жадная загрузка вернет их за один раз. Например, следующий код вернет записи вместе с их авторами и категориями:

$posts=Post::model()->with('author','categories')->findAll();

Кроме того, можно осуществлять вложенную жадную загрузку. Для этого вместо простого списка имен отношений, мы передаем методу with() имена отношений, упорядоченных иерархически, как в примере ниже:

$posts=Post::model()->with(
    'author.profile',
    'author.posts',
    'categories')->findAll();

Пример выше вернет нам все записи с их авторами и категориями, а также профиль каждого автора и все его записи.

Жадная загрузка может быть выполнена путём указания свойства CDbCriteria::with:

$criteria=new CDbCriteria;
$criteria->with=array(
    'author.profile',
    'author.posts',
    'categories',
);
$posts=Post::model()->findAll($criteria);

или

$posts=Post::model()->findAll(array(
    'with'=>array(
        'author.profile',
        'author.posts',
        'categories',
    )
));

3. Реляционный запрос без получения связанных моделей ¶

Иногда требуется выполнить запрос с использованием отношения, но, при этом, не требуются данные из связанной модели. Допустим, есть пользователи (User), которые публикуют множество записей (Post). Запись может быть опубликована, а может быть черновиком. Этот факт определяется полем модели published. Нам необходимо получить всех пользователей, которые опубликовали хотя-бы одну запись. При этом сами записи нам не интересны. Сделать это можно так:

$users=User::model()->with(array(
    'posts'=>array(
        // записи нам не нужны
        'select'=>false,
        // но нужно выбрать только пользователей с опубликованными записями
        'joinType'=>'INNER JOIN',
        'condition'=>'posts.published=1',
    ),
))->findAll();

4. Параметры реляционного запроса ¶

Выше мы упоминали о том, что в реляционном запросе можно указать дополнительные параметры. Эти параметры — пары имя-значение — используются для тонкой настройки реляционного запроса. Список параметров представлен ниже.

• select: список выбираемых полей для связанного AR-класса. По умолчанию значение параметра равно '*', т.е. выбираются все поля таблицы. Для используемых столбцов должны быть разрешены конфликты имён.

• condition: соответствует оператору WHERE, по умолчанию значение параметра пустое. Для используемых столбцов должны быть разрешены конфликты имён.

• params: параметры для связывания в генерируемом SQL-выражении. Параметры передаются как массив пар имя-значение.

• on: соответствует оператору ON. Условие, указываемое в этом параметре, будет добавлено к условию объединения с использованием оператора AND. Для используемых столбцов должны быть разрешены конфликты имён. Данный параметр неприменим для отношений типа MANY_MANY.

• order: соответствует оператору ORDER BY, по умолчанию значение параметра пустое. Для используемых столбцов должны быть разрешены конфликты имён.

• with: список дочерних связанных объектов, которые должны быть загружены с самим объектом. Неправильное использование данной возможности может привести к бесконечному циклу.

• joinType: тип объединения для отношения. По умолчанию значение параметра равно LEFT OUTER JOIN;

• alias: псевдоним таблицы, ассоциированной с отношением. По умолчанию значение параметра равняется null, что означает, что псевдоним соответствует имени отношения.

• together: параметр, устанавливающий необходимость принудительного объединения таблицы, ассоциированной с этим отношением, с другими таблицами. Этот параметр имеет смысл только для отношений типов HAS_MANY и MANY_MANY. Если параметр не установлен или равен false, тогда каждое отношение HAS_MANY или MANY_MANY будет использовать отдельный SQL запрос для связанных данных, что может улучшить скорость выполнения запроса т.к. уменьшается количество выбираемых данных. Если данный параметр равен true, зависимая таблица при запросе будет всегда объединяться с основной, то есть будет сделан один запрос даже в том случае, если к основной таблице применяется постраничная разбивка. Если данный параметр не задан, зависимая таблица будет объединена с основной только в случае, когда не к основной таблице не применяется постраничная разбивка. Более подробное описание можно найти в разделе «производительность реляционного запроса».

• join: дополнительный оператор JOIN. По умолчанию пуст. Этот параметр доступен с версии 1.1.3.

• group: соответствует оператору GROUP BY, по умолчанию значение параметра пустое. Для используемых столбцов должны быть разрешены конфликты имён.

• having: соответствует оператору HAVING, по умолчанию значение параметра пустое. Для используемых столбцов должны быть разрешены конфликты имён.

• index: имя столбца, значения которого должны быть использованы в качестве ключей массива, хранящего связанные объекты. Без установки этого параметра, массив связанных объектов использует целочисленный индекс, начинающийся с нуля. Параметр может быть установлен только для отношений HAS_MANY и MANY_MANY.

• scopes: группы условий, которые необходимо применить. В случае одной группы может использоваться как 'scopes'=>'scopeName'. Если же групп несколько, то используется как 'scopes'=>array('scopeName1','scopeName2'). Этот параметр доступен с версии 1.1.9.

Кроме того, для отложенной загрузки некоторых типов отношений доступен ряд дополнительных параметров:

• limit: параметр для ограничения количества строк в выборке. Параметр не применим для отношений BELONGS_TO;

• offset: параметр для указания начальной строки выборки. Параметр не применим для отношений BELONGS_TO.

• through: имя отношения модели, которое при получении данных будет использоваться как мост. Параметр может быть установлен только для отношений HAS_ONE и HAS_MANY. Этот параметр доступен с версии 1.1.7.

Ниже мы изменим определение отношения posts в модели User, добавив несколько вышеприведенных параметров:

class User extends CActiveRecord
{
    public function relations()
    {
        return array(
            'posts'=>array(self::HAS_MANY, 'Post', 'author_id',
                            'order'=>'posts.create_time DESC',
                            'with'=>'categories'),
            'profile'=>array(self::HAS_ONE, 'Profile', 'owner_id'),
        );
    }
}

Теперь при обращении к $author->posts, мы получим записи автора, отсортированные в обратном порядке по времени их создания. Для каждой записи будут загружены её категории.

5. Устранение конфликта имён столбцов ¶

При совпадении имён столбцов в двух и более соединяемых таблицах, приходится решать конфликт имён. Это делается при помощи добавления псевдонима таблицы к имени столбца.

В реляционном запросе псевдоним главной таблицы всегда равен t,. Имя псевдонима относящейся к ней таблице по умолчанию соответствует имени отношения. К примеру, в коде ниже псевдонимы для Post и Comment соответственно t и comments:

$posts=Post::model()->with('comments')->findAll();

Допустим, что и в Post и в Comment есть столбец create_time, в котором хранится время создания записи или комментария, и нам необходимо получить записи вместе с комментариями к ним, отсортированные сначала по времени создания записи, а затем по времени написания комментария. Для этого нам понадобится устранить конфликт столбцов create_time следующим образом:

$posts=Post::model()->with('comments')->findAll(array(
    'order'=>'t.create_time, comments.create_time'
));

6. Динамические параметры реляционного запроса ¶

Мы можем использовать динамические параметры как для метода with(), так и для параметра with. Динамические параметры переопределяют существующие параметры в соответствии с описанием метода relations(). К примеру, если для модели User, приведенной выше, мы хотим воспользоваться жадной загрузкой для получения записей автора в порядке возрастания (параметр order в определении отношения задает убывающий порядок), можно сделать это следующим образом:

User::model()->with(array(
    'posts'=>array('order'=>'posts.create_time ASC'),
    'profile',
))->findAll();

Динамические параметры в реляционных запросах можно использовать вместе с отложенной загрузкой. Для этого необходимо вызвать метод с тем же именем, что и имя связи, и передать параметры как его аргумент. К примеру, следующий код вернёт публикации пользователя, у которых status равен 1:

$user=User::model()->findByPk(1);
$posts=$user->posts(array('condition'=>'status=1'));

7. Производительность реляционного запроса ¶

Как было описано выше, жадная загрузка используется, главным образом, когда требуется получить множество связанных объектов. В этом случае соединением всех таблиц генерируется большой сложный SQL-запрос. Такой запрос во многих случаях является предпочтительным т.к. упрощает фильтрацию по значению столбца связанной таблицы. Тем не менее, в некоторых случаях такие запросы не являются эффективными.

Рассмотрим пример, в котором нам надо найти последние записи вместе с их комментариями. Учитывая, что у каждой записи 10 комментариев, при использовании одного большого SQL-запроса мы получим множество лишних данных так как каждая запись будет повторно выбираться с каждым её комментарием. Теперь попробуем по-другому: сначала выберем последние записи, а затем комментарии к ним. В данном случае нам необходимо выполнить два SQL запроса. Плюс в том, что в полученных данных не будет ничего лишнего.

Так какой подход более эффективен? Абсолютно верного ответа на этот вопрос нет. Выполнение одного большого SQL запроса может быть более эффективным так как СУБД не приходится лишний раз разбирать и выполнять дополнительные запросы. С другой стороны, используя один SQL запрос, мы получаем больше лишних данных и соответственно нам требуется больше времени на их передачу и обработку. По умолчанию Yii использует "жадную" загрузку, то есть генерирует один SQL запрос, кроме того случая, когда к главной модели применяется LIMIT. Если выставить опцию together в описании отношения в true, то мы получим единственный SQL запрос даже если используется LIMIT. Если использовать false, то выборка из некоторых таблиц будет производиться отдельными запросами. К примеру, для того, чтобы использовать отдельные SQL запросы для выборки последних записей и комментариев к ним, отношение comments модели Post следует описать следующим образом:

public function relations()
{
    return array(
        'comments' => array(self::HAS_MANY, 'Comment', 'post_id', 'together'=>false),
    );
}

Для жадной загрузки мы можем задать эту опцию динамически:

$posts = Post::model()->with(array('comments'=>array('together'=>false)))->findAll();

8. Статистический запрос ¶

Помимо реляционных запросов, описанных выше, Yii также поддерживает так называемые статистические запросы (или запросы агрегирования). Этот тип запросов используется для получения агрегированных данных, относящихся к связанным объектам, например количество комментариев к каждой записи, средний рейтинг для каждого наименования продукции и т.д. Статистические запросы могут быть использованы только для объектов, связанных отношениями HAS_MANY (например, у записи есть много комментариев) или MANY_MANY (например, запись принадлежит многим категориям, а категориия может относиться ко множеству записей).

Выполнение статистического запроса аналогично выполнению реляционного запроса в соответствии с описанием выше. Первым делом необходимо объявить статистический запрос в методе relations() класса CActiveRecord.

class Post extends CActiveRecord
{
    public function relations()
    {
        return array(
            'commentCount'=>array(self::STAT, 'Comment', 'post_id'),
            'categoryCount'=>array(self::STAT, 'Category', 'post_category(post_id, category_id)'),
        );
    }
}

Выше мы объявили два статистических запроса: commentCount подсчитывает количество комментариев к записи, а categoryCount считает количество категорий, к которым относится запись. Обратите внимание, что отношение между Post и Comment — типа HAS_MANY, а отношение между Post и Category — типа MANY_MANY (с использованием преобразующей таблицы post_category). Как можно видеть, порядок объявления очень схож с объявлением отношений, описанных выше. Единственное различие состоит в том, что в данном случае тип отношения равен STAT.

За счет объявленных отношений мы можем получить количество комментариев для записи, используя выражение $post->commentCount. В момент первого обращения к данному свойству для получения соответствующего результата неявным образом выполняется SQL-выражение. Как мы уже говорили, это называется подходом отложенной загрузки. Можно также использовать жадный вариант загрузки, если необходимо получить количество комментариев к нескольким записям:

$posts=Post::model()->with('commentCount', 'categoryCount')->findAll();

Выражение выше выполняет три SQL-запроса для получения всех записей вместе с значениями количества комментариев к ним и количества категорий. В случае отложенной загрузки нам бы понадобилось выполнить 2*N+1 SQL-запросов для N записей.

По умолчанию статистический запрос считает количество с использованием выражения COUNT. Его можно уточнить путем указания дополнительных параметров в момент объявления в методе relations(). Доступные параметры перечислены ниже:

• select: статистическое выражение, по умолчанию равно COUNT(*), что соответствует количеству дочерних объектов;

• defaultValue: значение, которое присваивается в случае, если результат статистического запроса для записи отрицателен. Например, если запись не имеет ни одного комментария, то свойству commentCount будет присвоено это значение. По умолчанию значение данного параметра равно 0;

• condition: соответствует оператору WHERE, по умолчанию значение параметра пустое;

• params: параметры для связывания в генерируемом SQL-выражении. Параметры передаются как массив пар имя-значение;

• order: соответствует оператору ORDER BY, по умолчанию значение параметра пустое;

• group: соответствует оператору GROUP BY, по умолчанию значение параметра пустое;

• having: соответствует оператору HAVING, по умолчанию значение параметра пустое.

9. Реляционные запросы с именованными группами условий ¶

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

Следущий код показывает случай с основной моделью:

$posts=Post::model()->published()->recently()->with('comments')->findAll();

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

В следующем примере показано, как применить группы условий к связанным моделям:

$posts=Post::model()->with('comments:recently:approved')->findAll();
// или, начиная с версии 1.1.7
$posts=Post::model()->with(array(
    'comments'=>array(
        'scopes'=>array('recently','approved')
    ),
))->findAll();
// или, начиная с версии 1.1.7
$posts=Post::model()->findAll(array(
    'with'=>array(
        'comments'=>array(
            'scopes'=>array('recently','approved')
        ),
    ),
));

Этот запрос вернёт все записи вместе с одобренными комментариями. Здесь comments относится к имени отношения. recently и approved — именованные группы, описанные в модели Comment. Имя отношения и группы параметров разделяются двоеточием.

Именованные группы могут быть использованы при описании отношений модели в методе CActiveRecord::relations() в параметре with. В следующем примере при обращении к $user->posts вместе с публикациями будут получены все одобренные комментарии.

class User extends CActiveRecord
{
    public function relations()
    {
        return array(
            'posts'=>array(self::HAS_MANY, 'Post', 'author_id',
                'with'=>'comments:approved'),
        );
    }
}
// или, начиная с версии 1.1.7
class User extends CActiveRecord
{
    public function relations()
    {
        return array(
            'posts'=>array(self::HAS_MANY, 'Post', 'author_id',
                'with'=>array(
                    'comments'=>array(
                        'scopes'=>'approved'
                    ),
                ),
            ),
        );
    }
}

С версии 1.1.7 стало возможно передать параметры именованным группам условий отношения. К примеру, если в Post есть именованная группа условий rated, принимающая минимальный рейтинг записи, использовать её в User можно так:

Примечание: до 1.1.7 именованные группы условий, применяемые к реляционным моделям, должны быть описаны в CActiveRecord::scopes. Поэтому они не могут быть параметризованы.

$users=User::model()->findAll(array(
    'with'=>array(
        'posts'=>array(
            'scopes'=>array(
                'rated'=>5,
            ),
        ),
    ),
));

10. Реляционные запросы с through ¶

При использовании through определение отношения должно выглядеть следующим образом:

'comments'=>array(self::HAS_MANY,'Comment',array('key1'=>'key2'),'through'=>'posts'),

В коде выше, а именно в array('key1'=>'key2'):

• key1 — ключ, определённый в отношении, на которое указывает through (в нашем случае posts).
• key2 — ключ, определённый в модели, на которую указывает отношение (в нашем случае Comment).

through может использоваться как с HAS_ONE, так и с HAS_MANY.

HAS_MANY through

HAS_MANY through ER

Пример использования HAS_MANY с through — получение пользователей, состоящих в определённой группе, если они записаны в группу через роли.

Более сложным примером является получение всех комментариев для всех пользователей определённой группы. В этом случае необходимо использовать несколько отношений с through в одной модели:

class Group extends CActiveRecord
{
   ...
   public function relations()
   {
       return array(
           'roles'=>array(self::HAS_MANY,'Role','group_id'),
           'users'=>array(self::HAS_MANY,'User',array('user_id'=>'id'),'through'=>'roles'),
           'comments'=>array(self::HAS_MANY,'Comment',array('id'=>'user_id'),'through'=>'users'),
       );
   }
}

Примеры

// получаем все группы с соответствующими им пользователями
$groups=Group::model()->with('users')->findAll();
 
// получаем все группы с соответствующими им пользователями и ролями
$groups=Group::model()->with('roles','users')->findAll();
 
// получаем всех пользователей и роли для группы с ID, равным 1
$group=Group::model()->findByPk(1);
$users=$group->users;
$roles=$group->roles;
 
// получаем все комментарии для группы с ID, равным 1
$group=Group::model()->findByPk(1);
$comments=$group->comments;

HAS_ONE through

HAS_ONE through ER

Пример использования HAS_ONE с through — получение адреса пользователя в случае, если пользователь связан с адресом через профиль. Все задействованные сущности (пользователь, профиль и адрес) имеют соответствующие им модели:

class User extends CActiveRecord
{
   ...
   public function relations()
   {
       return array(
           'profile'=>array(self::HAS_ONE,'Profile','user_id'),
           'address'=>array(self::HAS_ONE,'Address',array('id'=>'profile_id'),'through'=>'profile'),
       );
   }
}

Примеры

// получаем адрес пользователя с ID, равным 1
$user=User::model()->findByPk(1);
$address=$user->address;

through с собой

through можно использовать для модели, связанной с собой через мост. В нашем случае это пользователь, обучающий других пользователей:

through self ER

Отношения для данного случая определяются следующим образом:

class User extends CActiveRecord
{
   ...
   public function relations()
   {
       return array(
           'mentorships'=>array(self::HAS_MANY,'Mentorship','teacher_id','joinType'=>'INNER JOIN'),
           'students'=>array(self::HAS_MANY,'User',array('student_id'=>'id'),'through'=>'mentorships','joinType'=>'INNER JOIN'),
       );
   }
}

Примеры

// получаем всех студентов учителя с ID, равным 1
$teacher=User::model()->findByPk(1);
$students=$teacher->students;