Главная > Блог > Web-разработка > Yii2 и Laravel >

Yii2. Построитель запросов

23.03.2019

Теги: PHP • Web-разработка • Yii2 • БазаДанных • Запрос • Теория • Фреймворк

Построитель запросов позволяет конструировать SQL выражения в программируемом и независимом от СУБД виде. В сравнении с написанием чистого SQL выражения, использование построителя помогает писать более читаемый код и генерировать более безопасные SQL выражения.

Использование построителя запросов включает два этапа:

Создание объекта yii\db\Query, представляющего различные части (такие как SELECT, FROM) SQL выражения
Выполнение запроса методом yii\db\Query, таким как all() для извлечения данных из базы данных

Пример использования построителя запросов:

$rows = (new \yii\db\Query())
    ->select(['id', 'email'])
    ->from('user')
    ->where(['surname' => 'Иванов'])
    ->limit(10)
    ->all();

SELECT
    `id`, `email`
FROM
    `user`
WHERE
    `surname` = 'Иванов'
LIMIT
    10

Построение запросов

Создав объект yii\db\Query, вы можно вызвать различные методы для создания различных частей SQL выражения. Имена методов напоминают ключевые слова SQL, используемые в соответствующих частях SQL запроса.

Метод yii\db\Query::select()

Метод yii\db\Query::select()|select() определяет фрагмент SELECT SQL запроса. Можно указать столбцы, которые должны быть выбраны, они должны быть в виде массива или строки.

// в виде массива
$query->select(['id', 'name'])->all();
// в виде строки
$query->select('id, name')->all();

Имена столбцов могут быть выбраны вместе с префиксами таблиц и/или алиасами столбцов:

// в виде массива
$query->select(['category.id AS category_id', 'name'])->all();
// в виде массива
$query->select(['category_id' => 'category.id', 'name'])->all();
// в виде строки
$query->select('category.id AS category_id, name')->all();

Если не использовать метод select(), будут выбраны все столбцы.

Метод yii\db\Query::from()

Метод yii\db\Query::from() указывает фрагмент FROM SQL запроса:

$query = new \yii\db\Query();
$categories = $query->select(['*'])->from('category')->all();

SELECT * FROM `category`

Имена таблиц могут быть в виде строки или массива. Имена таблиц могут содержать префикс схемы и/или алиасы таблиц:

$query = new \yii\db\Query();
// имена таблиц в виде массива
$categories = $query->select(['*'])->from(['catalog.category c'])->all();
// имена таблиц в виде массива
$categories = $query->from(['с' => 'catalog.category'])->all();

SELECT * FROM `catalog`.`category` `c`

Метод yii\db\Query::where()

Метод yii\db\Query::where() определяет фрагмент WHERE SQL выражения. Можно использовать один из трёх форматов:

строковый формат, например, 'status=1'
формат массива, например, ['status' => 1, 'type' => 2]
формат операторов, например, ['like', 'name', 'Samsung']

Строковый формат

$query = new \yii\db\Query();
$categories = $query->select(['*'])->from('category')->where('parent_id=0')->all();

$parent_id = 0;
$categories = $query->from('category')
                    ->where('parent_id=:parent_id', [':parent_id' => $parent_id])
                    ->all();
$categories = $query->from('category')
                    ->where('parent_id=:parent_id')
                    ->addParams([':parent_id' => $parent_id])
                    ->all();

Формат массива

Формат массива лучше всего использовать для указания нескольких объединяемых через AND условий, каждое из которых является простым равенством.

$query = new \yii\db\Query();
$products = $query->from('product')
                  ->where(['category_id' => 1, 'id' => [1, 2, 3]])
                  ->all();

SELECT * FROM `product` WHERE (`category_id`=1) AND (`id` IN (1, 2, 3))

Формат операторов

Формат оператора позволяет задавать произвольные условия в программном стиле. Он имеет следующий вид:

[operator, operand1, operand2, ...]

Несколько примеров:

['and', 'id=1', 'id=2'] сгенерирует id=1 AND id=2
['or', 'id=1', 'id=2'] сгенерирует id=1 OR id=2
['and', 'type=1', ['or', 'id=1', 'id=2']] сгенерирует type=1 AND (id=1 OR id=2)
['between', 'id', 1, 10] сгенерирует id BETWEEN 1 AND 10
['not between', 'id', 1, 10] сгенерирует id NOT BETWEEN 1 AND 10
['in', 'id', [1, 2, 3]] сгенерирует id IN (1, 2, 3)
['not in', 'id', [1, 2, 3]] сгенерирует id NOT IN (1, 2, 3)
['like', 'name', 'Sumsung'] сгенерирует name LIKE '%Samsung%'
['not like', 'name', 'Sumsung'] сгенерирует name NOT LIKE '%Samsung%'
['like', 'name', ['Samsung', 'Lenovo']] сгенерирует name LIKE '%Samsung%' AND name LIKE '%Lenovo%'
['or like', 'name', ['Samsung', 'Lenovo']] сгенерирует name LIKE '%Samsung%' OR name LIKE '%Lenovo%'
['>', 'age', 10] сгенерирует age>10

