Может войдёшь?
Черновики Написать статью Профиль

Конструктор запросов

перевод документация 5.х

  1. 1. Введение
  2. 2. Получение результатов
    1. 2.1. Получение результатов из таблицы «по кускам»
    2. 2.2. Агрегатные функции
  3. 3. Выборка (SELECT)
  4. 4. Сырые выражения
  5. 5. Объединения (JOIN)
  6. 6. Слияние (UNION)
  7. 7. Условия WHERE
    1. 7.1. Группировка условий
    2. 7.2. Проверка на существование
    3. 7.3. JSON фильтрация (WHERE)
  8. 8. Упорядочивание, группировка, предел и смещение
  9. 9. Условное применение условий
  10. 10. Вставка (INSERT)
  11. 11. Обновление (UPDATE)
    1. 11.1. Обновление JSON-столбцов
    2. 11.2. Increment и Decrement
  12. 12. Удаление (DELETE)
  13. 13. Пессимистическая блокировка
Этот перевод актуален для англоязычной документации на (ветка 5.3) , (ветка 5.2) , (ветка 5.1) и (ветка 5.0). Опечатка? Выдели и нажми Ctrl+Enter.

Введение

Конструктор запросов Laravel предоставляет удобный, выразительный интерфейс для создания и выполнения запросов к базе данных. Он может использоваться для выполнения большинства типов операций и работает со всеми поддерживаемыми СУБД.

Конструктор запросов Laravel использует привязку параметров к запросам средствами PDO для защиты вашего приложения от SQL-инъекций. Нет необходимости экранировать строки перед их передачей в запрос.

Получение результатов

Получение всех записей таблицы

Используйте метод PHPtable() фасада DB для создания запроса. Метод PHPtable() возвращает экземпляр конструктора запросов для данной таблицы, позволяя вам «прицепить» к запросу дополнительные условия и в итоге получить результат методом PHPget():

PHP
<?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. Вы можете получить значение каждого столбца, обращаясь к столбцу как к свойству объекта:

PHP
foreach ($users as $user) {
  echo 
$user->name;
}

Получение одной строки/столбца из таблицы

Если вам необходимо получить только одну строку из таблицы БД, используйте метод PHPfirst(). Этот метод вернёт один объект StdClass:

PHP
$user DB::table('users')->where('name''John')->first();

echo 
$user->name;

Если вам не нужна вся строка, вы можете извлечь одно значение из записи методом PHPvalue(). Этот метод вернёт значение конкретного столбца:

PHP
$email DB::table('users')->where('name''John')->value('email');

Получение списка всех значений одного столбца

+ 5.3 5.2

добавлено в 5.3 () 5.2 ()

Если вы хотите получить массив значений одного столбца, используйте метод PHPpluck(). В этом примере мы получим коллекцию (для версии 5.2 и ранее — массив) названий ролей:

PHP
$titles DB::table('roles')->pluck('title');

foreach (
$titles as $title) {
  echo 
$title;
}

Вы можете указать произвольный ключ для возвращаемой коллекции (для версии 5.2 и ранее — массива):

PHP
$roles DB::table('roles')->pluck('title''name');

foreach (
$roles as $name => $title) {
  echo 
$title;
}
+ 5.1 5.0

добавлено в 5.1 () 5.0 ()

Если вы хотите получить массив значений одного столбца, используйте метод PHPlists(). В этом примере мы получим массив названий ролей:

PHP
$titles DB::table('roles')->lists('title');

foreach (
$titles as $title) {
  echo 
$title;
}

Вы можете указать произвольный ключ для возвращаемого массива:

PHP
$roles DB::table('roles')->lists('title''name');

foreach (
$roles as $name => $title) {
  echo 
$title;
}

Получение результатов из таблицы «по кускам»

Если вам необходимо обработать тысячи записей БД, попробуйте использовать метод PHPchunk(). Этот метод получает небольшой «кусок» результатов за раз и отправляет его в замыкание для обработки. Этот метод очень полезен для написания Artisan-команд, которые обрабатывают тысячи записей. Например, давайте обработаем всю таблицу users «кусками» по 100 записей:

PHP
DB::table('users')->orderBy('id')->chunk(100, function ($users) {
  foreach (
$users as $user) {
    
//
  
}
});

