Введение
Конструктор запросов Laravel предоставляет удобный, выразительный интерфейс для создания и выполнения запросов к базе данных. Он может использоваться для выполнения большинства типов операций и работает со всеми поддерживаемыми СУБД.
Конструктор запросов Laravel использует привязку параметров к запросам средствами PDO для защиты вашего приложения от SQL-инъекций. Нет необходимости экранировать строки перед их передачей в запрос.
Получение результатов
Получение всех записей таблицы
Используйте метод PHPtable()
фасада DB для создания запроса. Метод PHPtable()
возвращает экземпляр конструктора запросов для данной таблицы, позволяя вам «прицепить» к запросу дополнительные условия и в итоге получить результат методом PHPget()
:
<?php
namespace App\Http\Controllers;
use Illuminate\Support\Facades\DB;
//для версии 5.2 и ранее:
//use DB;
use App\Http\Controllers\Controller;
class UserController extends Controller
{
/**
* Показать список всех пользователей приложения.
*
* @return Response
*/
public function index()
{
$users = DB::table('users')->get();
return view('user.index', ['users' => $users]);
}
}
Метод PHPget()
возвращает объект Illuminate\Support\Collection (для версии 5.2 и ранее — массив) c результатами, в котором каждый результат — это экземпляр PHP-объекта StdClass. Вы можете получить значение каждого столбца, обращаясь к столбцу как к свойству объекта:
foreach ($users as $user) {
echo $user->name;
}
Получение одной строки/столбца из таблицы
Если вам необходимо получить только одну строку из таблицы БД, используйте метод PHPfirst()
. Этот метод вернёт один объект StdClass:
$user = DB::table('users')->where('name', 'John')->first();
echo $user->name;
Если вам не нужна вся строка, вы можете извлечь одно значение из записи методом PHPvalue()
. Этот метод вернёт значение конкретного столбца:
$email = DB::table('users')->where('name', 'John')->value('email');
Получение списка всех значений одного столбца
Если вы хотите получить массив значений одного столбца, используйте метод PHPpluck()
. В этом примере мы получим коллекцию (для версии 5.2 и ранее — массив) названий ролей:
$titles = DB::table('roles')->pluck('title');
foreach ($titles as $title) {
echo $title;
}
Вы можете указать произвольный ключ для возвращаемой коллекции (для версии 5.2 и ранее — массива):
$roles = DB::table('roles')->pluck('title', 'name');
foreach ($roles as $name => $title) {
echo $title;
}
Если вы хотите получить массив значений одного столбца, используйте метод PHPlists()
. В этом примере мы получим массив названий ролей:
$titles = DB::table('roles')->lists('title');
foreach ($titles as $title) {
echo $title;
}
Вы можете указать произвольный ключ для возвращаемого массива:
$roles = DB::table('roles')->lists('title', 'name');
foreach ($roles as $name => $title) {
echo $title;
}
Получение результатов из таблицы «по кускам»
Если вам необходимо обработать тысячи записей БД, попробуйте использовать метод PHPchunk()
. Этот метод получает небольшой «кусок» результатов за раз и отправляет его в замыкание для обработки. Этот метод очень полезен для написания Artisan-команд, которые обрабатывают тысячи записей. Например, давайте обработаем всю таблицу users «кусками» по 100 записей:
DB::table('users')->orderBy('id')->chunk(100, function ($users) {
foreach ($users as $user) {
//
}
});
Вы можете остановить обработку последующих «кусков» вернув false из замыкания:
DB::table('users')->orderBy('id')->chunk(100, function ($users) {
// Обработка записей...
return false;
});
Агрегатные функции
Конструктор запросов содержит множество агрегатных методов, таких как count, max, min, avg и sum. Вы можете вызывать их после создания своего запроса:
$users = DB::table('users')->count();
$price = DB::table('orders')->max('price');
Разумеется, вы можете комбинировать эти методы с другими условиями:
$price = DB::table('orders')
->where('finalized', 1)
->avg('price');
Выборка (SELECT)
Само собой, не всегда вам необходимо выбрать все столбцы из таблицы БД. Используя метод PHPselect()
вы можете указать необходимые столбцы для запроса:
$users = DB::table('users')->select('name', 'email as user_email')->get();
Метод PHPdistinct()
позволяет вернуть только отличающиеся результаты:
$users = DB::table('users')->distinct()->get();
Если у вас уже есть экземпляр конструктора запросов и вы хотите добавить столбец к существующему набору для выборки, используйте метод PHPaddSelect()
:
$query = DB::table('users')->select('name');
$users = $query->addSelect('age')->get();
Сырые выражения
Иногда вам может понадобиться использовать уже готовое SQL-выражение в вашем запросе. Такие выражения вставляются в запрос напрямую в виде строк, поэтому будьте внимательны и не допускайте возможностей для SQL-инъекций! Для создания сырого выражения используйте метод PHPDB::raw()
:
$users = DB::table('users')
->select(DB::raw('count(*) as user_count, status'))
->where('status', '<>', 1)
->groupBy('status')
->get();
Объединения (JOIN)
Конструктор запросов может быть использован для объединения данных из нескольких таблиц через PHPJOIN
. Для выполнения обычного объединения «inner join», используйте метод PHPjoin()
на экземпляре конструктора запросов. Первый аргумент метода PHPjoin()
— имя таблицы, к которой необходимо присоединить другие, а остальные аргументы указывают условия для присоединения столбцов. Как видите, вы можете объединять несколько таблиц одним запросом:
$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» вместо «inner join», используйте метод PHPleftJoin()
. Этот метод имеет ту же сигнатуру, что и метод PHPjoin()
:
$users = DB::table('users')
->leftJoin('posts', 'users.id', '=', 'posts.user_id')
->get();
Вы можете указать более сложные условия для объединения. Для начала передайте замыкание вторым аргументом метода PHPjoin()
. Замыкание будет получать объект JoinClause, позволяя вам указать условия для объединения:
DB::table('users')
->join('contacts', function ($join) {
$join->on('users.id', '=', 'contacts.user_id')->orOn(...);
})
->get();
Если вы хотите использовать стиль «where» для ваших объединений, то можете использовать для этого методы PHPwhere()
и PHPorWhere()
. Вместо сравнения двух столбцов эти методы будут сравнивать столбец и значение:
DB::table('users')
->join('contacts', function ($join) {
$join->on('users.id', '=', 'contacts.user_id')
->where('contacts.user_id', '>', 5);
})
->get();
Слияние (UNION)
Конструктор запросов позволяет создавать слияния двух запросов вместе. Например, вы можете создать начальный запрос и с помощью метода PHPunion()
слить его со вторым запросом:
$first = DB::table('users')
->whereNull('first_name');
$users = DB::table('users')
->whereNull('last_name')
->union($first)
->get();
Также существует метод PHPunionAll()
с аналогичными параметрами.
Условия WHERE
Для добавления в запрос условий where используйте метод PHPwhere()
на экземпляре конструктора запросов. Самый простой вызов PHPwhere()
требует три аргумента. Первый — имя столбца. Второй — оператор (любой из поддерживаемых базой данных). Третий — значение для сравнения со столбцом.
Например, вот запрос, проверяющий равенство значения столбца «votes» и 100:
$users = DB::table('users')->where('votes', '=', 100)->get();
Для удобства, если вам необходимо просто проверить равенство значения столбца и данного значения, вы можете передать значение сразу вторым аргументом метода PHPwhere()
:
$users = DB::table('users')->where('votes', 100)->get();
Разумеется, вы можете использовать различные другие операторы при написании условия where:
$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, а также условия or в запросе. Метод PHPorWhere()
принимает те же аргументы, что и метод PHPwhere()
:
$users = DB::table('users')
->where('votes', '>', 100)
->orWhere('name', 'John')
->get();
Метод PHPwhereBetween()
проверяет, что значения столбца находится в указанном интервале:
$users = DB::table('users')
->whereBetween('votes', [1, 100])->get();
Метод PHPwhereNotBetween()
проверяет, что значения столбца находится вне указанного интервала:
$users = DB::table('users')
->whereNotBetween('votes', [1, 100])
->get();
Фильтрация по совпадению с массивом значений
Метод PHPwhereIn()
проверяет, что значения столбца содержатся в данном массиве:
$users = DB::table('users')
->whereIn('id', [1, 2, 3])
->get();
Метод PHPwhereNotIn()
проверяет, что значения столбца не содержатся в данном массиве:
$users = DB::table('users')
->whereNotIn('id', [1, 2, 3])
->get();
Поиск неустановленных значений (NULL)
Метод PHPwhereNull()
проверяет, что значения столбца равны PHPNULL
:
$users = DB::table('users')
->whereNull('updated_at')
->get();
Метод PHPwhereNotNull()
проверяет, что значения столбца не равны PHPNULL
:
$users = DB::table('users')
->whereNotNull('updated_at')
->get();
добавлено в 5.3 ()
whereDate / whereMonth / whereDay / whereYear
Метод PHPwhereDate()
служит для сравнения значения столбца с датой:
$users = DB::table('users')
->whereDate('created_at', '2016-12-31')
->get();
Метод PHPwhereMonth()
служит для сравнения значения столбца с месяцем в году:
$users = DB::table('users')
->whereMonth('created_at', '12')
->get();
Метод PHPwhereDay()
служит для сравнения значения столбца с днём месяца:
$users = DB::table('users')
->whereDay('created_at', '31')
->get();
Метод PHPwhereYear()
служит для сравнения значения столбца с указанным годом:
$users = DB::table('users')
->whereYear('created_at', '2016')
->get();
добавлено в 5.0 ()
Вы можете использовать даже «динамические» условия where для гибкого построения операторов, используя магические методы:
$admin = DB::table('users')->whereId(1)->first();
$john = DB::table('users')
->whereIdAndEmail(2, 'john@doe.com')
->first();
$jane = DB::table('users')
->whereNameOrAge('Jane', 22)
->first();
Для проверки на совпадение двух столбцов можно использовать метод PHPwhereColumn()
:
$users = DB::table('users')
->whereColumn('first_name', 'last_name')
->get();
В метод также можно передать оператор сравнения:
$users = DB::table('users')
->whereColumn('updated_at', '>', 'created_at')
->get();
В метод PHPwhereColumn()
также можно передать массив с несколькими условиями. Эти условия будут объединены оператором AND:
$users = DB::table('users')
->whereColumn([
['first_name', '=', 'last_name'],
['updated_at', '>', 'created_at']
])->get();
Группировка условий
Иногда вам нужно сделать выборку по более сложным параметрам, таким как «существует ли» или вложенная группировка условий. Конструктор запросов Laravel справится и с такими запросами. Для начала посмотрим на пример группировки условий в скобках:
DB::table('users')
->where('name', '=', 'John')
->orWhere(function ($query) {
$query->where('votes', '>', 100)
->where('title', '<>', 'Admin');
})
->get();
Как видите, передав замыкание в метод PHPorWhere()
, мы дали конструктору запросов команду, начать группировку условий. Замыкание получит экземпляр конструктора запросов, который вы можете использовать для задания условий, поместив их в скобки. Приведённый пример выполнит такой SQL-запрос:
sqlselect * from users where name = 'John' or (votes > 100 and title <> 'Admin')
Проверка на существование
Метод PHPwhereExists()
позволяет написать SQL-условие where exists. Метод PHPwhereExists()
принимает в качестве аргумента замыкание, которое получит экземпляр конструктора запросов, позволяя вам определить запрос для помещения в условие «exists»:
DB::table('users')
->whereExists(function ($query) {
$query->select(DB::raw(1))
->from('orders')
->whereRaw('orders.user_id = users.id');
})
->get();
Этот пример выполнит такой SQL-запрос:
sqlselect * from users where exists ( select 1 from orders where orders.user_id = users.id )
JSON фильтрация (WHERE)
Laravel также поддерживает запросы для столбцов типа JSON в тех БД, которые поддерживают тип столбцов JSON. На данный момент это MySQL 5.7 и Postgres. Для запроса JSON столбца используйте оператор -> :
$users = DB::table('users')
->where('options->language', 'en')
->get();
$users = DB::table('users')
->where('preferences->dining->meal', 'salad')
->get();
Упорядочивание, группировка, предел и смещение
Метод PHPorderBy()
позволяет вам отсортировать результат запроса по заданному столбцу. Первый аргумент метода PHPorderBy()
— столбец для сортировки по нему, а второй — задаёт направление сортировки и может быть либо asc, либо desc:
$users = DB::table('users')
->orderBy('name', 'desc')
->get();
добавлено в 5.3 ()
Методы PHPgroupBy()
и PHPhaving()
используются для группировки результатов запроса. Сигнатура метода PHPhaving()
аналогична методу PHPwhere()
:
$users = DB::table('users')
->groupBy('account_id')
->having('account_id', '>', 100)
->get();
Метод PHPhavingRaw()
используется для передачи сырой строки в условие having. Например, мы можем найти все филиалы с объёмом продаж выше $2,500:
$users = DB::table('orders')
->select('department', DB::raw('SUM(price) as total_sales'))
->groupBy('department')
->havingRaw('SUM(price) > 2500')
->get();
Для ограничения числа возвращаемых результатов из запроса или для пропуска заданного числа результатов в запросе используются методы PHPskip()
и PHPtake()
:
$users = DB::table('users')->skip(10)->take(5)->get();
добавлено в 5.3 ()
Условное применение условий
Иногда необходимо применять условие к запросу, только если выполняется какое-то другое условие. Например, выполнять оператор PHPwhere
, только если нужное значение есть во входящем запросе. Это можно сделать с помощью метода PHPwhen()
:
$role = $request->input('role');
$users = DB::table('users')
->when($role, function ($query) use ($role) {
return $query->where('role_id', $role);
})
->get();
Метод PHPwhen()
выполняет данное замыкание, только когда первый параметр равен PHPtrue
. Если первый параметр равен PHPfalse
, то замыкание не будет выполнено.
добавлено в 5.3 ()
Вы можете передать ещё одно замыкание третьим параметром метода PHPwhen()
. Это замыкание будет выполнено, если первый параметр будет иметь значение PHPfalse
. Для демонстрации работы этой функции мы используем её для настройки сортировки по умолчанию для запроса:
$sortBy = null;
$users = DB::table('users')
->when($sortBy, function ($query) use ($sortBy) {
return $query->orderBy($sortBy);
}, function ($query) {
return $query->orderBy('name');
})
->get();
Вставка (INSERT)
Конструктор запросов предоставляет метод PHPinsert()
для вставки записей в таблицу БД. Метод PHPinsert()
принимает массив имён столбцов и значений:
DB::table('users')->insert(
['email' => 'john@example.com', 'votes' => 0]
);
Вы можете вставить в таблицу сразу несколько записей одним вызовом PHPinsert()
, передав ему массив массивов, каждый из которых — строка для вставки в таблицу:
DB::table('users')->insert([
['email' => 'taylor@example.com', 'votes' => 0],
['email' => 'dayle@example.com', 'votes' => 0]
]);
Если в таблице есть автоинкрементный ID, используйте метод PHPinsertGetId()
для вставки записи и получения её ID:
$id = DB::table('users')->insertGetId(
['email' => 'john@example.com', 'votes' => 0]
);
При использовании метода PHPinsertGetId()
для PostgreSQL автоинкрементное поле должно иметь имя PHPid
. Если вы хотите получить ID из другого поля таблицы, вы можете передать его имя вторым аргументом.
Обновление (UPDATE)
Разумеется, кроме вставки записей в БД конструктор запросов может и изменять существующие строки с помощью метода PHPupdate()
. Метод PHPupdate()
, как и метод PHPinsert()
, принимает массив столбцов и пар значений, содержащих столбцы для обновления. Вы можете ограничить запрос PHPupdate()
условием PHPwhere()
:
DB::table('users')
->where('id', 1)
->update(['votes' => 1]);
добавлено в 5.3 ()
Increment и Decrement
Конструктор запросов предоставляет удобные методы для увеличения и уменьшения значений заданных столбцов. Это просто более выразительный и краткий способ по сравнению с написанием оператора update вручную.
Оба метода принимают один обязательный аргумент — столбец для изменения. Второй аргумент может быть передан для указания, на какую величину необходимо изменить значение столбца:
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)
Конструктор запросов предоставляет метод PHPdelete()
для удаления записей из таблиц. Вы можете ограничить оператор PHPdelete()
, добавив условие PHPwhere()
перед его вызовом:
DB::table('users')->delete();
DB::table('users')->where('votes', '>', 100)->delete();
Если вы хотите очистить таблицу (усечение), удалив все строки и обнулив счётчик ID, используйте метод PHPtruncate()
:
DB::table('users')->truncate();
Усечение таблицы аналогично удалению всех её записей, а также сбросом счётчика autoincrement-полей. — прим. пер.
Пессимистическая блокировка
В конструкторе запросов есть несколько функций, которые помогают делать «пессимистическую блокировку» (pessimistic locking) для ваших операторов SELECT. Для запуска оператора SELECT с «разделяемой блокировкой» вы можете использовать в запросе метод PHPsharedLock()
. Разделяемая блокировка предотвращает изменение выбранных строк до конца транзакции:
DB::table('users')->where('votes', '>', 100)->sharedLock()->get();
Или вы можете использовать метод PHPlockForUpdate()
. Блокировка «для изменения» предотвращает изменение строк и их выбор другими разделяемыми блокировками:
DB::table('users')->where('votes', '>', 100)->lockForUpdate()->get();
Комментарии (4)
Класс!
Возможно ли динамически составлять набор методов для выборки? В случае, если заранее набор условий неизвестен, я могу добавить их в RAW sql в цикле, но как реализовать добавление например where в цикле. И если в случае, если у меня только where, я могу добавить условия в массив, то если добавится LIKE, то как реализовать я уже не знаю. Есть какая нибудь информация?
Конечно поздно, но может другим понадобится. Если я правильно понял, то тебе нужно что то такое
$query = \DB::table('название таблицы');
$query->where('что то сравниваешь', 'с чем то сравниваешь');
$query->groupBy('id')->get();
Как то вот так.
А как сделать чтобы выбрало где одно поле меньше другого поля в той же таблице???! В Mysql запрос такого вида
а в ларавел пробую так
$where[]=['offers.floor','<','offers.number_floors'];
но не работает!!! что делать?