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

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

  1. Создайте объект yii\db\Query для представления различных частей (например, SELECT, FROM) инструкции SELECT SQL.
  2. Выполните метод запроса (например, all ()) yii\db\Query для извлечения данных из базы данных.

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

$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

Создание запросов

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

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

select()

Метод select() указывает фрагмент SELECT инструкции SQL. Вы можете указать столбцы, которые будут выбраны либо в массиве, либо в строке, как показано ниже. Выбранные имена столбцов будут автоматически цитироваться, когда SQL-запрос генерируется из объекта запроса.

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

// equivalent to:

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

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

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

// equivalent to:

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

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

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

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

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

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

Как и во всех местах, где задействован необработанный SQL, вы можете использовать синтаксис синтаксиса агностиков СУБД для имен таблиц и столбцов при написании выражений БД в select. Вы также можете выбрать подзапросы и указать каждый подзапрос в терминах объекта 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');

Чтобы выделить отдельные строки, вы можете вызвать функцию distinct(), например:

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

Вы можете вызвать addSelect(), чтобы выбрать дополнительные столбцы. Например:

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

from()

Метод from() определяет фрагмент FROM инструкции SQL. Например:

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

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

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

// equivalent to:

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

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

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

Помимо имен таблиц, вы также можете выбрать из подзапросов, указав их в терминах объектов 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]);

where()

Метод where() определяет фрагмент WHERE для SQL-запроса. Вы можете использовать один из трех форматов для указания условия WHERE:

  • string формат, например, 'status=1'
  • hash формат, например. ['status' => 1, 'type' => 2]
  • operator формат, например. ['like', 'name', 'test']

String формат

Формат string лучше всего использовать для указания очень простых условий или если вам нужно использовать встроенные функции СУБД. Он работает так, как будто вы пишете сырой SQL. Например:

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

// or use parameter binding to bind dynamic parameter values
$query->where('status=:status', [':status' => $status]);

// raw SQL using MySQL YEAR() function on a date field
$query->where('YEAR(somedate) = 2015');

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

// Dangerous! Do NOT do this unless you are very certain $status must be an integer.
$query->where("status=$status");

При использовании привязки параметров вы можете вызвать params() или addParams() для указания параметров отдельно.

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

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

Хэш-формат

Хэш-формат лучше всего использовать для указания нескольких 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]);

Используя хэш-формат, Yii внутренне использует привязку параметров, в отличие от формата строки, здесь вам не нужно добавлять параметры вручную.

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

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

