Построитель запросов
- Введение
- Выполнение запросов к базе данных
- Разбиение результатов
- Отложенная потоковая передача результатов
- Агрегатные функции
- Выражения Select
- Сырые SQL-выражения
- Сырые sql-выражения
- Соединения Joins
- Объединения результатов Unions
- Основные выражения Where
- Выражения Where
- Выражения Or Where
- Выражение Where Not
- Выражения Where Any / All / None
- Выражения Where и JSON
- Дополнительные выражения Where
- Логическая группировка
- Расширенные выражения Where
- Выражения Where Exists
- Подзапросы выражений Where
- Полнотекстовый поиск
- Сортировка, группировка, ограничение и смещение
- Сортировка
- Группировка
- Ограничение и смещение
- Условные выражения
- Вставка
- Обновления-вставки
- Обновление
- Обновление столбцов JSON
- Увеличение и уменьшение отдельных значений
- Удаление
- Пессимистическая блокировка
- Отладка
Введение
Построитель запросов к базе данных Laravel предлагает удобный и гибкий интерфейс для создания и выполнения запросов к базе данных. Его можно использовать для выполнения большинства операций с базой данных в вашем приложении и он отлично работает со всеми поддерживаемыми Laravel системами баз данных.
Построитель запросов Laravel использует связывание параметров PDO для защиты приложения от SQL-инъекций. Нет необходимости чистить строки, передаваемые как связываемые параметры.
PDO не поддерживает связывание имен столбцов. Поэтому, вы никогда не должны использовать какие-либо входящие от пользователя данные в качестве имен столбцов, используемые вашими запросами, включая столбцы в запросах
order byи т.д.
Выполнение запросов к базе данных
Получение всех строк из таблицы
Вы можете использовать метод table фасада DB, чтобы начать запрос. Метод table возвращает текущий экземпляр построителя запросов для данной таблицы, позволяя вам связать больше ограничений к запросу и, наконец, получить результаты, используя метод get:
<?php
namespace App\Http\Controllers;
use Illuminate\Support\Facades\DB;
use Illuminate\View\View;
class UserController extends Controller
{
/**
* Показать список всех пользователей приложения.
*/
public function index(): View
{
$users = DB::table('users')->get();
return view('user.index', ['users' => $users]);
}
}
Метод get возвращает экземпляр Illuminate\Support\Collection, содержащий результаты запроса, где каждый результат является экземпляром объекта stdClass PHP. Вы можете получить доступ к значению каждого столбца, обратившись к столбцу как к свойству объекта:
use Illuminate\Support\Facades\DB;
$users = DB::table('users')->get();
foreach ($users as $user) {
echo $user->name;
}
Коллекции Laravel содержат множество чрезвычайно мощных методов для работы с наборами данных. Для получения дополнительной информации о коллекциях Laravel ознакомьтесь с их документацией.
Получение одной строки / столбца из таблицы
Если вам просто нужно получить одну строку из таблицы базы данных, вы можете использовать метод first фасада DB. Этот метод вернет единственный объект stdClass:
$user = DB::table('users')->where('name', 'John')->first();
return $user->email;
Если вы хотите получить одну строку из таблицы базы данных, но получаете Illuminate\Database\RecordNotFoundException, если соответствующая строка не найдена, вы можете использовать метод firstOrFail. Если RecordNotFoundException не перехвачен, HTTP-ответ 404 автоматически отправляется обратно клиенту:
$user = DB::table('users')->where('name', 'John')->firstOrFail();
Если вам не нужна вся строка, вы можете извлечь одно значение из записи с помощью метода value. Этот метод вернет значение столбца напрямую:
$email = DB::table('users')->where('name', 'John')->value('email');
Чтобы получить одну строку по значению столбца id, используйте метод find:
$user = DB::table('users')->find(3);
Получение списка значений столбца
Если вы хотите получить экземпляр Illuminate\Support\Collection, содержащий значения одного столбца, вы можете использовать метод pluck. В этом примере мы получим коллекцию из названий пользователей:
use Illuminate\Support\Facades\DB;
$titles = DB::table('users')->pluck('title');
foreach ($titles as $title) {
echo $title;
}
Вы можете указать столбец, который результирующая коллекция должна использовать в качестве ключей, указав второй аргумент методу pluck:
$titles = DB::table('users')->pluck('title', 'name');
foreach ($titles as $name => $title) {
echo $title;
}
Разбиение результатов
Если вам нужно работать с тысячами записей базы данных, рассмотрите возможность использования метода chunk фасада DB. Этот метод извлекает за раз небольшой фрагмент результатов и передает каждый фрагмент в функцию-аргумент для обработки. Например, давайте извлечем всю таблицу users фрагментами по 100 записей за раз:
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
DB::table('users')->orderBy('id')->chunk(100, function (Collection $users) {
foreach ($users as $user) {
// ...
}
});
Вы можете остановить обработку последующих фрагментов, вернув из функции обработки false:
DB::table('users')->orderBy('id')->chunk(100, function (Collection $users) {
// Обрабатываем записи...
return false;
});
Если вы обновляете записи базы данных во время фрагментирования результатов, то результаты ваших фрагментов могут измениться неожиданным образом. Если вы планируете обновлять полученные записи при фрагментировании, всегда лучше использовать вместо этого метод chunkById. Этот метод автоматически разбивает результаты на фрагменты на основе первичного ключа записи:
DB::table('users')->where('active', false)
->chunkById(100, function (Collection $users) {
foreach ($users as $user) {
DB::table('users')
->where('id', $user->id)
->update(['active' => true]);
}
});
Поскольку методы chunkById и lazyById добавляют свои собственные условия “where” к выполняемому запросу, вам обычно следует логически группировать свои собственные условия внутри замыкания:
DB::table('users')->where(function ($query) {
$query->where('credits', 1)->orWhere('credits', 2);
})->chunkById(100, function (Collection $users) {
foreach ($users as $user) {
DB::table('users')
->where('id', $user->id)
->update(['credits' => 3]);
}
});
При обновлении или удалении записей внутри функции-аргумента, любые изменения первичного или внешних ключей могут повлиять на запрос очередного фрагмента. Это может потенциально привести к тому, что записи могут не быть включены в последующие результаты выполнения функции.
Отложенная потоковая передача результатов
Метод lazy работает аналогично методу chunk в том смысле, что он выполняет запрос по частям. Однако вместо передачи каждого фрагмента непосредственно в функцию-обработчик, метод lazy() возвращает экземпляр LazyCollection, что позволяет вам взаимодействовать с результатами как с единым потоком:
use Illuminate\Support\Facades\DB;
DB::table('users')->orderBy('id')->lazy()->each(function (object $user) {
// ...
});
Еще раз, если вы планируете обновлять полученные записи во время их итерации, лучше вместо этого использовать методы lazyById или lazyByIdDesc. Эти методы автоматически разбивают результаты «постранично» на основе первичного ключа записи:
DB::table('users')->where('active', false)
->lazyById()->each(function (object $user) {
DB::table('users')
->where('id', $user->id)
->update(['active' => true]);
});
При обновлении или удалении записей во время их итерации любые изменения первичного ключа или внешних ключей могут повлиять на запрос фрагмента. Это может потенциально привести к тому, что записи не будут включены в результирующий набор.
Агрегатные функции
Построитель запросов также содержит множество методов для получения агрегированных значений, таких как count, max, min, avg, и sum. После создания запроса вы можете вызвать любой из этих методов:
use Illuminate\Support\Facades\DB;
$users = DB::table('users')->count();
$price = DB::table('orders')->max('price');
Конечно, вы можете комбинировать эти методы с другими выражениями, чтобы уточнить способ вычисления вашего совокупного значения:
$price = DB::table('orders')
->where('finalized', 1)
->avg('price');
Определение наличия записей
Вместо использования метода count для определения существования каких-либо записей, соответствующих ограничениям вашего запроса, используйте методы exists и doesntExist:
if (DB::table('orders')->where('finalized', 1)->exists()) {
// ...
}
if (DB::table('orders')->where('finalized', 1)->doesntExist()) {
// ...
}
Выражения Select
Уточнения выражения Select
Возможно, вам не всегда нужно выбирать все столбцы из таблицы базы данных. Используя метод select, вы можете указать собственное выражение SELECT для запроса:
use Illuminate\Support\Facades\DB;
$users = DB::table('users')
->select('name', 'email as user_email')
->get();
Метод distinct позволяет вам заставить запрос возвращать уникальные результаты:
$users = DB::table('users')->distinct()->get();
Если у вас уже есть экземпляр построителя запросов, и вы хотите добавить столбец к существующему выражению SELECT, то вы можете использовать метод addSelect:
$query = DB::table('users')->select('name');
$users = $query->addSelect('age')->get();
Сырые SQL-выражения
Иногда вам может понадобиться вставить в запрос произвольную строку, содержащую часть SQL-запроса. Для этого вы можете использовать метод raw фасада DB:
$users = DB::table('users')
->select(DB::raw('count(*) as user_count, status'))
->where('status', '<>', 1)
->groupBy('status')
->get();
Сырые выражения будут вставлены в запрос в виде строк, поэтому следует проявлять особую осторожность, чтобы не создавать уязвимости для SQL-инъекций.
Сырые sql-выражения
Вместо использования метода DB::raw, вы также можете использовать следующие методы для вставки произвольного SQL-выражения в различные части вашего запроса. Помните, Laravel не может гарантировать, что любой запрос, использующий сырые SQL-выражения, защищен от уязвимостей SQL-инъекций.
selectRaw
Метод selectRaw можно использовать вместо addSelect(DB::raw(/* ... */)). Этот метод принимает необязательный массив параметров для подстановки в качестве второго аргумента:
$orders = DB::table('orders')
->selectRaw('price * ? as price_with_tax', [1.0825])
->get();
whereRaw / orWhereRaw
Методы whereRaw и orWhereRaw можно использовать для вставки сырого SQL-выражения WHERE в ваш запрос. Эти методы принимают необязательный массив параметров в качестве второго аргумента:
$orders = DB::table('orders')
->whereRaw('price > IF(state = "TX", ?, 100)', [200])
->get();
havingRaw / orHavingRaw
Методы havingRaw и orHavingRaw могут использоваться для вставки необработанной строки в качестве значения выражения HAVING. Эти методы принимают необязательный массив параметров в качестве второго аргумента:
$orders = DB::table('orders')
->select('department', DB::raw('SUM(price) as total_sales'))
->groupBy('department')
->havingRaw('SUM(price) > ?', [2500])
->get();
orderByRaw
Метод orderByRaw используется для предоставления необработанной строки в качестве значения выражения ORDER BY:
$orders = DB::table('orders')
->orderByRaw('updated_at - created_at DESC')
->get();
groupByRaw
Метод groupByRaw используется для предоставления необработанной строки в качестве значения выражения GROUP BY:
$orders = DB::table('orders')
->select('city', 'state')
->groupByRaw('city, state')
->get();
Соединения Joins
Inner Join
Построитель запросов также может использоваться для добавления выражений соединения (join) к вашим запросам. Чтобы выполнить базовое «внутреннее соединение» (inner join), вы можете использовать метод join. Первым аргументом, передаваемым методу join, является имя таблицы, к которой вам нужно присоединиться, а остальные аргументы определяют ограничения столбца для соединения. Вы даже можете соединить несколько таблиц в один запрос:
use Illuminate\Support\Facades\DB;
$users = DB::table('users')
->join('contacts', 'users.id', '=', 'contacts.user_id')
->join('orders', 'users.id', '=', 'orders.user_id')
->select('users.*', 'contacts.phone', 'orders.price')
->get();
Left Join / Right Join
Если вы хотите выполнить «левое соединение» или «правое соединение» вместо «внутреннего соединения», используйте методы leftJoin или rightJoin. Эти методы имеют ту же сигнатуру, что и метод join:
$users = DB::table('users')
->leftJoin('posts', 'users.id', '=', 'posts.user_id')
->get();
$users = DB::table('users')
->rightJoin('posts', 'users.id', '=', 'posts.user_id')
->get();
Cross Join
Вы можете использовать метод crossJoin для выполнения «перекрестного соединения». Перекрестные соединения генерируют декартово произведение между первой таблицей и соединяемой таблицей:
$sizes = DB::table('sizes')
->crossJoin('colors')
->get();
Расширенные выражения соединения
Вы также можете указать более сложные выражения соединения. Для начала передайте функцию в качестве второго аргумента методу join. Функция получит экземпляр Illuminate\Database\Query\JoinClause, который позволяет вам указать ограничения JOIN:
DB::table('users')
->join('contacts', function (JoinClause $join) {
$join->on('users.id', '=', 'contacts.user_id')->orOn(/* ... */);
})
->get();
Если вы хотите использовать выражение WHERE в своих соединениях, вы можете использовать методы where и orWhere экземпляра JoinClause. Вместо сравнения двух столбцов эти методы будут сравнивать столбец со значением:
DB::table('users')
->join('contacts', function (JoinClause $join) {
$join->on('users.id', '=', 'contacts.user_id')
->where('contacts.user_id', '>', 5);
})
->get();
Подзапросы соединений
Вы можете использовать методы joinSub, leftJoinSub, и rightJoinSub, чтобы присоединить запрос к подзапросу. Каждый из этих методов получает три аргумента: подзапрос, псевдоним таблицы и функцию, определяющую связанные столбцы. В этом примере мы получим коллекцию пользователей, где каждая запись пользователя также содержит временную метку created_at последнего опубликованного поста пользователя в блоге:
$latestPosts = DB::table('posts')
->select('user_id', DB::raw('MAX(created_at) as last_post_created_at'))
->where('is_published', true)
->groupBy('user_id');
$users = DB::table('users')
->joinSub($latestPosts, 'latest_posts', function (JoinClause $join) {
$join->on('users.id', '=', 'latest_posts.user_id');
})->get();
Боковые соединения (Lateral Joins)
Боковые соединения в настоящее время поддерживаются PostgreSQL, MySQL >= 8.0.14 и SQL Server.
Вы можете использовать методы joinLateral и leftJoinLateral для выполнения “бокового соединения” с подзапросом. Каждый из этих методов принимает два аргумента: подзапрос и его псевдоним таблицы. Условие(я) соединения должно быть указано в where выражении данного подзапроса. Боковые соединения оцениваются для каждой строки и могут ссылаться на столбцы вне подзапроса.
В этом примере мы получим коллекцию пользователей, а также три последних блог-поста пользователя. Для каждого пользователя может быть до трех строк в наборе результатов: по одной для каждого из его последних блог-постов. Условие соединения указывается с помощью whereColumn выражения внутри подзапроса, ссылаясь на текущую строку пользователя:
$latestPosts = DB::table('posts')
->select('id as post_id', 'title as post_title', 'created_at as post_created_at')
->whereColumn('user_id', 'users.id')
->orderBy('created_at', 'desc')
->limit(3);
$users = DB::table('users')
->joinLateral($latestPosts, 'latest_posts')
->get();
Объединения результатов Unions
Построитель запросов также содержит удобный метод «объединения» двух или более запросов вместе. Например, вы можете создать первый запрос и использовать метод union для объединения его с другими запросами:
use Illuminate\Support\Facades\DB;
$first = DB::table('users')
->whereNull('first_name');
$users = DB::table('users')
->whereNull('last_name')
->union($first)
->get();
В дополнение к методу union, построитель запросов содержит метод unionAll. Запросы, объединенные с использованием метода unionAll, не будут удалять повторяющиеся результаты. Метод unionAll имеет ту же сигнатуру, что и метод union.
Основные выражения Where
Выражения Where
Вы можете использовать метод where построителя запросов, чтобы добавить в запрос выражения WHERE. Самый простой вызов метода where требует трех аргументов. Первый аргумент – это имя столбца. Второй аргумент – это оператор, который может быть любым из поддерживаемых базой данных операторов. Третий аргумент – это значение, которое нужно сравнить со значением столбца.
Например, следующий запрос извлекает пользователей, у которых значение столбца votes равно 100, а значение столбца age больше, чем 35:
$users = DB::table('users')
->where('votes', '=', 100)
->where('age', '>', 35)
->get();
Для удобства, если вы хотите убедиться, что столбец соответствует = переданному значению, то вы можете передать это значение в качестве второго аргумента в метод where. Laravel будет предполагать, что вы хотите использовать оператор =:
$users = DB::table('users')->where('votes', 100)->get();
Как упоминалось ранее, вы можете использовать любой оператор, который поддерживается вашей системой баз данных:
$users = DB::table('users')
->where('votes', '>=', 100)
->get();
$users = DB::table('users')
->where('votes', '<>', 100)
->get();
$users = DB::table('users')
->where('name', 'like', 'T%')
->get();
Вы также можете передать массив условий методу where. Каждый элемент массива должен быть массивом, содержащим три аргумента, как и обычно передаваемых методу where:
$users = DB::table('users')->where([
['status', '=', '1'],
['subscribed', '<>', '1'],
])->get();
PDO не поддерживает привязку имен столбцов. Поэтому вы никогда не должны брать из пользовательского ввода имена столбцов для совершения запросов, включая столбцы “order by”.
MySQL и MariaDB автоматически преобразуют строки в целые числа при сравнении чисел-строк. В этом процессе нечисловые строки преобразуются в
0, что может привести к неожиданным результатам. Например, если в вашей таблице есть столбецsecretсо значениемaaaи вы запускаетеUser::where('secret', 0), будет возвращена эта строка. Чтобы избежать этого, убедитесь, что все значения приведены к соответствующим типам, прежде чем использовать их в запросах.
Выражения Or Where
При объединении в цепочку вызовов метода where построителя запросов выражения WHERE будут объединены вместе с помощью оператора AND. Однако, вы можете использовать метод orWhere для добавления выражения к запросу с помощью оператора OR. Метод orWhere принимает те же аргументы, что и метод where:
$users = DB::table('users')
->where('votes', '>', 100)
->orWhere('name', 'John')
->get();
Если вам нужно сгруппировать условие OR в круглых скобках, вы можете передать функцию в качестве первого аргумента методу orWhere:
$users = DB::table('users')
->where('votes', '>', 100)
->orWhere(function (Builder $query) {
$query->where('name', 'Abigail')
->where('votes', '>', 50);
})
->get();
В приведенном выше примере будет получен следующий SQL:
select * from users where votes > 100 or (name = 'Abigail' and votes > 50)
Вы всегда должны группировать вызовы
orWhere, чтобы избежать неожиданного поведения при применении глобальных диапазонов.
Выражение Where Not
Методы whereNot и orWhereNot могут использоваться для отрицания заданной группы ограничений запроса. Например, в следующем запросе исключаются товары, находящиеся на распродаже или имеющие цену менее десяти:
$products = DB::table('products')
->whereNot(function (Builder $query) {
$query->where('clearance', true)
->orWhere('price', '<', 10);
})
->get();
Выражения Where Any / All / None
Иногда вам может понадобиться применить одни и те же условия к нескольким столбцам запроса. Например, вы можете хотеть выбрать все записи, где хотя бы один столбец из списка соответствует определенному значению. Это можно сделать с помощью метода whereAny:
$users = DB::table('users')
->where('active', true)
->whereAny([
'name',
'email',
'phone',
], 'like', 'Example%')
->get();
Запрос выше приведет к следующему SQL:
SELECT *
FROM users
WHERE active = true AND (
name LIKE 'Example%' OR
email LIKE 'Example%' OR
phone LIKE 'Example%'
)
Аналогично метод whereAll может быть использован для извлечения записей, где все указанные столбцы соответствуют заданному условию:
$posts = DB::table('posts')
->where('published', true)
->whereAll([
'title',
'content',
], 'like', '%Laravel%')
->get();
Запрос выше приведет к следующему SQL:
SELECT *
FROM posts
WHERE published = true AND (
title LIKE '%Laravel%' AND
content LIKE '%Laravel%'
)
Метод whereNone можно использовать для извлечения записей, в которых ни один из заданных столбцов не соответствует заданному ограничению:
$posts = DB::table('albums')
->where('published', true)
->whereNone([
'title',
'lyrics',
'tags',
], 'like', '%explicit%')
->get();
Результатом приведенного выше запроса будет следующий SQL:
SELECT *
FROM albums
WHERE published = true AND NOT (
title LIKE '%explicit%' OR
lyrics LIKE '%explicit%' OR
tags LIKE '%explicit%'
)
Выражения Where и JSON
Laravel также поддерживает запросы к типам столбцов JSON в базах данных, которые предоставляют поддержку для типов столбцов JSON. В настоящее время это включает MariaDB 10.3+, MySQL 8.0+, PostgreSQL 12.0+, SQL Server 2017+ и SQLite 3.39.0. Для выполнения запроса к столбцу JSON используйте оператор ->:
$users = DB::table('users')
->where('preferences->dining->meal', 'salad')
->get();
Вы можете использовать whereJsonContains для запроса массивов JSON.
$users = DB::table('users')
->whereJsonContains('options->languages', 'en')
->get();
Если ваше приложение использует базы данных MariaDB, MySQL или PostgreSQL, вы можете передать массив значений методу whereJsonContains:
$users = DB::table('users')
->whereJsonContains('options->languages', ['en', 'de'])
->get();
Вы можете использовать метод whereJsonLength для запроса массивов JSON по их длине:
$users = DB::table('users')
->whereJsonLength('options->languages', 0)
->get();
$users = DB::table('users')
->whereJsonLength('options->languages', '>', 1)
->get();
Дополнительные выражения Where
whereLike / orWhereLike / whereNotLike / orWhereNotLike
Метод whereLike позволяет добавлять в запрос предложения “LIKE” для сопоставления с образцом. Эти методы обеспечивают независимый от базы данных способ выполнения запросов на сопоставление строк с возможностью переключения чувствительности к регистру. По умолчанию сопоставление строк не учитывает регистр:
$users = DB::table('users')
->whereLike('name', '%John%')
->get();
Вы можете включить поиск с учетом регистра с помощью аргумента caseSensitive:
$users = DB::table('users')
->whereLike('name', '%John%', caseSensitive: true)
->get();
Метод orWhereLike позволяет добавить предложение “or” с условием LIKE:
$users = DB::table('users')
->where('votes', '>', 100)
->orWhereLike('name', '%John%')
->get();
Метод whereNotLike позволяет добавлять в запрос предложения “NOT LIKE”:
$users = DB::table('users')
->whereNotLike('name', '%John%')
->get();
Аналогичным образом вы можете использовать orWhereNotLike для добавления предложения “or” с условием NOT LIKE:
$users = DB::table('users')
->where('votes', '>', 100)
->orWhereNotLike('name', '%John%')
->get();
Параметр поиска
whereLikeс учетом регистра в настоящее время не поддерживается на SQL Server.
whereIn / whereNotIn / orWhereIn / orWhereNotIn
Метод whereIn проверяет, что значение переданного столбца содержится в указанном массиве:
$users = DB::table('users')
->whereIn('id', [1, 2, 3])
->get();
Метод whereNotIn проверяет, что значение переданного столбца не содержится в указанном массиве:
$users = DB::table('users')
->whereNotIn('id', [1, 2, 3])
->get();
Вы также можете использовать объект запроса в качестве второго аргумента метода whereIn:
$activeUsers = DB::table('users')->select('id')->where('is_active', 1);
$users = DB::table('comments')
->whereIn('user_id', $activeUsers)
->get();
Приведенный выше пример создаст следующий SQL-запрос:
select * from comments where user_id in (
select id
from users
where is_active = 1
)
Если вы добавляете в свой запрос большой массив связываемых целочисленных параметров, то методы
whereIntegerInRawилиwhereIntegerNotInRawмогут использоваться для значительного сокращения потребляемой памяти.
whereBetween / orWhereBetween
Метод whereBetween проверяет, что значение столбца находится между двумя значениями:
$users = DB::table('users')
->whereBetween('votes', [1, 100])
->get();
whereNotBetween / orWhereNotBetween
Метод whereNotBetween проверяет, что значение столбца находится за пределами двух значений:
$users = DB::table('users')
->whereNotBetween('votes', [1, 100])
->get();
whereBetweenColumns / whereNotBetweenColumns / orWhereBetweenColumns / orWhereNotBetweenColumns
Метод whereBetweenColumns проверяет, что значение столбца находится между двумя значениями двух столбцов в одной строке таблицы:
$patients = DB::table('patients')
->whereBetweenColumns('weight', ['minimum_allowed_weight', 'maximum_allowed_weight'])
->get();
Метод whereNotBetweenColumns проверяет, что значение столбца находится за пределами двух значений двух столбцов в одной строке таблицы:
$patients = DB::table('patients')
->whereNotBetweenColumns('weight', ['minimum_allowed_weight', 'maximum_allowed_weight'])
->get();
whereNull / whereNotNull / orWhereNull / orWhereNotNull
Метод whereNull проверяет, что значение переданного столбца равно NULL:
$users = DB::table('users')
->whereNull('updated_at')
->get();
Метод whereNotNull проверяет, что значение переданного столбца не равно NULL:
$users = DB::table('users')
->whereNotNull('updated_at')
->get();
whereDate / whereMonth / whereDay / whereYear / whereTime
Метод whereDate используется для сравнения значения столбца с датой:
$users = DB::table('users')
->whereDate('created_at', '2016-12-31')
->get();
Метод whereMonth используется для сравнения значения столбца с конкретным месяцем:
$users = DB::table('users')
->whereMonth('created_at', '12')
->get();
Метод whereDay используется для сравнения значения столбца с определенным днем месяца:
$users = DB::table('users')
->whereDay('created_at', '31')
->get();
Метод whereYear используется для сравнения значения столбца с конкретным годом:
$users = DB::table('users')
->whereYear('created_at', '2016')
->get();
Метод whereTime используется для сравнения значения столбца с определенным временем:
$users = DB::table('users')
->whereTime('created_at', '=', '11:20:45')
->get();
whereColumn / orWhereColumn
Метод whereColumn используется для проверки равенства двух столбцов:
$users = DB::table('users')
->whereColumn('first_name', 'last_name')
->get();
Вы также можете передать оператор сравнения методу whereColumn:
$users = DB::table('users')
->whereColumn('updated_at', '>', 'created_at')
->get();
Вы также можете передать массив сравнений столбцов методу whereColumn. Эти условия будут объединены с помощью оператора AND:
$users = DB::table('users')
->whereColumn([
['first_name', '=', 'last_name'],
['updated_at', '>', 'created_at'],
])->get();
Логическая группировка
Иногда требуется сгруппировать несколько выражений WHERE в круглых скобках, чтобы добиться желаемой логической группировки вашего запроса. Фактически, вы должны всегда группировать вызовы метода orWhere в круглых скобках, чтобы избежать неожиданного поведения запроса. Для этого вы можете передать функцию методу where:
$users = DB::table('users')
->where('name', '=', 'John')
->where(function (Builder $query) {
$query->where('votes', '>', 100)
->orWhere('title', '=', 'Admin');
})
->get();
Как вы можете видеть, передача функции в метод where инструктирует построитель запросов начать группу ограничений. Функция получит экземпляр построителя запросов, который вы можете использовать для задания ограничений, которые должны содержаться в группе скобок. В приведенном выше примере будет получен следующий SQL:
select * from users where name = 'John' and (votes > 100 or title = 'Admin')
Вы всегда должны группировать вызовы
orWhere, чтобы избежать неожиданного поведения при применении глобальных диапазонов.
Расширенные выражения Where
Выражения Where Exists
Метод whereExists позволяет писать выражения WHERE EXISTS SQL. Метод whereExists принимает функцию, которая получит экземпляр построителя запросов, позволяя вам определить запрос, который должен быть помещен внутри выражения EXISTS:
$users = DB::table('users')
->whereExists(function (Builder $query) {
$query->select(DB::raw(1))
->from('orders')
->whereColumn('orders.user_id', 'users.id');
})
->get();
Кроме того, вы можете предоставить объект запроса методу whereExists вместо замыкания:
$orders = DB::table('orders')
->select(DB::raw(1))
->whereColumn('orders.user_id', 'users.id');
$users = DB::table('users')
->whereExists($orders)
->get();
Оба приведенных выше примера создадут следующий SQL-запрос:
select * from users
where exists (
select 1
from orders
where orders.user_id = users.id
)
Подзапросы выражений Where
Иногда требуется создать выражение WHERE, которое сравнивает результаты подзапроса с переданным значением. Вы можете добиться этого, передав функцию и значение методу where. Например, следующий запрос будет извлекать всех пользователей, недавно имевших «членство» указанного типа:
use App\Models\User;
use Illuminate\Database\Query\Builder;
$users = User::where(function (Builder $query) {
$query->select('type')
->from('membership')
->whereColumn('membership.user_id', 'users.id')
->orderByDesc('membership.start_date')
->limit(1);
}, 'Pro')->get();
Или вам может потребоваться создать выражение “where”, которое сравнивает столбец с результатами подзапроса. Вы можете сделать это, передав методу where столбец, оператор и функцию. Например, следующий запрос будет извлекать все записи о доходах, где сумма меньше средней:
use App\Models\Income;
use Illuminate\Database\Query\Builder;
$incomes = Income::where('amount', '<', function (Builder $query) {
$query->selectRaw('avg(i.amount)')->from('incomes as i');
})->get();
Полнотекстовый поиск
Полнотекстовый поиск поддерживаются в настоящее время для MariaDB, MySQL и PostgreSQL.
Методы whereFullText и orWhereFullText позволяют добавлять полнотекстовые “условия” в запрос для столбцов, имеющих полнотекстовые индексы. Laravel автоматически преобразует эти методы в соответствующий SQL-код для используемой базы данных. Например, для приложений, использующих MariaDB или MySQL, будет сгенерировано условие MATCH AGAINST:
$users = DB::table('users')
->whereFullText('bio', 'web developer')
->get();
Сортировка, группировка, ограничение и смещение
Сортировка
Метод orderBy
Метод orderBy позволяет вам сортировать результаты запроса по конкретному столбцу. Первый аргумент, принимаемый методом orderBy, должен быть столбцом, по которому вы хотите выполнить сортировку, а второй аргумент определяет направление сортировки и может быть либо asc, либо desc:
$users = DB::table('users')
->orderBy('name', 'desc')
->get();
Для сортировки по нескольким столбцам вы можете просто вызывать orderBy столько раз, сколько необходимо:
$users = DB::table('users')
->orderBy('name', 'desc')
->orderBy('email', 'asc')
->get();
Методы latest и oldest
Методы latest и oldest позволяют легко упорядочивать результаты по дате. По умолчанию результат будет упорядочен по столбцу created_at таблицы. Или вы можете передать имя столбца, по которому хотите сортировать:
$user = DB::table('users')
->latest()
->first();
Случайный порядок
Метод inRandomOrder используется для случайной сортировки результатов запроса. Например, вы можете использовать этот метод для выборки случайного пользователя:
$randomUser = DB::table('users')
->inRandomOrder()
->first();
Удаление существующих сортировок
Метод reorder удаляет все выражения ORDER BY, которые ранее были применены к запросу:
$query = DB::table('users')->orderBy('name');
$unorderedUsers = $query->reorder()->get();
Вы можете передать столбец и направление при вызове метода reorder, чтобы удалить все существующие выражения ORDER BY и применить к запросу совершенно новый порядок:
$query = DB::table('users')->orderBy('name');
$usersOrderedByEmail = $query->reorder('email', 'desc')->get();
Группировка
Методы groupBy и having
Как и следовало ожидать, для группировки результатов запроса могут использоваться методы groupBy и having. Сигнатура метода having аналогична сигнатуре метода where:
$users = DB::table('users')
->groupBy('account_id')
->having('account_id', '>', 100)
->get();
Вы можете использовать метод havingBetween для фильтрации результатов в заданном диапазоне:
$report = DB::table('orders')
->selectRaw('count(id) as number_of_orders, customer_id')
->groupBy('customer_id')
->havingBetween('number_of_orders', [5, 15])
->get();
Вы можете передать несколько аргументов методу groupBy для группировки по нескольким столбцам:
$users = DB::table('users')
->groupBy('first_name', 'status')
->having('account_id', '>', 100)
->get();
Чтобы создать более сложные операторы having, см. метод havingRaw.
Ограничение и смещение
Методы skip и take
Вы можете использовать методы skip и take, чтобы ограничить количество результатов, возвращаемых запросом, или пропустить указанное количество результатов из запроса:
$users = DB::table('users')->skip(10)->take(5)->get();
Как вариант, вы можете использовать методы limit и offset. Эти методы функционально эквивалентны методам take и skip соответственно:
$users = DB::table('users')
->offset(10)
->limit(5)
->get();
Условные выражения
Иногда может потребоваться, чтобы определенные выражения запроса применялись к запросу на основании другого условия. Например, бывает необходимо применить оператор WHERE только в том случае, если переданное входящее значение присутствует в HTTP-запросе. Вы можете сделать это с помощью метода when:
$role = $request->input('role');
$users = DB::table('users')
->when($role, function (Builder $query, string $role) {
$query->where('role_id', $role);
})
->get();
Метод when выполняет переданную функцию-аргумент только тогда, когда первый аргумент равен true. Если первый аргумент – false, функция не будет выполнена. Итак, в приведенном выше примере функция метода when будет вызываться только в том случае, если поле role присутствует во входящем запросе и оценивается как true.
Вы можете передать другую функцию в качестве третьего аргумента методу when. Это функция будет выполнена только в том случае, если первый аргумент оценивается как false. Чтобы проиллюстрировать этот функционал, определим порядок вывода записей по умолчанию для запроса:
$sortByVotes = $request->boolean('sort_by_votes');
$users = DB::table('users')
->when($sortByVotes, function (Builder $query, bool $sortByVotes) {
$query->orderBy('votes');
}, function (Builder $query) {
$query->orderBy('name');
})
->get();
Вставка
Построитель запросов также содержит метод insert, который можно использовать для вставки записей в таблицу базы данных. Метод insert принимает массив имен и значений столбцов:
DB::table('users')->insert([
'email' => 'kayla@example.com',
'votes' => 0
]);
Вы можете вставить сразу несколько записей, передав массив массивов. Каждый из массивов представляет собой запись, которую нужно вставить в таблицу:
DB::table('users')->insert([
['email' => 'picard@example.com', 'votes' => 0],
['email' => 'janeway@example.com', 'votes' => 0],
]);
Метод insertOrIgnore позволяет игнорировать ошибки при вставке записей в базу данных. При использовании этого метода следует помнить, что ошибки дублирования записей будут проигнорированы, и другие виды ошибок также могут быть проигнорированы в зависимости от используемой базы данных. Например, insertOrIgnore пропускает строгий режим MySQL:
DB::table('users')->insertOrIgnore([
['id' => 1, 'email' => 'sisko@example.com'],
['id' => 2, 'email' => 'archer@example.com'],
]);
Метод insertUsing вставляет новые записи в таблицу, используя подзапрос для определения данных, которые должны быть вставлены:
DB::table('pruned_users')->insertUsing([
'id', 'name', 'email', 'email_verified_at'
], DB::table('users')->select(
'id', 'name', 'email', 'email_verified_at'
)->where('updated_at', '<=', now()->subMonth()));
Автоинкрементирование идентификаторов
Если таблица имеет автоинкрементный идентификатор, то используйте метод insertGetId, чтобы вставить запись и затем получить идентификатор этой записи:
$id = DB::table('users')->insertGetId(
['email' => 'john@example.com', 'votes' => 0]
);
При использовании PostgreSQL метод
insertGetIdожидает, что автоинкрементный столбец будет называтьсяid. Если вы хотите получить идентификатор из другой «последовательности», вы можете передать имя столбца в качестве второго параметра методуinsertGetId.
Обновления-вставки
Метод upsert вставляет записи, которые не существуют, и обновляет записи, которые уже существуют, новыми значениями, которые вы можете указать. Первый аргумент метода состоит из значений для вставки или обновления, а второй аргумент перечисляет столбцы, которые однозначно идентифицируют записи в связанной таблице. Третий и последний аргумент метода – это массив столбцов, который следует обновить, если соответствующая запись уже существует в базе данных:
DB::table('flights')->upsert(
[
['departure' => 'Oakland', 'destination' => 'San Diego', 'price' => 99],
['departure' => 'Chicago', 'destination' => 'New York', 'price' => 150]
],
['departure', 'destination'],
['price']
);
В приведенном выше примере Laravel попытается вставить две записи. Если запись уже существует с такими же значениями столбцов departure и destination, то Laravel обновит столбец price этой записи.
Все базы данных, кроме SQL Server, требуют, чтобы столбцы во втором аргументе метода
upsertимели «первичный» или «уникальный» индекс. Вдобавок, драйверы базы данных MariaDB и MySQL игнорирует второй аргумент методаupsertи всегда использует «первичный» и «уникальный» индексы таблицы для обнаружения существующих записей.
Обновление
Помимо вставки записей в базу данных, построитель запросов также может обновлять существующие записи с помощью метода update. Метод update, как и метод insert, принимает массив пар столбцов и значений, указывающих столбцы, которые нужно обновить. Вы можете ограничить запрос update с помощью выражений WHERE:
$affected = DB::table('users')
->where('id', 1)
->update(['votes' => 1]);
Обновление или вставка
Иногда требуется обновить существующую запись в базе данных или создать ее, если соответствующей записи не существует. В этом сценарии может использоваться метод updateOrInsert. Метод updateOrInsert принимает два аргумента: массив условий, по которым нужно найти запись, и массив пар столбцов и значений, указывающих столбцы, которые нужно обновить.
Метод updateOrInsert попытается найти соответствующую запись в базе данных, используя пары столбец и значение первого аргумента. Если запись существует, она будет обновлена значениями второго аргумента. Если запись не может быть найдена, будет вставлена новая запись с объединенными атрибутами обоих аргументов:
DB::table('users')
->updateOrInsert(
['email' => 'john@example.com', 'name' => 'John'],
['votes' => '2']
);
Вы можете предоставить закрытие метода updateOrInsert, чтобы настроить атрибуты, которые обновляются или вставляются в базу данных на основе существования соответствующей записи:
DB::table('users')->updateOrInsert(
['user_id' => $user_id],
fn ($exists) => $exists ? [
'name' => $data['name'],
'email' => $data['email'],
] : [
'name' => $data['name'],
'email' => $data['email'],
'marketable' => true,
],
);
Обновление столбцов JSON
При обновлении столбца JSON вы должны использовать синтаксис -> для обновления соответствующего ключа в объекте JSON. Эта операция поддерживается в MariaDB 10.3+, MySQL 5.7+ и PostgreSQL 9.5+:
$affected = DB::table('users')
->where('id', 1)
->update(['options->enabled' => true]);
Увеличение и уменьшение отдельных значений
Конструктор запросов также содержит удобные методы увеличения или уменьшения значения конкретного столбца. Оба метода принимают по крайней мере один аргумент: столбец, который нужно изменить. Может быть указан второй аргумент, определяющий величину, на которую следует увеличить или уменьшить столбец:
DB::table('users')->increment('votes');
DB::table('users')->increment('votes', 5);
DB::table('users')->decrement('votes');
DB::table('users')->decrement('votes', 5);
При необходимости вы также можете указать дополнительные столбцы для обновления во время операции увеличения или уменьшения:
DB::table('users')->increment('votes', 1, ['name' => 'John']);
Кроме того, вы можете одновременно увеличивать или уменьшать значения нескольких столбцов с помощью методов incrementEach и decrementEach:
DB::table('users')->incrementEach([
'votes' => 5,
'balance' => 100,
]);
Удаление
Метод delete может использоваться для удаления записей из таблицы. Он возвращает количество затронутых строк. Вы можете ограничить операторы delete, добавив метод where перед вызовом метода delete:
$deleted = DB::table('users')->delete();
$deleted = DB::table('users')->where('votes', '>', 100)->delete();
Если вы хотите очистить всю таблицу, что приведет к удалению всех записей из таблицы и сбросу автоинкрементного идентификатора на ноль, вы можете использовать метод truncate:
DB::table('users')->truncate();
Очистка таблицы и PostgreSQL
При очистке базы данных PostgreSQL будет применено поведение CASCADE. Это означает, что все связанные с внешним ключом записи в других таблицах также будут удалены.
Пессимистическая блокировка
Построитель запросов также включает несколько функций, которые помогут вам достичь «пессимистической блокировки» при выполнении ваших операторов SELECT. Чтобы выполнить оператор с «совместной блокировкой», вы можете вызвать метод sharedLock в запросе. Совместная блокировка предотвращает изменение выбранных строк до тех пор, пока ваша транзакция не будет зафиксирована:
DB::table('users')
->where('votes', '>', 100)
->sharedLock()
->get();
В качестве альтернативы вы можете использовать метод lockForUpdate. Блокировка «для обновления» предотвращает изменение выбранных записей или их выбор с помощью другой совместной блокировки:
DB::table('users')
->where('votes', '>', 100)
->lockForUpdate()
->get();
Отладка
Вы можете использовать методы dd или dump при построении запроса, чтобы отобразить связанные параметры запроса и сам SQL-запрос. Метод dd отобразит отладочную информацию и затем прекратит выполнение запроса. Метод dump отобразит информацию об отладке, но позволит продолжить выполнение запроса:
DB::table('users')->where('votes', '>', 100)->dd();
DB::table('users')->where('votes', '>', 100)->dump();
Методы dumpRawSql и ddRawSql могут быть вызваны для запроса, чтобы вывести SQL-запрос с правильно подставленными параметрами:
DB::table('users')->where('votes', '>', 100)->dumpRawSql();
DB::table('users')->where('votes', '>', 100)->ddRawSql();
