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

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

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

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

Следующий код показывает обычное использование построителя запросов:

$rows = (new \yii\db\Query())
    ->select(['id', 'email'])
    ->from('user')
    ->where(['last_name' => 'Smith'])
    ->limit(10)
    ->all();

Приведённый выше код создаёт и выполняет следующее SQL выражение, где параметр :last_name привязывается к строке 'Smith'.

SELECT `id`, `email` 
FROM `user`
WHERE `last_name` = :last_name
LIMIT 10

Info: В основном вы будете работать с [[yii\db\Query]] вместо [[yii\db\QueryBuilder]]. Последний вызывается неявно при вызове одного из методов запроса. [[yii\db\QueryBuilder]] это класс, отвечающий за генерацию зависимого от СУБД SQL выражения (такие как экранирование имён таблиц/столбцов) из независимых от СУБД объектов [[yii\db\Query]].

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

Создав объект [[yii\db\Query]], вы можете вызвать различные методы для создания различных частей SQL выражения. Имена методов напоминают ключевые слова SQL, используемые в соответствующих частях SQL запроса. Например, чтобы указать FROM часть запроса, вам нужно вызвать метод [[yii\db\Query::from()|from()]]. Все методы построителя запросов возвращают свой объект, который позволяет объединять несколько вызовов в цепочку.

Далее будет описание каждого метода построителя запросов.

[[yii\db\Query::select()|select()]]

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

$query->select(['id', 'email']);

// эквивалентно:

$query->select('id, email');

Имена столбцов могут быть выбраны вместе с префиксами таблиц и/или алиасами столбцов, также как при записи обычного SQL выражения. Например,

$query->select(['user.id AS user_id', 'email']);

// эквивалентно:

$query->select('user.id AS user_id, email');

Если вы используете формат массива для указания столбцов, вы можете также указать ключи массива для указания алиасов столбцов. Например, приведённый выше код может быть переписан:

$query->select(['user_id' => 'user.id', 'email']);

Если вы не вызываете метод [[yii\db\Query::select()|select()]] при создании запроса, будет использована *, что означает выбрать все столбцы.

Кроме имён столбцов, вы можете также использовать SQL выражения. Вы должны использовать формат массива для использования выражений, которые содержат запятые для предотвращения некорректного автоматического экранирования. Например,

$query->select(["CONCAT(first_name, ' ', last_name) AS full_name", 'email']);

Начиная с версии 2.0.1, вы также можете использовать подзапросы. Вы должны указывать каждый подзапрос в выражении как объект [[yii\db\Query]]. Например,

$subQuery = (new Query())->select('COUNT(*)')->from('user');

// SELECT `id`, (SELECT COUNT(*) FROM `user`) AS `count` FROM `post`
$query = (new Query())->select(['id', 'count' => $subQuery])->from('post');

Чтоб выбрать конкретные строки, вы можете вызвать метод [[yii\db\Query::distinct()|distinct()]]:

// SELECT DISTINCT `user_id` ...
$query->select('user_id')->distinct();

Вы можете вызвать [[yii\db\Query::addSelect()|addSelect()]] для добавления полей. Например,

$query->select(['id', 'username'])
    ->addSelect(['email']);

[[yii\db\Query::from()|from()]]

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

// SELECT * FROM `user`
$query->from('user');

Вы можете указать имена таблиц в виде строки или массива. Имена таблиц могут содержать префикс схемы и/или алиасы таблиц, как при написании обычного SQL выражения. Например,

$query->from(['public.user u', 'public.post p']);

// эквивалентно:

$query->from('public.user u, public.post p');

Если вы используете формат массива, вы можете использовать ключи массива для указания алиасов:

$query->from(['u' => 'public.user', 'p' => 'public.post']);

Кроме имён таблиц, вы можете, также, как и в select, указывать подзапросы в виде объекта [[yii\db\Query]].

$subQuery = (new Query())->select('id')->from('user')->where('status=1');

// SELECT * FROM (SELECT `id` FROM `user` WHERE status=1) u 
$query->from(['u' => $subQuery]);

[[yii\db\Query::where()|where()]]

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

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

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

Строковый формат - это лучший выбор для простых условий. Он работает так, будто вы просто пишете SQL запрос. Например,

$query->where('status=1');

// или используя привязку параметров
$query->where('status=:status', [':status' => $status]);