[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: операнд 1 должен быть именем столбца, а операнды 2 и 3 должны быть начальным и конечным значениями диапазона, в котором находится столбец. Например, ['between', 'id', 1, 10] будет генерировать id BETWEEN 1 AND 10.
  • not between: схожим с between за исключением того, что BETWEEN заменяется NOT BETWEEN в сгенерированном состоянии.
  • in: операнд 1 должен быть выражением столбца или DB. Операнд 2 может быть как массивом, так и объектом Query. Он будет генерировать условие IN. Если операнд 2 является массивом, он будет представлять диапазон значений, которые должно быть выражением столбца или DB; Если Operand 2 является объектом Query, будет создан подзапрос, который будет использоваться в качестве диапазона выражения столбца или DB. Например,['in', 'id', [1, 2, 3]] будет генерировать id IN (1, 2, 3). Метод корректно процитирует имя столбца и значения escape в диапазоне. Оператор in также поддерживает составные столбцы. В этом случае операнд 1 должен быть массивом столбцов, в то время как операнд 2 должен быть массивом массивов или объектом Query, представляющим диапазон столбцов.
  • not in: аналогично оператору in, кроме того, что IN заменяется NOT IN в сгенерированном состоянии.
  • like: операнд 1 должен быть столбцом или выражением DB, а операнд 2 - строкой или массивом, представляющим значения, которые должны быть похожими на выражение столбца или DB. Например, ['like', 'name', 'tester'] будет генерировать name LIKE '%tester%'. Когда диапазон значений задан как массив, будет генерироваться и объединяться несколько предикатов LIKE, используя AND. Например, ['like', 'name', ['test', 'sample']] будет генерировать name LIKE '%test%' AND name LIKE '%sample%'. Вы также можете предоставить необязательный третий операнд, чтобы указать, как избежать специальных символов в значениях. Операнд должен быть массивом сопоставлений от специальных символов к их экранированным экземплярам. Если этот операнд не указан, будет использоваться экранирование по умолчанию. Вы можете использовать false или пустой массив, чтобы указать, что значения уже экранированы, и не следует использовать escape. Обратите внимание, что при использовании экранирования экранирования (или третьего операнда не предусмотрено), значения будут автоматически заключены в пару процентов символов.
  • or like: аналогичный оператору like, за исключением того, что OR используется для конкатенации предикатов LIKE, когда операнд 2 является массивом.
  • not like: похож на like оператор, за исключением того, что LIKE заменяется NOT LIKE в сгенерированном состоянии.
  • or not like: похож на not like оператор, кроме того, что OR используется для конкатенации предикатов NOT LIKE.
  • exists: требуется один операнд, который должен быть экземпляром yii\db\Query, представляющим этот подзапрос. Он будет строить выражение EXISTS (sub-query).
  • not exists: аналогично оператору exists и строит выражение NOT EXISTS (sub-query).
  • >, <= или любой другой допустимый оператор БД, который принимает два операнда: первый операнд должен быть именем столбца, а второй операнд - значением. Например, ['>', 'age', 10] будет генерировать age>10.

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

Условия добавления

Вы можете использовать andWhere() или 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, основанных на входных данных конечных пользователей, вы обычно хотите игнорировать те входные значения, которые пусты. Например, в форме поиска, которая позволяет выполнять поиск по имени пользователя и электронной почте, вы хотели бы игнорировать условие имени пользователя/электронной почты, если пользователь не вводит ничего в поле ввода имени пользователя / электронной почты. Вы можете достичь этой цели, используя метод filterWhere():

// $username and $email are from user inputs
$query->filterWhere([
    'username' => $username,
    'email' => $email,
]);

Единственное отличие между filterWhere() и where() заключается в том, что первые будут игнорировать пустые значения, предоставленные в условии в хеш-формате. Поэтому, если $email пуст, а $username нет, то приведенный выше код приведет к условию SQL WHERE username=:username.

Как andWhere() и orWhere (), вы можете использовать andFilterWhere() и orFilterWhere(), чтобы добавить дополнительные условия фильтра к существующим. Кроме того, существует yii\db\Query::andFilterCompare(), который может разумно определять оператор на основе того, что находится в значении:

$query->andFilterCompare('name', 'John Doe');
$query->andFilterCompare('rating', '>9');
$query->andFilterCompare('value', '<=100');

Вы также можете явно указать оператор:

$query->andFilterCompare('name', 'Doe', 'like');

Существуют аналогичные методы для условия HAVING:

  • filterHaving()
  • andFilterHaving()
  • orFilterHaving()

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');

Вы можете вызвать addOrderBy(), чтобы добавить дополнительные столбцы к фрагменту ORDER BY. Например:

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

groupBy()

Метод groupBy() указывает фрагмент GROUP BY SQL-запроса. Например:

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

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

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

Вы можете вызвать addGroupBy(), чтобы добавить дополнительные столбцы к фрагменту GROUP BY. Например:

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

having()

Метод having() указывает фрагмент HAVING SQL-запроса. Он принимает условие, которое может быть указано так же, как и для where(). Например:

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

Пожалуйста, обратитесь к документации для where() за дополнительной информацией о том, как указать условие. Вы можете вызвать andHaving() или orHaving(), чтобы добавить дополнительные условия к фрагменту HAVING. Например:

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

limit() и offset()

Методы limit() и offset() определяют фрагменты LIMIT и OFFSET SQL-запроса. Например:

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

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

join()

Метод join() указывает фрагмент JOIN запроса SQL. Например:

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

Метод join() принимает четыре параметра:

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

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

  • innerJoin()
  • leftJoin()
  • 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');

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

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);

Вы можете вызвать union() несколько раз, чтобы добавить больше фрагментов UNION.

Методы запроса

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

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

Например:

// 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();

Все эти методы запроса принимают необязательный параметр $db, представляющий соединение с БД, которое должно использоваться для выполнения запроса БД. Если вы опустите этот параметр, компонент приложения db будет использоваться в качестве соединения с БД. Ниже приведен другой пример с использованием метода запроса 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;
  • Вызовите метод запроса (например, 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();
    
// show the SQL statement
echo $command->sql;
// show the parameters to be bound
print_r($command->params);

// returns all rows of the query result
$rows = $command->queryAll();

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

Когда вы вызовете функцию all(), она вернет массив строк, индексированных последовательными целыми числами. Иногда вам может понадобиться индексировать их по-разному, например индексирование по определенному столбцу или значениям выражения. Вы можете достичь этой цели, вызвав indexBy() перед all(). Например:

// returns [100 => ['id' => 100, 'username' => '...', ...], 101 => [...], 103 => [...], ...]
$query = (new \yii\db\Query())
    ->from('user')
    ->limit(10)
    ->indexBy('id')
    ->all();

Чтобы индексировать значения выражений, передайте анонимную функцию методу 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 is an array of 100 or fewer rows from the user table
}

// or if you want to iterate the row one by one
foreach ($query->each() as $user) {
    // $user represents one row of data from the user table
}

Метод 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 is indexed by the "username" column
}

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