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

Введение

Конструктор запросов 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');

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

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

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

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

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

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

Объединение INNER 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

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

$users = DB::table('users')
            ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
            ->get();

$users = DB::table('sizes')
            ->crossJoin('colours')
            ->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

Для добавления в запрос условий 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();

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

Условия ИЛИ

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

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

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

В интервале

Метод 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();

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

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

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

$users = DB::table('users')
                ->whereYear('created_at', '2016')
                ->get();

$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();

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

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

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

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

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

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

orderBy

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

$users = DB::table('users')
                ->orderBy('name', 'desc')
                ->get();

$user = DB::table('users')
                ->latest()
                ->first();

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

groupBy / having / havingRaw

Методы 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();

skip / take

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

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

$users = DB::table('users')
                ->offset(10)
                ->limit(5)
                ->get();

$role = $request->input('role');

$users = DB::table('users')
                ->when($role, function ($query) use ($role) {
                  return $query->where('role_id', $role);
                })
                ->get();

$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

Если в таблице есть автоинкрементный 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]);

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

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();