Не встраивайте переменные непосредственно в условие, особенно если значение переменной получено от пользователя, потому что это делает ваше приложение подверженным атакам через SQL инъекции.

// Опасность! Не делайте так если вы не уверены, что $status это действительно число.
$query->where("status=$status");

При использовании привязки параметров, вы можете вызывать [[yii\db\Query::params()|params()]] или [[yii\db\Query::addParams()|addParams()]] для раздельного указания параметров.

$query->where('status=:status')
    ->addParams([':status' => $status]);

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

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

// ...WHERE (`status` = 10) AND (`type` IS NULL) AND (`id` IN (4, 8, 15))
$query->where([
    'status' => 10,
    'type' => null,
    'id' => [4, 8, 15],
]);

Как вы можете видеть, построитель запросов достаточно умен, чтобы правильно обрабатывать значения NULL или массивов.

Вы также можете использовать подзапросы:

$userQuery = (new Query())->select('id')->from('user');

// ...WHERE `id` IN (SELECT `id` FROM `user`)
$query->where(['id' => $userQuery]);

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

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

[operator, operand1, operand2, ...]

Операнды могут быть заданы в виде строкового формата, формата массива или формата операторов рекурсивно, в то время как оператор может быть одним из следующих:

  • and: операнды должны быть объединены с помощью оператора AND. Например, ['and', 'id=1', 'id=2'] сгенерирует id=1 AND id=2. Если операнд массив, он будет сконвертирован в строку по правилам описанным ниже. Например, ['and', 'type=1', ['or', 'id=1', 'id=2']] сгенерирует type=1 AND (id=1 OR id=2). Этот метод не производит никакого экранирования.

  • or: похож на оператор and за исключением того, что будет использоваться оператор OR.

  • between: первый операнд должен быть именем столбца, а второй и третий оператор должны быть начальным и конечным значением диапазона. Например, ['between', 'id', 1, 10] сгенерирует id BETWEEN 1 AND 10.

  • not between: похож на between за исключением того, что BETWEEN заменяется на NOT BETWEEN в сгенерированном условии.

  • in: первый операнд должен быть столбцом или выражением БД. Второй операнд может быть либо массивом, либо объектом Query. Будет сгенерировано условие IN. Если второй операнд массив, он будет представлять набор значений, которым может быть равен столбец или выражение БД; Если второй операнд объект Query, будет сформирован подзапрос, который будет использован как диапазон для столбца или выражения БД. Например, ['in', 'id', [1, 2, 3]] сформирует id IN (1, 2, 3). Метод будет правильно экранировать имя столбца и значения диапазона. Оператор in также поддерживает составные столбцы. В этом случае, первый операнд должен быть массивом названий столбцов, в то время как операнд 2 должен быть массивом массивов или объектом Query представляющим диапазоны для столбцов.

  • not in: похож на оператор in, кроме того что IN будет заменён на NOT IN в сформированном условии.

  • like: первый операнд должен быть столбцом или выражением БД, а второй операнд будет строкой или массивом представляющим значения, на которые должны быть похожи столбцы или выражения БД. Например, ['like', 'name', 'tester'] сформирует name LIKE '%tester%'. Когда диапазон значений задан в виде массива, несколько LIKE утверждений будут сформированы и соединены с помощью AND. Например, ['like', 'name', ['test', 'sample']] сформирует name LIKE '%test%' AND name LIKE '%sample%'. Вы также можете передать третий необязательный операнд, для указания способа экранирования специальных символов в значениях. Операнд должен быть представлен массивом соответствия специальных символов их экранированным аналогам. Если этот операнд не задан, то будет использовано соответствие по умолчанию. Вы можете также использовать значение false или пустой массив, чтоб указать что значения уже экранированы. Обратите внимание, что при использовании массива соответствия экранирования (или если третий операнд не передан), значения будут автоматически заключены в символы процентов.

    Note: При использовании PostgreSQL вы можете использовать также ilike вместо like для регистронезависимого поиска.

  • or like: похож на оператор like, только утверждения LIKE будут объединяться с помощью оператора OR, если второй операнд будет представлен массивом.

  • not like: похож на оператор like, только LIKE будет заменён на NOT LIKE в сгенерированном условии.

  • or not like: похож на оператор not like, только утверждения NOT LIKE будут объединены с помощью OR.

  • exists: требует один операнд, который должен быть экземпляром [[yii\db\Query]] представляющим подзапрос. Будет сгенерировано выражение EXISTS (sub-query).

  • not exists: похож на оператор exists и сформирует выражение NOT EXISTS (sub-query).

  • >, <=, или другие валидные операторы БД, которые требуют двух операндов: первый операнд должен быть именем столбца, второй операнд это значение. Например, ['>', 'age', 10] сформирует age>10.

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

