База Данных: Query builder
- Введение
- Выполнение запросов к базе данных
- Оператор выборки
- Необработанные выражения
- Joins
- Unions
- Простые выражения Where
- Расширенные выражения Where
- Порядок, группировка, ограничение и смещение
- Выражения условий
- Операторы вставки
- Операторы обновления
- Операторы удаления
- Пессимистическая блокировка
- Отладка
Введение
Конструктор запросов к базе данных Laravel предоставляет удобный и плавный интерфейс для создания и выполнения запросов к базе данных. Его можно использовать для выполнения большинства операций с базами данных в вашем приложении, и он отлично работает со всеми поддерживаемыми системами баз данных Laravel.
Конструктор запросов Laravel использует привязку параметров PDO для защиты вашего приложения от атак путем внедрения SQL. Нет необходимости очищать или дезинфицировать строки, переданные в построитель запросов в качестве привязок запросов.
{note} PDO не поддерживает привязку имен столбцов. Таким образом, вы никогда не должны позволять вводу пользователем диктовать имена столбцов, на которые ссылаются ваши запросы, включая столбцы «упорядочить по».
Выполнение запросов к базе данных
Извлечение всех строк из таблицы
Вы можете использовать метод table, предоставленный фасадом DB, чтобы начать запрос. Метод table возвращает беглый экземпляр построителя запросов для данной таблицы, что позволяет вам связать дополнительные ограничения с запросом, а затем, наконец, получить результаты запроса с помощью метода get:
<?php namespace App\Http\Controllers; use App\Http\Controllers\Controller;use Illuminate\Support\Facades\DB; class UserController extends Controller{ /** * Show a list of all of the application's users. * * @return \Illuminate\Http\Response */ public function index() { $users = DB::table('users')->get(); return view('user.index', ['users' => $users]); }}Метод get возвращает экземпляр Illuminate\Support\Collection, содержащий результаты запроса, где каждый результат является экземпляром объекта PHP stdClass. Вы можете получить доступ к значению каждого столбца, обратившись к столбцу как к свойству объекта:
use Illuminate\Support\Facades\DB; $users = DB::table('users')->get(); foreach ($users as $user) { echo $user->name;}{tip} Коллекции 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\Facades\DB; DB::table('users')->orderBy('id')->chunk(100, function ($users) { foreach ($users as $user) { // }});Вы можете остановить дальнейшую обработку фрагментов, вернув false из замыкания:
DB::table('users')->orderBy('id')->chunk(100, function ($users) { // Process the records... return false;});Если вы обновляете записи базы данных во время разделения результатов на фрагменты, результаты вашего фрагмента могут измениться неожиданным образом. Если вы планируете обновлять извлеченные записи во время фрагментации, всегда лучше вместо этого использовать метод chunkById. Этот метод автоматически разбивает результаты на страницы на основе первичного ключа записи:
DB::table('users')->where('active', false) ->chunkById(100, function ($users) { foreach ($users as $user) { DB::table('users') ->where('id', $user->id) ->update(['active' => true]); } });{note} При обновлении или удалении записей внутри обратного вызова чанка любые изменения первичного ключа или внешних ключей могут повлиять на запрос чанка. Это потенциально может привести к тому, что записи не будут включены в разбивку результатов.
Отложенная потоковая передача результатов
Метод lazy работает аналогично методу chunk в том смысле, что он выполняет запрос частями. Однако вместо того, чтобы передавать каждый фрагмент в колбэк, метод lazy() возвращает LazyCollection, что позволяет вам взаимодействовать с результатами как один поток:
use Illuminate\Support\Facades\DB; DB::table('users')->orderBy('id')->lazy()->each(function ($user) { //});Опять же, если вы планируете обновлять извлеченные записи во время их повторения, лучше вместо этого использовать методы lazyById или lazyByIdDesc. Эти методы автоматически разбивают результаты на страницы на основе первичного ключа записи:
DB::table('users')->where('active', false) ->lazyById()->each(function ($user) { DB::table('users') ->where('id', $user->id) ->update(['active' => true]); });{note} При обновлении или удалении записей во время их итерации любые изменения в первичном ключе или внешних ключах могут повлиять на запрос фрагмента. Это потенциально может привести к тому, что записи не будут включены в результаты.
Агрегаты
Конструктор запросов также предоставляет множество методов для получения совокупных значений, таких как 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" для запроса:
use Illuminate\Support\Facades\DB; $users = DB::table('users') ->select('name', 'email as user_email') ->get();Метод distinct позволяет заставить запрос возвращать разные результаты:
$users = DB::table('users')->distinct()->get();Если у вас уже есть экземпляр query builder и вы хотите добавить столбец в его существующее предложение, вы можете использовать метод addSelect:
$query = DB::table('users')->select('name'); $users = $query->addSelect('age')->get();Необработанные выражения
Иногда вам может понадобиться вставить произвольную строку в запрос. Чтобы создать необработанное строковое выражение, вы можете использовать метод raw, предоставляемый фасадом DB:
$users = DB::table('users') ->select(DB::raw('count(*) as user_count, status')) ->where('status', '<>', 1) ->groupBy('status') ->get();{note} Необработанные операторы будут внедрены в запрос в виде строк, поэтому вы должны быть предельно осторожны, чтобы избежать создания уязвимостей SQL-инъекций.
Сырые методы
Вместо использования метода DB::raw вы также можете использовать следующие методы для вставки необработанного выражения в различные части вашего запроса. Помните, Laravel не может гарантировать, что любой запрос, использующий необработанные выражения, защищен от уязвимостей SQL-инъекций.
selectRaw
Вместо addSelect(DB::raw(...)) можно использовать метод selectRaw. Этот метод принимает необязательный массив привязок в качестве второго аргумента:
$orders = DB::table('orders') ->selectRaw('price * ? as price_with_tax', [1.0825]) ->get();whereRaw / orWhereRaw
Методы whereRaw и orWhereRaw можно использовать для вставки необработанного предложения "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
Query builder также можно использовать для добавления предложений соединения к вашим запросам. Чтобы выполнить базовое "inner join", вы можете использовать метод join в экземпляре query builder. Первый аргумент, передаваемый методу 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
Если вы хотите выполнить "left join" или "right join" вместо "inner 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 для выполнения "cross join". Перекрестные соединения генерируют декартово произведение между первой таблицей и объединенной таблицей:
$sizes = DB::table('sizes') ->crossJoin('colors') ->get();Расширенные выражения Join
Вы также можете указать более сложные предложения соединения. Чтобы начать, передайте замыкание в качестве второго аргумента метода join. Замыкание получит экземпляр Illuminate\Database\Query\JoinClause, который позволяет указать ограничения на предложение "join":
DB::table('users') ->join('contacts', function ($join) { $join->on('users.id', '=', 'contacts.user_id')->orOn(...); }) ->get();Если вы хотите использовать предложение "where" в своих соединениях, вы можете использовать методы where и orWhere , предоставляемые экземпляром JoinClause. Вместо сравнения двух столбцов эти методы сравнивают столбец со значением:
DB::table('users') ->join('contacts', function ($join) { $join->on('users.id', '=', 'contacts.user_id') ->where('contacts.user_id', '>', 5); }) ->get();Subquery Joins
Вы можете использовать методы 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 ($join) { $join->on('users.id', '=', 'latest_posts.user_id'); })->get();Unions
Query builder также предоставляет удобный способ "union" объединения двух или более запросов. Например, вы можете создать исходный запрос и использовать метод 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 query builder, чтобы добавить предложения "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();As previously mentioned, you may use any operator that is supported by your database system:
$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();{note} PDO does not support binding column names. Therefore, you should never allow user input to dictate the column names referenced by your queries, including "order by" columns.
Выражения Or Where
При объединении в цепочку вызовов метода where query builder предложения "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($query) { $query->where('name', 'Abigail') ->where('votes', '>', 50); }) ->get();В приведенном выше примере будет получен следующий SQL:
select * from users where votes > 100 or (name = 'Abigail' and votes > 50){note} Вы всегда должны группировать вызовы
orWhere, чтобы избежать неожиданного поведения при применении глобальных областей.
Выражения JSON Where
Laravel также поддерживает запросы к типам столбцов JSON в базах данных, которые обеспечивают поддержку типов столбцов JSON. В настоящее время это включает MySQL 5.7+, PostgreSQL, SQL Server 2016 и SQLite 3.9.0 (с расширением JSON1). Чтобы запросить столбец JSON, используйте оператор ->:
$users = DB::table('users') ->where('preferences->dining->meal', 'salad') ->get();Вы можете использовать whereJsonContains для запроса массивов JSON. Эта функция не поддерживается базой данных SQLite:
$users = DB::table('users') ->whereJsonContains('options->languages', 'en') ->get();Если ваше приложение использует базы данных 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
whereBetween / orWhereBetween
Метод whereBetween проверяет, находится ли значение столбца между двумя значениями:
$users = DB::table('users') ->whereBetween('votes', [1, 100]) ->get();whereNotBetween / orWhereNotBetween
Метод whereNotBetween проверяет, находится ли значение столбца за пределами двух значений:
$users = DB::table('users') ->whereNotBetween('votes', [1, 100]) ->get();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();{note} Если вы добавляете в запрос большой массив целочисленных привязок, можно использовать методы
whereIntegerInRawилиwhereIntegerNotInRaw, чтобы значительно сократить использование памяти.
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 ($query) { $query->where('votes', '>', 100) ->orWhere('title', '=', 'Admin'); }) ->get();Как видите, передача замыкания в метод where указывает конструктору запросов начать группу ограничений. Замыкание получит экземпляр query builder, который можно использовать для установки ограничений, которые должны содержаться в группе скобок. В приведенном выше примере будет получен следующий SQL:
select * from users where name = 'John' and (votes > 100 or title = 'Admin'){note} Вы всегда должны группировать вызовы
orWhere, чтобы избежать неожиданного поведения при применении глобальных областей.
Расширенные выражения Where
Выражения Where Exists
Метод whereExists позволяет вам писать предложения SQL "where exists". Метод whereExists принимает замыкание, которое получит экземпляр query builder, позволяя вам определить запрос, который должен быть помещен внутри предложения "exists":
$users = DB::table('users') ->whereExists(function ($query) { $query->select(DB::raw(1)) ->from('orders') ->whereColumn('orders.user_id', 'users.id'); }) ->get();Приведенный выше запрос выдаст следующий SQL:
select * from userswhere exists ( select 1 from orders where orders.user_id = users.id)Выражения Subquery Where
Иногда вам может понадобиться создать предложение "where", которое сравнивает результаты подзапроса с заданным значением. Вы можете сделать это, передав замыкание и значение методу where. Например, следующий запрос извлечет всех пользователей, у которых есть недавнее "membership" данного типа;
use App\Models\User; $users = User::where(function ($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 ($query) { $query->selectRaw('avg(i.amount)')->from('incomes as i');})->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 ($query, $role) { return $query->where('role_id', $role); }) ->get();Метод when выполняет данное замыкание только тогда, когда первый аргумент равен true. Если первый аргумент равен false, замыкание не будет выполнено. Таким образом, в приведенном выше примере замыкание, заданное для метода when, будет вызываться только в том случае, если поле role присутствует во входящем запросе и оценивается как true.
Вы можете передать другое замыкание в качестве третьего аргумента метода when. Это замыкание будет выполняться только в том случае, если первый аргумент оценивается как false. Чтобы проиллюстрировать, как можно использовать эту функцию, мы будем использовать ее для настройки порядка запросов по умолчанию:
$sortByVotes = $request->input('sort_by_votes'); $users = DB::table('users') ->when($sortByVotes, function ($query, $sortByVotes) { return $query->orderBy('votes'); }, function ($query) { return $query->orderBy('name'); }) ->get();Операторы вставки
Query builder также предоставляет метод 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 будет игнорировать ошибки при вставке записей в базу данных:
DB::table('users')->insertOrIgnore([ ['id' => 1, 'email' => 'sisko@example.com'], ['id' => 2, 'email' => 'archer@example.com'],]);{note}
insertOrIgnoreбудет игнорировать повторяющиеся записи, а также может игнорировать другие типы ошибок в зависимости от ядра базы данных. Например,insertOrIgnoreбудет обходить строгий режим MySQL.
Автоинкрементные идентификаторы
Если таблица имеет автоматически увеличивающийся идентификатор, используйте метод insertGetId, чтобы вставить запись, а затем получить идентификатор:
$id = DB::table('users')->insertGetId( ['email' => 'john@example.com', 'votes' => 0]);{note} При использовании 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 этой записи.
{note} Все базы данных, кроме SQL Server, требуют, чтобы столбцы во втором аргументе метода
upsertимели «первичный» или «уникальный» индекс. Кроме того, драйвер базы данных MySQL игнорирует второй аргумент методаupsertи всегда использует «первичный» и «уникальный» индексы таблицы для обнаружения существующих записей.
Операторы обновления
Помимо вставки записей в базу данных, построитель запросов также может обновлять существующие записи, используя метод update. Метод update, как и метод insert, принимает массив пар столбцов и значений, указывающих столбцы, которые необходимо обновить. Метод update возвращает количество затронутых строк. Вы можете ограничить запрос 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'] );Обновление столбцов JSON
При обновлении столбца JSON следует использовать синтаксис -> для обновления соответствующего ключа в объекте JSON. Эта операция поддерживается в MySQL 5.7+ и PostgreSQL 9.5+:
$affected = DB::table('users') ->where('id', 1) ->update(['options->enabled' => true]);Увеличение и уменьшение
Query builder также предоставляет удобные методы для увеличения или уменьшения значения заданного столбца. Оба этих метода принимают по крайней мере один аргумент: столбец, который нужно изменить. Второй аргумент может быть предоставлен, чтобы указать величину, на которую столбец должен быть увеличен или уменьшен:
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']);Операторы удаления
Метод delete построителя запросов может использоваться для удаления записей из таблицы. Метод 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. Это означает, что все записи, связанные с внешним ключом, в других таблицах также будут удалены.
Пессимистическая блокировка
Query builder также включает в себя несколько функций, которые помогут вам достичь «пессимистической блокировки» при выполнении ваших операторов 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();