Звезда не активнаЗвезда не активнаЗвезда не активнаЗвезда не активнаЗвезда не активна
 

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

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

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



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

  • [[yii\db\Query::select()|select()]]
  • [[yii\db\Query::from()|from()]]
    • Префиксы
  • [[yii\db\Query::where()|where()]]
    • Строковый формат
    • Формат массива
    • Формат операторов
    • Добавление условий
    • Условия для фильтров
  • [[yii\db\Query::orderBy()|orderBy()]]
  • [[yii\db\Query::groupBy()|groupBy()]]
  • [[yii\db\Query::having()|having()]]
  • [[yii\db\Query::limit()|limit()]] и [[yii\db\Query::offset()|offset()]]
  • [[yii\db\Query::join()|join()]]
  • [[yii\db\Query::union()|union()]]

[[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::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.

Заберите ссылку на статью к себе, чтобы потом легко её найти ;)

Выберите, то, чем пользуетесь чаще всего:

Спасибо за внимание, оставайтесь на связи! Ниже ссылка на форум и обсуждение ; )


Обсудить эту статью

INFO: Вы отправляете сообщение как 'Гость'