Конструктор запитів

Конструктор запитів Yii надає обʼєктно-орієнтований спосіб написання SQL-запитів. Він дозволяє розробнику використовувати методи і властивості класу для того, щоб вказати окремі частини SQL запиту. Потім, конструктор збирає окремі частини в SQL запит, який може бути потім виконаний викликом методів DAO, як описано в «Обʼєкти доступу до даних (DAO)». Наступний код показує типове використання конструктора запитів для отримання SQL-запиту SELECT:




Конструктор запитів найкраще використовувати в тому випадку,
коли необхідно зібрати SQL запит слідуючи деякій умовній логіці додатку.
Основними перевагами конструктора запитів є те, що він:



Використовувати побудовник запитів не обовʼязково. Якщо ваші запити прості, легше і швидше використовувати саме SQL.





Примітка: Конструктор запитів не може бути використаний для зміни існуючого запиту,
  заданого за допомогою SQL. Наприклад, не буде працювати наступний код:

  
  

  
  
Не варто використовувати для одного запиту і SQL, і конструктор запитів.





Підготовка конструктора запитів



Конструктор запитів реалізований у класі CDbCommand - головному класі для роботи з базою даних,
описаному в «Обʼєкти доступу до даних (DAO)».



Для того, щоб почати його використовувати необхідно створити новий екземпляр CDbCommand:






Тут ми використовуємо Yii::app()->db для отримання зʼєднання з базою даних і,
потім, викликаємо CDbConnection::createCommand() для створення екземпляра команди.



Варто зазначити, що замість передачі createCommand() цілого SQL,
як це робилося у випадку із DAO, ми не вказуємо параметр,
так як ми зберемо окремі частини запиту за допомогою методів конструктора, які описані далі.



Запити отримання даних



Запити на отримання даних відповідають в SQL запитам SELECT.
У конструктора є ряд методів для збирання окремих частин SELECT запиту.
Тому що всі ці методи повертають примірник CDbCommand, то ми можемо використовувати їх ланцюжком,
як показано у прикладі на початку цього розділу.



Розглянемо використання перерахованих методів. Для простоти припустимо, що запити робляться до MySQL.
Для інших СУБД екранування таблиць, полів і значень, показане у прикладах, може відрізнятися.



select()






Метод select() задає частину запиту після SELECT. Параметр $columns визначає поля,
що обираються, і може бути або списком імен полів, що вибираються, розділених комою, або масивом імен полів.
Імена можуть містити префікси таблиць та псевдоніми полів. Метод автоматично екранує імена,
якщо в імені поля немає дужок (що означає використання виразу).



Кілька прикладів:






selectDistinct()






Метод selectDistinct() робить те ж саме,
що і метод select() плюс додає до виразу DISTINCT.
Приміром, selectDistinct('id, username') згенерує наступний SQL:






from()






Метод from() задає частину запиту після FROM.
Параметр $tables визначає, з яких таблиць проводиться вибірка і може бути або списком імен таблиць,
розділеним комами, або масивом імен таблиць. Імена можуть містити префікси схеми (такі, як public.tbl_user)
та псевдоніми таблиць (такі, як tbl_user u). Метод автоматично екранує імена,
якщо у них немає дужок (що означає використання підзапиту або виразу).



Приклади:






where()






Метод where() задає частину запиту після WHERE.
Параметр $conditions визначає умови запиту, а $params - параметри, які підставляються у запит.
Значення параметра $conditions може бути як рядком (наприклад, id=1) так і масивом наступного вигляду:






де operator може бути одним з наступних:



Кілька прикладів використання where:






Варто зазначити, що у випадку, коли оператор містить like, необхідно явно задавати спецсимволи (на кшталт % і _).
Якщо патерн вводиться користувачем, то необхідно використовувати наведений нижче код для екранування
спецсимволів і запобігання інтерпретації їх як спецсимволів:






order()






Метод order() задає частину запиту після ORDER BY. Параметр $columns визначає,
по яким полям буде проводитись сортування. Поля можуть бути вказані як у вигляді рядка,
що містить список полів і напрямів (ASC або DESC), розділених комами, так і масив полів і напрямів.
Імена полів можуть містити префікси таблиць. Метод автоматично екранує імена полів,
якщо вони не містять дужок (що означає використання виразу).



Кілька прикладів:






limit() і offset()






Методи limit() і offset() задають частини запиту,
що йдуть після LIMIT і OFFSET. Варто зазначити, що не всі СУБД підтримують саме синтаксис LIMIT і OFFSET.
Якщо він не підтримується, то конструктор запитів переписує весь SQL запит для досягнення подібного ефекту.



Кілька прикладів:






join() і його варіанти






Метод join() і його варіанти задають порядок і параметри обʼєднання таблиць
з використанням INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, CROSS JOIN та NATURAL JOIN.
Параметр $table визначає таблицю, з якою виконується обʼєднання.
Імʼя таблиці може містити префікс схеми або псевдонім. Метод екранує імʼя таблиці,
якщо воно не містить дужок, що означає використання підзапиту або виразу.
Параметр $conditions задає умову обʼєднання. Синтаксис такий же, як і в where().
Через $params вказуються параметри, які підставляються у запит.