Вы можете использовать [[yii\db\Query::andWhere()|andWhere()]] или [[yii\db\Query::orWhere()|orWhere()]] для добавления дополнительных условий. Вы можете использовать эти вызовы несколько раз для добавления нескольких условий. Например,

$status = 10;
$search = 'yii';

$query->where(['status' => $status]);

if (!empty($search)) {
    $query->andWhere(['like', 'title', $search]);
}

Если $search не пустое, то будет сформировано следующее условие WHERE:

WHERE (`status` = 10) AND (`title` LIKE '%yii%')

Условия для фильтров

Когда условие WHERE формируется на основе пользовательского ввода, обычно, хочется проигнорировать не заданные значения. Например, в форме поиска, которая позволяет осуществлять поиск по имени пользователя или email, вы хотели бы игнорировать username/email условие, если пользователь ничего не ввёл в поле ввода. Вы можете достичь этого используя метод [[yii\db\Query::filterWhere()|filterWhere()]].

// $username и $email вводит пользователь
$query->filterWhere([
    'username' => $username,
    'email' => $email,
]);

Единственное отличие между [[yii\db\Query::filterWhere()|filterWhere()]] и [[yii\db\Query::where()|where()]] заключается в игнорировании пустых значений, переданных в условие в форме массива. Таким образом если $email будет пустым, а $username нет, то приведённый выше код сформирует условие WHERE username=:username.

Info: значение признаётся пустым, если это null, пустой массив, пустая строка или строка состоящая из одних пробельных символов.

Также вместо [[yii\db\Query::andWhere()|andWhere()]] и [[yii\db\Query::orWhere()|orWhere()]], вы можете использовать [[yii\db\Query::andFilterWhere()|andFilterWhere()]] и [[yii\db\Query::orFilterWhere()|orFilterWhere()]] для добавления дополнительных условий фильтрации.

[[yii\db\Query::orderBy()|orderBy()]]

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

// ... ORDER BY `id` ASC, `name` DESC
$query->orderBy([
    'id' => SORT_ASC,
    'name' => SORT_DESC,
]);

В данном коде, ключи массива - это имена столбцов, а значения массива - это соответствующее направление сортировки. PHP константа SORT_ASC определяет сортировку по возрастанию и SORT_DESC сортировка по умолчанию.

Если ORDER BY содержит только простые имена столбцов, вы можете определить их с помощью столбцов, также как и при написании обычного SQL. Например,

$query->orderBy('id ASC, name DESC');

Note: Вы должны использовать массив для указания ORDER BY содержащих выражения БД.

Вы можете вызывать [[yii\db\Query::addOrderBy()|addOrderBy()]] для добавления столбцов в фрагмент ORDER BY.

$query->orderBy('id ASC')
    ->addOrderBy('name DESC');

[[yii\db\Query::groupBy()|groupBy()]]

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

// ... GROUP BY `id`, `status`
$query->groupBy(['id', 'status']);

Если фрагмент GROUP BY содержит только простые имена столбцов, вы можете указать их используя строку, также как в обычном SQL выражении.

$query->groupBy('id, status');

Note: Вы должны использовать массив для указания GROUP BY содержащих выражения БД.

Вы можете вызывать [[yii\db\Query::addGroupBy()|addGroupBy()]] для добавления имён столбцов в фрагмент GROUP BY. For example,

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

[[yii\db\Query::having()|having()]]

Метод [[yii\db\Query::having()|having()]] определяет фрагмент HAVING SQL запроса. Он принимает условия, которое может быть определено тем же способом, что и для where().

// ... HAVING `status` = 1
$query->having(['status' => 1]);

Пожалуйста, обратитесь к документации для where() для более подробной информации о определении условий.

Вы можете вызывать [[yii\db\Query::andHaving()|andHaving()]] или [[yii\db\Query::orHaving()|orHaving()]] для добавления дополнительных условий в фрагмент HAVING.

