Реляційна Active Record

Ми вже розглянули використання Active Record (AR) для вибору даних з однієї таблиці бази даних. У цьому розділі ми розповімо, як використовувати AR для обʼєднання декількох звʼязаних таблиць і отримати обʼєднаний набір даних.

Для використання реляційної AR рекомендується щоб усі звʼязки відношення первинний-зовнішній ключ були чітко визначені для таблиць, що обʼєднуються. Це допомагає підтримувати звʼязність та цілісність даних.

Для наочності прикладів у даному розділі ми будемо використовувати схему бази даних, представлену на цій діаграмі сутність-відношення (ER).

Діаграма ER

Діаграма ER

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

Оголошення відношення

Перед тим, як використовувати AR для реляційних запитів, нам необхідно пояснити AR, як AR-класи звʼязані один з одним.

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

Існує пʼятий спеціальний тип, який виконує агреговані запити на звʼязаних записах - він називається STAT. Зверніться, будь ласка, до розділу Статистичний запит за деталями.

Оголошуючи відношення в 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.

Виконання реляційного запиту

Найпростіший спосіб виконати реляційний запит — зчитати реляційну властивість 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',
    )
));

Реляційний запит без отримання звʼязаних моделей

Іноді потрібно виконати запит із використанням відношень, але, при цьому, не потрібні дані із звʼязаної моделі. Припустимо, є користувачі (User), які публікують безліч записів (Post). Запис може бути опублікований, а може бути чернеткою. Цей факт визначається полем моделі published. Нам необхідно отримати всіх користувачів, які опублікували хоча-б один запис. При цьому самі записи нам не цікаві. Зробити це можна так:

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

Параметри реляційного запиту

Вище ми згадували про те, що в реляційному запиті можна вказати додаткові параметри. Ці параметри — пари імʼя-значення — використовуються для тонкого налаштування реляційного запиту. Перелік параметрів представлений нижче.

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

Нижче ми змінимо визначення відношення 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, ми отримаємо записи автора, відсортовані в зворотньому порядку за часом їх створення. Для кожного запису будуть завантажені її категорії.

Усунення конфлікту імен стовпців

При співпаданні імен стовпців у двох і більше зʼєднуваних таблиць, доводиться вирішувати конфлікт імен. Це робиться за допомогою додавання псевдоніма таблиці до імені стовпця.

У реляційному запиті псевдонім головної таблиці завжди дорівнює 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'
));

Підказка: Псевдонім таблиці звʼязку за замовчуванням дорівнює назві самого звʼязку. Майте на увазі, що при використанні одного звʼязку всередині іншого буде використано назву останнього із них. При цьому назва батьківського звʼязку не буде використана у якості префікса. Наприклад, псевдонімом звʼязку 'author.group' є 'group', а не 'author.group'.

$posts=Post::model()->with('author', 'author.group')->findAll(array(
  'order'=>'group.name, author.name, t.title'
));

Ви можете уникнути конфлікту псевдонімів таблиць задавши властивість звʼязку alias.

$comments=Comment::model()->with(
  'author',
  'post',
  'post.author'=>array('alias'=>'p_author'))->findAll(array(
  'order'=>'author.name, p_author.name, post.title'
));

Динамічні параметри реляційного запиту

Ми можемо використовувати динамічні параметри як для методу 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'));

Продуктивність реляційного запиту

Як було описано вище, жадібне завантаження використовується, головним чином, коли потрібно отримати безліч звʼязаних обʼєктів. У цьому випадку зʼєднанням усіх таблиць генерується великий складний 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();

Статистичний запит

Крім реляційних запитів, описаних вище, 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(). Доступні параметри перераховані нижче:

Реляційні запити з іменованими групами умов

У реляційному запиті іменовані групи умов можуть бути використані двома способами. Їх можна застосувати до основної моделі і до звʼязаних моделей.

Наступний код показує роботу з основною моделлю:

$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. Імʼя відношення і групи параметрів розділяються двокрапкою.

Вам може знадобитися використовувати замість жадібної вибірки відкладену для звʼязку із групою умов. Синтаксис для цього такий:

// імʼя звʼязку comments повторюється два рази
$approvedComments = $post->comments('comments:approved');

Іменовані групи можуть бути використані при описі відношень моделі у методі 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,
            ),
        ),
    ),
));
 
class Post extends CActiveRecord
{
    ......
 
    public function rated($rating)
    {
        $this->getDbCriteria()->mergeWith(array(
            'condition'=>'rating=:rating',
            'params'=>array(':rating'=>$rating),
        ));
        return $this;
    }
 
    ......
}

Реляційні запити з through

При використанні through визначення відношення має виглядати наступним чином:

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

У коді вище, а саме у array('key1'=>'key2'):

through може використовуватися з HAS_ONE, BELONGS_TO та HAS_MANY.

HAS_MANY through

HAS_MANY through ER

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 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

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;