Вы можете остановить обработку последующих «кусков» вернув false из замыкания:

PHP
DB::table('users')->orderBy('id')->chunk(100, function ($users) {
  
// Обработка записей...

  
return false;
});

Агрегатные функции

Конструктор запросов содержит множество агрегатных методов, таких как count, max, min, avg и sum. Вы можете вызывать их после создания своего запроса:

PHP
$users DB::table('users')->count();

$price DB::table('orders')->max('price');

Разумеется, вы можете комбинировать эти методы с другими условиями:

PHP
$price DB::table('orders')
                ->
where('finalized'1)
                ->
avg('price');

Выборка (SELECT)

Указание столбцов для выборки

Само собой, не всегда вам необходимо выбрать все столбцы из таблицы БД. Используя метод PHPselect() вы можете указать необходимые столбцы для запроса:

PHP
$users DB::table('users')->select('name''email as user_email')->get();

Метод PHPdistinct() позволяет вернуть только отличающиеся результаты:

PHP
$users DB::table('users')->distinct()->get();

Если у вас уже есть экземпляр конструктора запросов и вы хотите добавить столбец к существующему набору для выборки, используйте метод PHPaddSelect():

PHP
$query DB::table('users')->select('name');

$users $query->addSelect('age')->get();

Сырые выражения

Иногда вам может понадобиться использовать уже готовое SQL-выражение в вашем запросе. Такие выражения вставляются в запрос напрямую в виде строк, поэтому будьте внимательны и не допускайте возможностей для SQL-инъекций! Для создания сырого выражения используйте метод PHPDB::raw():

PHP
$users DB::table('users')
                     ->
select(DB::raw('count(*) as user_count, status'))
                     ->
where('status''<>'1)
                     ->
groupBy('status')
                     ->
get();

Объединения (JOIN)

Объединение INNER JOIN

Конструктор запросов может быть использован для объединения данных из нескольких таблиц через PHPJOIN. Для выполнения обычного объединения «inner join», используйте метод PHPjoin() на экземпляре конструктора запросов. Первый аргумент метода PHPjoin() — имя таблицы, к которой необходимо присоединить другие, а остальные аргументы указывают условия для присоединения столбцов. Как видите, вы можете объединять несколько таблиц одним запросом:

PHP
$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

Для выполнения объединения «left join» вместо «inner join», используйте метод PHPleftJoin(). Этот метод имеет ту же сигнатуру, что и метод PHPjoin():

PHP
$users DB::table('users')
            ->
leftJoin('posts''users.id''=''posts.user_id')
            ->
get();
+ 5.3 5.2

добавлено в 5.3 () 5.2 ()

Объединение CROSS JOIN

Для выполнения объединения CROSS JOIN используйте метод PHPcrossJoin() с именем таблицы, с которой нужно произвести объединение. CROSS JOIN формирует таблицу перекрестным соединением (декартовым произведением) двух таблиц:

PHP
$users DB::table('sizes')
            ->
crossJoin('colours')
            ->
get();

Сложные условия объединения

Вы можете указать более сложные условия для объединения. Для начала передайте замыкание вторым аргументом метода PHPjoin(). Замыкание будет получать объект JoinClause, позволяя вам указать условия для объединения:

PHP
DB::table('users')
        ->
join('contacts', function ($join) {
            
$join->on('users.id''=''contacts.user_id')->orOn(...);
        })
        ->
get();

Если вы хотите использовать стиль «where» для ваших объединений, то можете использовать для этого методы PHPwhere() и PHPorWhere(). Вместо сравнения двух столбцов эти методы будут сравнивать столбец и значение:

PHP
DB::table('users')
        ->
join('contacts', function ($join) {
            
$join->on('users.id''=''contacts.user_id')
                 ->
where('contacts.user_id''>'5);
        })
        ->
get();

Слияние (UNION)

Конструктор запросов позволяет создавать слияния двух запросов вместе. Например, вы можете создать начальный запрос и с помощью метода PHPunion() слить его со вторым запросом:

PHP
$first DB::table('users')
            ->
whereNull('first_name');

$users DB::table('users')
            ->
whereNull('last_name')
            ->
union($first)
            ->
get();

Также существует метод PHPunionAll() с аналогичными параметрами.

Условия WHERE

Простые условия WHERE