// ... HAVING (`status` = 1) AND (`age` > 30)
$query->having(['status' => 1])
    ->andHaving(['>', 'age', 30]);

[[yii\db\Query::limit()|limit()]] и [[yii\db\Query::offset()|offset()]]

Методы [[yii\db\Query::limit()|limit()]] и [[yii\db\Query::offset()|offset()]] определяют фрагменты LIMIT и OFFSET SQL запроса.

// ... LIMIT 10 OFFSET 20
$query->limit(10)->offset(20);

Если вы определяете неправильный limit или offset (например отрицательное значение), они будут проигнорированы.

Info: Для СУБД, которые не поддерживают LIMIT и OFFSET (такие как MSSQL), построитель запросов будет генерировать SQL выражения, которые эмулирует поведение LIMIT/OFFSET.

[[yii\db\Query::join()|join()]]

Метод [[yii\db\Query::join()|join()]] определяет фрагмент JOIN SQL запроса.

// ... LEFT JOIN `post` ON `post`.`user_id` = `user`.`id`
$query->join('LEFT JOIN', 'post', 'post.user_id = user.id');

Метод [[yii\db\Query::join()|join()]] принимает четыре параметра:

  • $type: тип объединения, например, 'INNER JOIN', 'LEFT JOIN'.
  • $table: имя таблицы, которая должна быть присоединена.
  • $on: необязательное условие объединения, то есть фрагмент ON. Пожалуйста, обратитесь к документации для where() для более подробной информации о определении условий. Отметим, что синтаксис массивов не работает для задания условий для столбцов, то есть ['user.id' => 'comment.userId'] будет означать условие, где ID пользователя долен быть равен строке 'comment.userId'. Вместо этого стоит указывать условие в виде строки 'user.id = comment.userId'.
  • $params: необязательные параметры присоединяемые к условию объединения.

Вы можете использовать следующие сокращающие методы для указания INNER JOIN, LEFT JOIN и RIGHT JOIN, в указанном порядке.

  • [[yii\db\Query::innerJoin()|innerJoin()]]
  • [[yii\db\Query::leftJoin()|leftJoin()]]
  • [[yii\db\Query::rightJoin()|rightJoin()]]

Например,

$query->leftJoin('post', 'post.user_id = user.id');

Для соединения с несколькими таблицами, вызовите вышеуказанные методы несколько раз.

Кроме соединения с таблицами, вы можете также присоединять подзапросы. Чтобы это сделать, укажите объединяемый подзапрос как объект [[yii\db\Query]].

$subQuery = (new \yii\db\Query())->from('post');
$query->leftJoin(['u' => $subQuery], 'u.id = author_id');

В этом случае, вы должны передать подзапросы в массиве и использовать ключи для определения алиасов.

[[yii\db\Query::union()|union()]]

Метод [[yii\db\Query::union()|union()]] определяет фрагмент UNION SQL запроса.

$query1 = (new \yii\db\Query())
    ->select("id, category_id AS type, name")
    ->from('post')
    ->limit(10);

$query2 = (new \yii\db\Query())
    ->select('id, type, name')
    ->from('user')
    ->limit(10);

$query1->union($query2);

Вы можете вызвать [[yii\db\Query::union()|union()]] несколько раз для присоединения фрагментов UNION.

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

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

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

Например,

// SELECT `id`, `email` FROM `user`
$rows = (new \yii\db\Query())
    ->select(['id', 'email'])
    ->from('user')
    ->all();

// SELECT * FROM `user` WHERE `username` LIKE `%test%`
$row = (new \yii\db\Query())
    ->from('user')
    ->where(['like', 'username', 'test'])
    ->one();

Note: метод [[yii\db\Query::one()|one()]] вернёт только первую строку результата запроса. Он НЕ добавляет LIMIT 1 в генерируемый SQL. Это хорошо и предпочтительно если вы знаете, что запрос вернёт только одну или несколько строк данных (например, при запросе по первичному ключу). Однако, если запрос потенциально может вернут много строк данных, вы должны вызвать limit(1) для повышения производительности, Например, (new \yii\db\Query())->from('user')->limit(1)->one().

