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