Для добавления в запрос условий where используйте метод PHPwhere() на экземпляре конструктора запросов. Самый простой вызов PHPwhere() требует три аргумента. Первый — имя столбца. Второй — оператор (любой из поддерживаемых базой данных). Третий — значение для сравнения со столбцом.

Например, вот запрос, проверяющий равенство значения столбца «votes» и 100:

PHP
$users DB::table('users')->where('votes''='100)->get();

Для удобства, если вам необходимо просто проверить равенство значения столбца и данного значения, вы можете передать значение сразу вторым аргументом метода PHPwhere():

PHP
$users DB::table('users')->where('votes'100)->get();

Разумеется, вы можете использовать различные другие операторы при написании условия where:

PHP
$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();
+ 5.3 5.2

добавлено в 5.3 () 5.2 ()

В функцию PHPwhere() также можно передать массив условий:

PHP
$users DB::table('users')->where([
  [
'status''=''1'],
  [
'subscribed''<>''1'],
])->
get();

Условия ИЛИ

Вы можете сцепить вместе условия where, а также условия or в запросе. Метод PHPorWhere() принимает те же аргументы, что и метод PHPwhere():

PHP
$users DB::table('users')
                    ->
where('votes''>'100)
                    ->
orWhere('name''John')
                    ->
get();

Дополнительные условия WHERE

В интервале

Метод PHPwhereBetween() проверяет, что значения столбца находится в указанном интервале:

PHP
$users DB::table('users')
                    ->
whereBetween('votes', [1100])->get();

Вне интервала

Метод PHPwhereNotBetween() проверяет, что значения столбца находится вне указанного интервала:

PHP
$users DB::table('users')
                    ->
whereNotBetween('votes', [1100])
                    ->
get();

Фильтрация по совпадению с массивом значений

Метод PHPwhereIn() проверяет, что значения столбца содержатся в данном массиве:

PHP
$users DB::table('users')
                    ->
whereIn('id', [123])
                    ->
get();

Метод PHPwhereNotIn() проверяет, что значения столбца не содержатся в данном массиве:

PHP
$users DB::table('users')
                    ->
whereNotIn('id', [123])
                    ->
get();

Поиск неустановленных значений (NULL)

Метод PHPwhereNull() проверяет, что значения столбца равны PHPNULL:

PHP
$users DB::table('users')
                    ->
whereNull('updated_at')
                    ->
get();

Метод PHPwhereNotNull() проверяет, что значения столбца не равны PHPNULL:

PHP
$users DB::table('users')
                    ->
whereNotNull('updated_at')
                    ->
get();
+ 5.3

добавлено в 5.3 ()

whereDate / whereMonth / whereDay / whereYear

Метод PHPwhereDate() служит для сравнения значения столбца с датой:

PHP
$users DB::table('users')
                ->
whereDate('created_at''2016-12-31')
                ->
get();

Метод PHPwhereMonth() служит для сравнения значения столбца с месяцем в году:

PHP
$users DB::table('users')
                ->
whereMonth('created_at''12')
                ->
get();

Метод PHPwhereDay() служит для сравнения значения столбца с днём месяца:

PHP
$users DB::table('users')
                ->
whereDay('created_at''31')
                ->
get();

Метод PHPwhereYear() служит для сравнения значения столбца с указанным годом:

PHP
$users DB::table('users')
                ->
whereYear('created_at''2016')
                ->
get();
+ 5.0

добавлено в 5.0 ()

Динамические условия WHERE

Вы можете использовать даже «динамические» условия where для гибкого построения операторов, используя магические методы:

PHP
$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();
+ 5.3 5.2

добавлено в 5.3 () 5.2 ()

whereColumn

Для проверки на совпадение двух столбцов можно использовать метод PHPwhereColumn():

PHP
$users DB::table('users')
                ->
whereColumn('first_name''last_name')
                ->
get();

В метод также можно передать оператор сравнения:

PHP
$users DB::table('users')
                ->
whereColumn('updated_at''>''created_at')
                ->
get();

В метод PHPwhereColumn() также можно передать массив с несколькими условиями. Эти условия будут объединены оператором AND:

PHP
$users DB::table('users')
                ->
whereColumn([
                  [
'first_name''=''last_name'],
                  [
'updated_at''>''created_at']
                ])->