Все методы выборки могут получать необязательный параметр $db, представляющий [[yii\db\Connection|соединение с БД]], которое должно использоваться, чтобы выполнить запрос к БД. Если вы упускаете этот параметр, будет использоваться компонент приложения $db. Ниже приведён ещё один пример использования метода [[yii\db\Query::count()|count()]]:

// executes SQL: SELECT COUNT(*) FROM `user` WHERE `last_name`=:last_name
$count = (new \yii\db\Query())
    ->from('user')
    ->where(['last_name' => 'Smith'])
    ->count();

При вызове методов выборки [[yii\db\Query]], внутри на самом деле проводится следующая работа:

  • Вызывается [[yii\db\QueryBuilder]] для генерации SQL запроса на основе текущего [[yii\db\Query]];
  • Создаёт объект [[yii\db\Command]] с сгенерированным SQL запросом;
  • Вызывается выбирающий метод (например [[yii\db\Command::queryAll()|queryAll()]]) из [[yii\db\Command]] для выполнения SQL запроса и извлечения данных.

Иногда вы можете захотеть увидеть или использовать SQL запрос построенный из объекта [[yii\db\Query]]. Этой цели можно добиться с помощью следующего кода: 

$command = (new \yii\db\Query())
    ->select(['id', 'email'])
    ->from('user')
    ->where(['last_name' => 'Smith'])
    ->limit(10)
    ->createCommand();

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

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

Индексация результатов запроса

При вызове [[yii\db\Query::all()|all()]] возвращается массив строк индексированный последовательными целыми числами. Иногда вам может потребоваться индексировать его по-другому, например, сделать индекс по указанному столбцу или значением выражения. Вы можете реализовать такое поведение через вызов [[yii\db\Query::indexBy()|indexBy()]] перед вызовом [[yii\db\Query::all()|all()]].

// возвращает [100 => ['id' => 100, 'username' => '...', ...], 101 => [...], 103 => [...], ...]
$query = (new \yii\db\Query())
    ->from('user')
    ->limit(10)
    ->indexBy('id')
    ->all();

Для индексации по значению выражения, передайте анонимную функцию в метод [[yii\db\Query::indexBy()|indexBy()]]:

$query = (new \yii\db\Query())
    ->from('user')
    ->indexBy(function ($row) {
        return $row['id'] . $row['username'];
    })->all();

Анонимная функция должна принимать параметр $row, который содержит текущую строку запроса и должна вернуть скалярное значение, которое будет использоваться как значение индекса для текущей строки.

Пакетная выборка

При работе с большими объемами данных, методы на подобие [[yii\db\Query::all()]] не подходят, потому что они требуют загрузки всех данных в память. Чтобы сохранить требования к памяти минимальными, Yii предоставляет поддержку так называемых пакетных выборок. Пакетная выборка делает возможным курсоры данных и выборку данных пакетами.

Пакетная выборка может использоваться следующим образом:

use yii\db\Query;

$query = (new Query())
    ->from('user')
    ->orderBy('id');

foreach ($query->batch() as $users) {
    // $users это массив из 100 или менее строк из таблицы пользователей
}

// или если вы хотите перебрать все строки по одной
foreach ($query->each() as $user) {
    // $user представляет одну строку из выборки
}

Метод [[yii\db\Query::batch()]] и [[yii\db\Query::each()]] возвращает объект [[yii\db\BatchQueryResult]], который реализует интерфейс Iterator и может использоваться в конструкции foreach. Во время первой итерации будет выполнен SQL запрос к базе данных. Данные будут выбираться пакетами в следующих итерациях. По умолчанию, размер пакета имеет размер 100, то есть при каждой выборке будет выбираться по 100 строк. Вы можете изменить размер пакета, передав первый параметр в метод batch() или each().

По сравнению с [[yii\db\Query::all()]], пакетная выборка загружает только по 100 строк данных за раз в память. Если вы обрабатываете данные и затем сразу выбрасываете их, пакетная выборка может помочь уменьшить использование памяти.

Если указать индексный столбец через [[yii\db\Query::indexBy()]], в пакетной выборке индекс будет сохранятся. Например,

$query = (new \yii\db\Query())
    ->from('user')
    ->indexBy('username');

foreach ($query->batch() as $users) {
    // $users индексируется по столбцу "username"
}

foreach ($query->each() as $username => $user) {
    // ...
}

results matching ""

    No results matching ""