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

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

$user = Yii::app()->db->createCommand()
    ->select('id, username, profile')
    ->from('tbl_user u')
    ->join('tbl_profile p', 'u.id=p.user_id')
    ->where('id=:id', array(':id'=>$id))
    ->queryRow();

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

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

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

$command = Yii::app()->db->createCommand('SELECT * FROM tbl_user');
// наступний рядок НЕ додасть WHERE до SQL
$command->where('id=:id', array(':id'=>$id));

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

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

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

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

$command = Yii::app()->db->createCommand();

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

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

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

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

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

select()

function select($columns='*')

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

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

// SELECT *
select()
// SELECT `id`, `username`
select('id, username')
// SELECT `tbl_user`.`id`, `username` AS `name`
select('tbl_user.id, username as name')
// SELECT `id`, `username`
select(array('id', 'username'))
// SELECT `id`, count(*) as num
select(array('id', 'count(*) as num'))

selectDistinct()

function selectDistinct($columns)

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

SELECT DISTINCT `id`, `username`

from()

function from($tables)

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

Приклади:

// FROM `tbl_user`
from('tbl_user')
// FROM `tbl_user` `u`, `public`.`tbl_profile` `p`
from('tbl_user u, public.tbl_profile p')
// FROM `tbl_user`, `tbl_profile`
from(array('tbl_user', 'tbl_profile'))
// FROM `tbl_user`, (select * from tbl_profile) p
from(array('tbl_user', '(select * from tbl_profile) p'))

where()

function where($conditions, $params=array())

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

array(operator, operand1, operand2, ...)

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

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