Добавление условий

Можно использовать методы andWhere() или orWhere() для добавления дополнительных условий.

$query = new \yii\db\Query();
$products = $query->from('product')
                  ->where(['category_id' => 1])
                  ->andWhere(['id' => [1, 2, 3]])
                  ->all();

SELECT * FROM `product` WHERE (`category_id`=1) AND (`id` IN (1, 2, 3))

$query = new \yii\db\Query();
$products = $query->from('product')
                  ->where(['category_id' => 1])
                  ->orWhere(['like', 'name', 'Samsung'])
                  ->all();

SELECT * FROM `product` WHERE (`category_id`=1) OR (`name` LIKE '%Samsung%')

Метод yii\db\Query::orderBy()

Метод yii\db\Query::orderBy() определяет фрагмент ORDER BY SQL выражения.

$query = new \yii\db\Query();
$categories = $query->from('product')
                    ->where(['category_id' => 1])
                    ->orderBy([
                        'id' => SORT_ASC,
                        'name' => SORT_DESC,
                    ])
                    ->all();

$query = new \yii\db\Query();
$categories = $query->from('product')
                    ->where(['category_id' => 1])
                    ->orderBy('id ASC, name DESC')
                    ->all();

$query = new \yii\db\Query();
$categories = $query->from('product')
                    ->where(['category_id' => 1])
                    ->orderBy('id ASC')
                    ->addOrderBy('name DESC')
                    ->all();

SELECT * FROM `product` WHERE `category_id`=1 ORDER BY `id` ASC, `name` DESC

Метод yii\db\Query::groupBy()

Метод yii\db\Query::groupBy() определяет фрагмент GROUP BY SQL запроса.

$query->groupBy(['id', 'name']);
$query->groupBy('id, name');
$query->groupBy('id')->addGroupBy('name');

SELECT * FROM `product` WHERE `category_id`=1 GROUP BY `id`, `name`

Прочие методы

Метод yii\db\Query::having() определяет фрагмент HAVING SQL запроса.
Методы yii\db\Query::limit() и yii\db\Query::offset() определяют фрагменты LIMIT и OFFSET SQL запроса.
Метод yii\db\Query::join() определяет фрагмент JOIN SQL запроса.
Метод yii\db\Query::union() определяет фрагмент UNION SQL запроса.

Методы выборки

Класс yii\db\Query предоставляет целый набор методов для разных вариантов выборки:

yii\db\Query::all() возвращает массив строк, каждая из которых это ассоциативный массив пар ключ-значение.
yii\db\Query::one() возвращает первую строку запроса.
yii\db\Query::column() возвращает первый столбец результата.
yii\db\Query::scalar() возвращает скалярное значение первого столбца первой строки результата.
yii\db\Query::exists() возвращает значение указывающее, что выборка содержит результат.
yii\db\Query::count() возвращает результат COUNT запроса.
Другие методы агрегирования запросов: yii\db\Query::sum($q), yii\db\Query::average($q), yii\db\Query::max($q), yii\db\Query::min($q). Параметр $q обязателен для этих методов и могут содержать либо имя столбца, либо выражение БД.

$count = (new \yii\db\Query())
    ->from('user')
    ->where(['surname' => 'Иванов'])
    ->count();

SELECT COUNT(*) FROM `user` WHERE `surname`='Иванов'

Отладка SQL-запроса

Чтобы посмотреть запрос, сформированный с помощью yii\db\Query, можно воспользоваться следующим кодом:

$command = (new \yii\db\Query())
    ->select(['id', 'email'])
    ->from('user')
    ->where(['surname' => 'Иванов'])
    ->limit(10)
    ->createCommand();

// показать SQL запрос
echo $command->sql;

// показать привязываемые параметры
print_r($command->params);

// возвращает все строки запроса
$rows = $command->queryAll();

Дополнительно

Построитель запросов

Поиск: PHP • Web-разработка • Yii2 • Запрос • Фреймворк • База данных • Query • SQL