Построитель запросов
- Введение
- Выполнение запросов к базе данных
- Разбиение результатов
- Отложенная потоковая передача результатов
- Агрегатные функции
- Выражения 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;
Если вам не нужна вся строка, вы можете извлечь одно значение из записи с помощью метода 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]);
}
});
При обновлении или удалении записей внутри функции-аргумента, любые изменения первичного или внешних ключей могут повлиять на запрос очередного фрагмента. Это может потенциально привести к тому, что записи могут не быть включены в последующие результаты выполнения функции.
Отложенная потоковая передача результатов
Метод 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”.
Выражения 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;
$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' => '[email protected]',
'votes' => 0
]);
Вы можете вставить сразу несколько записей, передав массив массивов. Каждый из массивов представляет собой запись, которую нужно вставить в таблицу:
DB::table('users')->insert([
['email' => '[email protected]', 'votes' => 0],
['email' => '[email protected]', 'votes' => 0],
]);
Метод insertOrIgnore
позволяет игнорировать ошибки при вставке записей в базу данных. При использовании этого метода следует помнить, что ошибки дублирования записей будут проигнорированы, и другие виды ошибок также могут быть проигнорированы в зависимости от используемой базы данных. Например, insertOrIgnore
пропускает строгий режим MySQL:
DB::table('users')->insertOrIgnore([
['id' => 1, 'email' => '[email protected]'],
['id' => 2, 'email' => '[email protected]'],
]);
Метод 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' => '[email protected]', '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' => '[email protected]', 'name' => 'John'],
['votes' => '2']
);
You may provide a closure to the updateOrInsert
method to customize the attributes that are updated or inserted into the database based on the existence of a matching record:
Вы можете предоставить закрытие метода 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();