get();

Группировка условий

Иногда вам нужно сделать выборку по более сложным параметрам, таким как «существует ли» или вложенная группировка условий. Конструктор запросов Laravel справится и с такими запросами. Для начала посмотрим на пример группировки условий в скобках:

PHP
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»:

PHP
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
)
+ 5.3 5.2

добавлено в 5.3 () 5.2 ()

JSON фильтрация (WHERE)

Laravel также поддерживает запросы для столбцов типа JSON в тех БД, которые поддерживают тип столбцов JSON. На данный момент это MySQL 5.7 и Postgres. Для запроса JSON столбца используйте оператор -> :

PHP
$users DB::table('users')
                ->
where('options->language''en')
                ->
get();

$users DB::table('users')
                ->
where('preferences->dining->meal''salad')
                ->
get();

Упорядочивание, группировка, предел и смещение

orderBy

Метод PHPorderBy() позволяет вам отсортировать результат запроса по заданному столбцу. Первый аргумент метода PHPorderBy() — столбец для сортировки по нему, а второй — задаёт направление сортировки и может быть либо asc, либо desc:

PHP
$users DB::table('users')
                ->
orderBy('name''desc')
                ->
get();
+ 5.3

добавлено в 5.3 ()

latest / oldest

Методы PHPlatest() и PHPoldest() позволяют легко отсортировать результаты по дате. По умолчанию выполняется сортировка по столбцу created_at. Или вы можете передать имя столбца для сортировки по нему:

PHP
$user DB::table('users')
                ->
latest()
                ->
first();
+ 5.3 5.2

добавлено в 5.3 () 5.2 ()

inRandomOrder

Для сортировки результатов запроса в случайном порядке можно использовать метод PHPinRandomOrder(). Например, вы можете использовать этот метод для выбора случайного пользователя:

PHP
$randomUser DB::table('users')
                ->
inRandomOrder()
                ->
first();

groupBy / having / havingRaw

Методы PHPgroupBy() и PHPhaving() используются для группировки результатов запроса. Сигнатура метода PHPhaving() аналогична методу PHPwhere():

PHP
$users DB::table('users')
                ->
groupBy('account_id')
                ->
having('account_id''>'100)
                ->
get();

Метод PHPhavingRaw() используется для передачи сырой строки в условие having. Например, мы можем найти все филиалы с объёмом продаж выше $2,500:

PHP
$users DB::table('orders')
                ->
select('department'DB::raw('SUM(price) as total_sales'))
                ->
groupBy('department')
                ->
havingRaw('SUM(price) > 2500')
                ->
get();

skip / take

Для ограничения числа возвращаемых результатов из запроса или для пропуска заданного числа результатов в запросе используются методы PHPskip() и PHPtake():

PHP
$users DB::table('users')->skip(10)->take(5)->get();
+ 5.3

добавлено в 5.3 ()

Или вы можете использовать методы PHPlimit() и PHPoffset():

PHP
$users DB::table('users')
                ->
offset(10)
                ->
limit(5)
                ->
get();
+ 5.3 5.2

добавлено в 5.3 () 5.2 ()

Условное применение условий

Иногда необходимо применять условие к запросу, только если выполняется какое-то другое условие. Например, выполнять оператор PHPwhere, только если нужное значение есть во входящем запросе. Это можно сделать с помощью метода PHPwhen():

PHP
$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

добавлено в 5.3 ()

Вы можете передать ещё одно замыкание третьим параметром метода PHPwhen(). Это замыкание будет выполнено, если первый параметр будет иметь значение PHPfalse. Для демонстрации работы этой функции мы используем её для настройки сортировки по умолчанию для запроса:

PHP
$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() принимает массив имён столбцов и значений:

PHP
DB::table('users')->insert(
  [
'email' => 'john@example.com''votes' => 0]
);

Вы можете вставить в таблицу сразу несколько записей одним вызовом PHPinsert(), передав ему массив массивов, каждый из которых — строка для вставки в таблицу:

PHP
DB::table('users')->insert([
  [
'email' => 'taylor@example.com''votes' => 0],
  [
'email' => 'dayle@example.com''votes' => 0]
]);

Автоинкрементные ID

Если в таблице есть автоинкрементный ID, используйте метод PHPinsertGetId() для вставки записи и получения её ID:

PHP
$id DB::table('users')->insertGetId(
  [
'email' => 'john@example.com''votes' => 0]
);

При использовании метода PHPinsertGetId() для PostgreSQL автоинкрементное поле должно иметь имя PHPid. Если вы хотите получить ID из другого поля таблицы, вы можете передать его имя вторым аргументом.

Обновление (UPDATE)

Разумеется, кроме вставки записей в БД конструктор запросов может и изменять существующие строки с помощью метода PHPupdate(). Метод PHPupdate(), как и метод PHPinsert(), принимает массив столбцов и пар значений, содержащих столбцы для обновления. Вы можете ограничить запрос PHPupdate() условием PHPwhere():

PHP
DB::table('users')
            ->
where('id'1)
            ->
update(['votes' => 1]);
+ 5.3

добавлено в 5.3 ()

Обновление JSON-столбцов

При обновлении JSON-столбцов используйте синтаксис PHP-> для обращения к нужному ключу в JSON-объекте. Эта операция поддерживается только в БД, поддерживающих JSON-столбцы:

PHP
DB::table('users')
            ->
where('id'1)
            ->
update(['options->enabled' => true]);

Increment и Decrement

Конструктор запросов предоставляет удобные методы для увеличения и уменьшения значений заданных столбцов. Это просто более выразительный и краткий способ по сравнению с написанием оператора update вручную.

Оба метода принимают один обязательный аргумент — столбец для изменения. Второй аргумент может быть передан для указания, на какую величину необходимо изменить значение столбца:

PHP
DB::table('users')->increment('votes');

DB::table('users')->increment('votes'5);

DB::table('users')->decrement('votes');

DB::table('users')->decrement('votes'5);

Вы также можете указать дополнительные поля для изменения:

PHP
DB::table('users')->increment('votes'1, ['name' => 'John']);

Удаление (DELETE)

Конструктор запросов предоставляет метод PHPdelete() для удаления записей из таблиц. Вы можете ограничить оператор PHPdelete(), добавив условие PHPwhere() перед его вызовом:

PHP
DB::table('users')->delete();

DB::table('users')->where('votes''>'100)->delete();

Если вы хотите очистить таблицу (усечение), удалив все строки и обнулив счётчик ID, используйте метод PHPtruncate():

PHP
DB::table('users')->truncate();

Усечение таблицы аналогично удалению всех её записей, а также сбросом счётчика autoincrement-полей. — прим. пер.

Пессимистическая блокировка

В конструкторе запросов есть несколько функций, которые помогают делать «пессимистическую блокировку» (pessimistic locking) для ваших операторов SELECT. Для запуска оператора SELECT с «разделяемой блокировкой» вы можете использовать в запросе метод PHPsharedLock(). Разделяемая блокировка предотвращает изменение выбранных строк до конца транзакции:

PHP
DB::table('users')->where('votes''>'100)->sharedLock()->get();

Или вы можете использовать метод PHPlockForUpdate(). Блокировка «для изменения» предотвращает изменение строк и их выбор другими разделяемыми блокировками:

PHP
DB::table('users')->where('votes''>'100)->lockForUpdate()->get();

Комментарии (4)

al_saher

Класс!

vitiank

Возможно ли динамически составлять набор методов для выборки? В случае, если заранее набор условий неизвестен, я могу добавить их в RAW sql в цикле, но как реализовать добавление например where в цикле. И если в случае, если у меня только where, я могу добавить условия в массив, то если добавится LIKE, то как реализовать я уже не знаю. Есть какая нибудь информация?

Alexandr5

Конечно поздно, но может другим понадобится. Если я правильно понял, то тебе нужно что то такое
$query = \DB::table('название таблицы');
$query->where('что то сравниваешь', 'с чем то сравниваешь');
$query->groupBy('id')->get();

Как то вот так.

paha333

А как сделать чтобы выбрало где одно поле меньше другого поля в той же таблице???! В Mysql запрос такого вида

sqlSELECT * FROM `offers` WHERE `floor`<`number_floors`

а в ларавел пробую так

PHP
$where[]=['offers.floor','<','offers.number_floors'];

но не работает!!! что делать?

Написать комментарий

Разметка: ? ?

Авторизуйся, чтобы прокомментировать.