Варто зазначити, що цей метод відрізняється від інших тим,
що кожен наступний його виклик додає частину запиту до попередніх.



Кілька прикладів:






group()






Метод group() задає частину запиту після GROUP BY.
Параметр $columns визначає поля, за якими буде здійснюватися групування і може бути або рядком,
розділених комами полів, або масивом полів. Імена полів можуть містити префікси.
Метод автоматично екранує імена полів, якщо вони не містять дужок (що означає використання виразів).



Кілька прикладів:






having()






Метод having() задає частину запиту після HAVING.
Використовується так само, як і where().



Кілька прикладів:






union()






Метод union() задає частину запиту після UNION.
Він додає $sql до створеного запитом, використовуючи UNION.
Кілька викликів union() додадуть кілька частин запиту.



Кілька прикладів:






Виконання запитів



Після виклику методів для побудови запиту, наведених вище, для виконання запиту можна використовувати методи DAO,
як описано у розділі «Обʼєкти доступу до даних (DAO)».
Приміром, ми можемо викликати CDbCommand::queryRow() для отримання рядка або CDbCommand::queryAll()
для отримання всіх рядків одразу.



Приклад:






Отримання SQL



Крім виконання запитів, які ми створили за допомогою конструктора, можна також отримати їх SQL.
Зробити це можна за допомогою CDbCommand::getText().






Якщо у запиті є параметри, отримати їх можна за допомогою властивості CDbCommand::params.



Альтернативний синтаксис побудови запитів



Іноді використання ланцюжка викликів може бути неоптимальним рішенням.
Конструктор запитів Yii дозволяє створити запит шляхом завдання полів обʼєкта.
Для кожного методу конструктора запитів є відповідне поле з таким же імʼям.
Присвоєння значення полю еквівалентно виклику відповідного метода.
Приміром, наведені нижче рядки еквівалентні, якщо $command — обʼєкт CDbCommand:






Більш того, метод CDbConnection::createCommand() може приймати масив як параметр.
Пари імʼя-значення з масиву будуть використані для ініціалізації полів створеного екземпляра CDbCommand.
Тобто для побудови запиту можна використовувати наступний код:






Побудова кількох запитів



Для побудови декількох запитів екземпляр CDbCommand може бути використаний кілька разів.
Перед тим, як побудувати новий запит необхідно викликати метод CDbCommand::reset()
для очищення попереднього запиту. Приклад:






Побудова запитів для зміни даних



До запитів для зміни даних відносяться SQL запити для вставки, оновлення та видалення даних з бази.
У побудовнику запитів є відповідні методи insert, update і delete.
На відміну від запитів отримання даних, описаних вище, дані методи будують повний SQL-запит і тут же виконують його.



insert()






Метод insert() будує і виконує SQL запит INSERT. Параметр $table вказує,
в яку таблицю виконується вставка, а $columns - задає масив пар імʼя-значення поля для вставки.
Метод екранує імʼя таблиці і використовує параметри для значень, що вставляються.



Приклад:






update()






Метод update() будує і виконує SQL запит UPDATE. Параметр $table вказує таблицю,
яка буде оновлюватись; $columns є масивом пар імʼя-значення, що задає значення полів,
які будуть оновлюватися; $conditions і $params еквівалентні таким же параметрам у
where() і визначають частину запиту UPDATE після WHERE.
Метод екранує імʼя таблиці і використовує параметри для значень, що оновлюються.



Приклад:






delete






Метод delete() будує і виконує SQL запит DELETE.
Параметр $table вказує таблицю, з якої видаляються значення; $conditions та $params еквівалентні
таким же параметрам у where(), які визначають частину запиту DELETE після WHERE.
Метод екранує імʼя таблиці.



Приклад:






Побудова запитів зміни схеми



Крім звичайних запитів для отримання даних і роботи з ними,
побудовник може збирати і виконувати SQL-запити для зміни схеми бази даних.
Підтримуються наступні запити:





Інформація: Незважаючи на те, що для різних СУБД запити для зміни схеми різні,
  побудовник запитів надає єдиний інтерфейс для їх створення. Це спрощує завдання мігрування із однієї СУБД на іншу.





Абстрактні типи даних



Побудовник запитів вводить ряд абстрактних типів даних, які можна використовувати для опису полів таблиці.
На відміну від реальних типів даних, які відрізняються в різних СУБД, абстрактні типи не залежать від СУБД.
При використанні їх для опису типів полів, побудовник запитів конвертує абстрактні типи у відповідні їм реальні.



Побудовник запитів підтримує такі абстрактні типи:



createTable()






Метод createTable() будує і виконує SQL запит для створення таблиці.
Параметр $table задає імʼя таблиці, що створюється. Параметр $columns визначає поля нової таблиці.
Вони повинні бути вказані у вигляді пар імʼя-значення (тобто 'username' => 'string').
Параметр $options задає додатковий фрагмент SQL, який буде додано до SQL, що генерується.
Побудовник запиту екранує імʼя таблиці та імена полів.