// WHERE id=1 or id=2
where('id=1 or id=2')
// WHERE id=:id1 or id=:id2
where('id=:id1 or id=:id2', array(':id1'=>1, ':id2'=>2))
// WHERE id=1 OR id=2
where(array('or', 'id=1', 'id=2'))
// WHERE id=1 AND (type=2 OR type=3)
where(array('and', 'id=1', array('or', 'type=2', 'type=3')))
// WHERE `id` IN (1, 2)
where(array('in', 'id', array(1, 2))
// WHERE `id` NOT IN (1, 2)
where(array('not in', 'id', array(1,2)))
// WHERE `name` LIKE '%Qiang%'
where(array('like', 'name', '%Qiang%'))
// WHERE `name` LIKE '%Qiang' AND `name` LIKE '%Xue'
where(array('like', 'name', array('%Qiang', '%Xue')))
// WHERE `name` LIKE '%Qiang' OR `name` LIKE '%Xue'
where(array('or like', 'name', array('%Qiang', '%Xue')))
// WHERE `name` NOT LIKE '%Qiang%'
where(array('not like', 'name', '%Qiang%'))
// WHERE `name` NOT LIKE '%Qiang%' OR `name` NOT LIKE '%Xue%'
where(array('or not like', 'name', array('%Qiang%', '%Xue%')))

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

$keyword=$_GET['q'];
// екранує символи % та _
$keyword=strtr($keyword, array('%'=>'\%', '_'=>'\_'));
$command->where(array('like', 'title', '%'.$keyword.'%'));

order()

function order($columns)

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

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

// ORDER BY `name`, `id` DESC
order('name, id desc')
// ORDER BY `tbl_profile`.`name`, `id` DESC
order(array('tbl_profile.name', 'id desc'))

limit() і offset()

function limit($limit, $offset=null)
function offset($offset)

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

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

// LIMIT 10
limit(10)
// LIMIT 10 OFFSET 20
limit(10, 20)
// OFFSET 20
offset(20)

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

function join($table, $conditions, $params=array())
function leftJoin($table, $conditions, $params=array())
function rightJoin($table, $conditions, $params=array())
function crossJoin($table)
function naturalJoin($table)

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

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

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

// JOIN `tbl_profile` ON user_id=id
join('tbl_profile', 'user_id=id')
// LEFT JOIN `pub`.`tbl_profile` `p` ON p.user_id=id AND type=1
leftJoin('pub.tbl_profile p', 'p.user_id=id AND type=:type', array(':type'=>1))

group()

function group($columns)

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

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

// GROUP BY `name`, `id`
group('name, id')
// GROUP BY `tbl_profile`.`name`, `id`
group(array('tbl_profile.name', 'id'))

having()

function having($conditions, $params=array())

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

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

// HAVING id=1 or id=2
having('id=1 or id=2')
// HAVING id=1 OR id=2
having(array('or', 'id=1', 'id=2'))

union()

function union($sql)

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

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

// UNION (select * from tbl_profile)
union('select * from tbl_profile')

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

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

Приклад:

$users = Yii::app()->db->createCommand()
    ->select('*')
    ->from('tbl_user')
    ->queryAll();

Отримання SQL

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

$sql = Yii::app()->db->createCommand()
    ->select('*')
    ->from('tbl_user')
    ->text;

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

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

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

$command->select(array('id', 'username'));
$command->select = array('id', 'username');

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

$row = Yii::app()->db->createCommand(array(
    'select' => array('id', 'username'),
    'from' => 'tbl_user',
    'where' => 'id=:id',
    'params' => array(':id'=>1),
))->queryRow();

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

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

$command = Yii::app()->db->createCommand();
$users = $command->select('*')->from('tbl_users')->queryAll();
$command->reset();  // clean up the previous query
$posts = $command->select('*')->from('tbl_posts')->queryAll();

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

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

insert()

function insert($table, $columns)

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

Приклад:

// будуємо і виконуємо наступний SQL:
// INSERT INTO `tbl_user` (`name`, `email`) VALUES (:name, :email)
$command->insert('tbl_user', array(
    'name'=>'Tester',
    'email'=>'tester@example.com',
));

update()

function update($table, $columns, $conditions='', $params=array())

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

Приклад:

// будуємо і виконуємо наступний SQL:
// UPDATE `tbl_user` SET `name`=:name WHERE id=:id
$command->update('tbl_user', array(
    'name'=>'Tester',
), 'id=:id', array(':id'=>1));

delete

function delete($table, $conditions='', $params=array())

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

Приклад:

// будуємо і виконуємо наступний SQL:
// DELETE FROM `tbl_user` WHERE id=:id
$command->delete('tbl_user', 'id=:id', array(':id'=>1));

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

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

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

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

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

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

createTable()

function createTable($table, $columns, $options=null)

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

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

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

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

// CREATE TABLE `tbl_user` (
//     `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
//     `username` varchar(255) NOT NULL,
//     `location` point
// ) ENGINE=InnoDB
createTable('tbl_user', array(
    'id' => 'pk',
    'username' => 'string NOT NULL',
    'location' => 'point',
), 'ENGINE=InnoDB')

renameTable()

function renameTable($table, $newName)

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

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

// RENAME TABLE `tbl_users` TO `tbl_user`
renameTable('tbl_users', 'tbl_user')

dropTable()

function dropTable($table)

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

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

// DROP TABLE `tbl_user`
dropTable('tbl_user')

truncateTable()

function truncateTable($table)

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

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

// TRUNCATE TABLE `tbl_user`
truncateTable('tbl_user')

addColumn()

function addColumn($table, $column, $type)

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

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

// ALTER TABLE `tbl_user` ADD `email` varchar(255) NOT NULL
addColumn('tbl_user', 'email', 'string NOT NULL')

dropColumn()

function dropColumn($table, $column)

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

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

// ALTER TABLE `tbl_user` DROP COLUMN `location`
dropColumn('tbl_user', 'location')

renameColumn()

function renameColumn($table, $name, $newName)

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

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

// ALTER TABLE `tbl_users` CHANGE `name` `username` varchar(255) NOT NULL
renameColumn('tbl_user', 'name', 'username')

alterColumn()

function alterColumn($table, $column, $type)

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

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

// ALTER TABLE `tbl_user` CHANGE `username` `username` varchar(255) NOT NULL
alterColumn('tbl_user', 'username', 'string NOT NULL')

addForeignKey()

function addForeignKey($name, $table, $columns,
    $refTable, $refColumns, $delete=null, $update=null)

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

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

// ALTER TABLE `tbl_profile` ADD CONSTRAINT `fk_profile_user_id`
// FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`id`)
// ON DELETE CASCADE ON UPDATE CASCADE
addForeignKey('fk_profile_user_id', 'tbl_profile', 'user_id',
    'tbl_user', 'id', 'CASCADE', 'CASCADE')

dropForeignKey()

function dropForeignKey($name, $table)

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

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

// ALTER TABLE `tbl_profile` DROP FOREIGN KEY `fk_profile_user_id`
dropForeignKey('fk_profile_user_id', 'tbl_profile')

createIndex()

function createIndex($name, $table, $column, $unique=false)

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

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

// CREATE INDEX `idx_username` ON `tbl_user` (`username`)
createIndex('idx_username', 'tbl_user', 'username')

dropIndex()

function dropIndex($name, $table)

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

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

// DROP INDEX `idx_username` ON `tbl_user`
dropIndex('idx_username', 'tbl_user')