Для вказівки визначення поля можна використовувати абстрактний тип даних, як вже було описано раніше.
Побудовник конвертує абстрактний тип даних до відповідного реального типу даних у відповідності
до використовуваної СУБД. Приміром, string у випадку MySQL зконвертує у varchar (255).



Визначення поля також може містити неабстрактний тип даних і специфікацій.
Вони будуть в результуючому SQL без будь-яких змін.
Приміром, point не є абстрактним типом даних і при використанні у визначенні поля
буде включений в отримуваний SQL як є. string NOT NULL буде зконвертований
у varchar (255) NOT NULL (тобто конвертується лише абстрактний тип string).



Приклад створення таблиці:






renameTable()






Метод renameTable() будує і виконує SQL запит для перейменування таблиці.
Параметр $table задає імʼя таблиці, що перейменовується . Параметр $newName визначає нове імʼя таблиці.
Побудовник запиту екранує імена таблиці.



Приклад перейменування таблиці:






dropTable()






Метод dropTable() будує і виконує SQL запит для видалення таблиці.
Параметр $table визначає імʼя таблиці, що видаляється. Побудовник запиту екранує імʼя таблиці.



Приклад видалення таблиці:






truncateTable()






Метод truncateTable() будує і виконує SQL запит для очищення всіх даних таблиці.
Параметр $table визначає імʼя таблиці, що очищується. Побудовник запиту екранує імʼя таблиці.



Приклад очищення таблиці:






addColumn()






Метод addColumn() будує і виконує SQL запит для додавання нового поля таблиці.
Параметр $table задає імʼя таблиці, до якої буде додаватися нове поле.
Параметр $column - імʼя нового поля. $type задає тип поля.
Визначення поля може містити абстрактний тип даних, як вже було описано у підрозділі «createTable».
Побудовник запиту екранує імʼя таблиці та імʼя поля.



Приклад додавання поля:






dropColumn()






Метод dropColumn() будує і виконує SQL запит для видалення поля таблиці.
Параметр $table задає імʼя таблиці, з якої видаляється поле. Параметр $column визначає імʼя поля, що видаляється.
Побудовник запиту екранує імʼя таблиці та імʼя поля.



Приклад видалення поля таблиці:






renameColumn()






Метод renameColumn() будує і виконує SQL запит для перейменування поля таблиці.
Параметр $table задає імʼя таблиці, поле якої буде перейменовано.
Параметр $name визначає імʼя поля, яке переіменовується. $newName задає імʼя нового поля.
Побудовник запиту екранує імʼя таблиці та імена полів.



Приклад перейменування поля таблиці:






alterColumn()






Метод alterColumn() будує і виконує SQL запит для зміни поля таблиці.
Параметр $table задає імʼя таблиці, поле якої буде змінено.
Параметр $column визначає імʼя змінюваного поля. $type задає нове визначення поля,
яке може містити абстрактний тип даних, як було описано в підрозділі «createTable».
Побудовник запитів екранує імʼя таблиці та імʼя поля.



Приклад зміни поля таблиці:






addForeignKey()






Метод addForeignKey() будує і виконує SQL запит для додавання зовнішнього ключа в таблицю.
Параметр $name задає імʼя зовнішнього ключа.
Параметри $table та $columns визначають імʼя таблиці та імʼя поля зовнішнього ключа.
Якщо вказано декілька полів, то вони повинні бути розділені комами.
Параметри $refTable і $refColumns визначають імʼя таблиці та імʼя поля, на яке посилається зовнішній ключ.
Параметри $delete і $update задають SQL-опції ON DELETE та ON UPDATE відповідно.
Більшість СУБД підтримують наступні опції: RESTRICT, CASCADE, NO ACTION, SET DEFAULT та SET NULL.
Побудовник запитів екранує імʼя таблиці, імʼя індексу та імена полів.



Приклад додавання зовнішнього ключа:






dropForeignKey()






Метод dropForeignKey() будує і виконує SQL запит для видалення зовнішнього ключа.
Параметр $name задає імʼя зовнішнього ключа, який потрібно видалити.
Параметр $table - імʼя таблиці, з якої видаляється ключ. Побудовник запиту екранує імʼя таблиці та імʼя ключа.



Приклад видалення зовнішнього ключа:






createIndex()






Метод createIndex() будує і виконує SQL запит для створення індексу.
Параметр $name задає імʼя індексу, який буде створено. Параметр $table — імʼя таблиці, у якій створюється індекс.
Параметр $column — імʼя індексованого поля. Параметр $unique визначає, чи повинен бути індекс унікальним.
Якщо індекс складається із декількох полів, то вони розділяються комами.
Побудовник запитів екранує імʼя таблиці, імʼя індексу та імена полів.



Приклад створення індексу:






dropIndex()






Метод dropIndex() будує і виконує SQL запит для видалення індексу.
Параметр $name задає імʼя індексу, що видаляється. Параметр $table — імʼя таблиці, у якій видаляється індекс.
Побудовник запиту екранує імʼя таблиці та імʼя індексу.



Приклад видалення індексу: