База данных: Конструктор запросов

• Введение
• Выполнение запросов к базе данных
  • Обработка результатов по частям
  • Потоковая обработка результатов
  • Агрегатные функции
• Операторы SELECT
• Необработанные выражения
• Объединения таблиц (Joins)
• Объединения запросов (Unions)
• Основные условия WHERE
  • Условия WHERE
  • Условия OR WHERE
  • Отрицательные условия WHERE
  • Условия WHERE для любого / всех
  • Условия WHERE для JSON
  • Дополнительные условия WHERE
  • Логическая группировка
• Расширенные условия WHERE
  • Условия WHERE EXISTS
  • Подзапросы в WHERE
  • Полнотекстовые условия WHERE
• Сортировка, группировка, лимит и смещение
  • Сортировка
  • Группировка
  • Лимит и смещение
• Условные операторы
• Операторы INSERT
  • Обновление или вставка (Upserts)
• Операторы UPDATE
  • Обновление JSON-столбцов
  • Инкремент и декремент
• Операторы DELETE
• Пессимистическая блокировка
• Отладка

#Введение

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

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

#Выполнение запросов к базе данных

#Получение всех строк из таблицы

Вы можете использовать метод table, предоставляемый фасадом DB, чтобы начать запрос. Метод table возвращает экземпляр конструктора запросов для указанной таблицы, позволяя добавлять дополнительные ограничения к запросу и затем получить результаты с помощью метода get:

<?php

namespace App\Http\Controllers;

use Illuminate\Support\Facades\DB;
use Illuminate\View\View;

class UserController extends Controller
{
    /**
     * Показать список всех пользователей приложения.
     */
    public function index(): View
    {
        $users = DB::table('users')->get();

        return view('user.index', ['users' => $users]);
    }
}

Метод get возвращает экземпляр Illuminate\Support\Collection, содержащий результаты запроса, где каждый результат — это объект PHP stdClass. Вы можете получить доступ к значению каждого столбца, обращаясь к нему как к свойству объекта:

use Illuminate\Support\Facades\DB;

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

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

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

Если вам нужно получить только одну строку из таблицы базы данных, вы можете использовать метод first фасада DB. Этот метод вернёт один объект stdClass:

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

return $user->email;

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

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

Чтобы получить одну строку по значению столбца id, используйте метод find:

$user = DB::table('users')->find(3);

#Получение списка значений столбца

Если вы хотите получить экземпляр Illuminate\Support\Collection, содержащий значения одного столбца, вы можете использовать метод pluck. В этом примере мы получим коллекцию заголовков пользователей:

use Illuminate\Support\Facades\DB;

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

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

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

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

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

#Обработка результатов по частям

Если вам нужно работать с тысячами записей из базы данных, рассмотрите возможность использования метода chunk, предоставляемого фасадом DB. Этот метод извлекает небольшие части результатов и передаёт каждую часть в замыкание для обработки. Например, получим всю таблицу users частями по 100 записей:

use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;

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

Вы можете остановить обработку дальнейших частей, вернув false из замыкания:

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

    return false;
});

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

DB::table('users')->where('active', false)
    ->chunkById(100, function (Collection $users) {
        foreach ($users as $user) {
            DB::table('users')
                ->where('id', $user->id)
                ->update(['active' => true]);
        }
    });

#Потоковая обработка результатов

Метод lazy работает аналогично методу chunk, выполняя запрос частями. Однако вместо передачи каждой части в callback, метод lazy() возвращает LazyCollection, позволяющую работать с результатами как с единым потоком:

use Illuminate\Support\Facades\DB;

DB::table('users')->orderBy('id')->lazy()->each(function (object $user) {
    // ...
});

Если вы планируете обновлять записи во время итерации, лучше использовать методы lazyById или lazyByIdDesc. Эти методы автоматически разбивают результаты на страницы по первичному ключу:

DB::table('users')->where('active', false)
    ->lazyById()->each(function (object $user) {
        DB::table('users')
            ->where('id', $user->id)
            ->update(['active' => true]);
    });

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

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

use Illuminate\Support\Facades\DB;

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

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

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

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

#Проверка существования записей

Вместо использования метода count для проверки существования записей, соответствующих условиям запроса, вы можете использовать методы exists и doesntExist:

if (DB::table('orders')->where('finalized', 1)->exists()) {
    // ...
}

if (DB::table('orders')->where('finalized', 1)->doesntExist()) {
    // ...
}

#Операторы SELECT

#Указание списка выбираемых столбцов

Не всегда нужно выбирать все столбцы из таблицы. С помощью метода select вы можете указать собственный список столбцов для выборки:

use Illuminate\Support\Facades\DB;

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

Метод distinct позволяет заставить запрос возвращать только уникальные результаты:

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

Если у вас уже есть экземпляр конструктора запросов и вы хотите добавить столбец к существующему списку выбираемых, используйте метод 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();

#Методы для необработанных выражений

Вместо использования метода DB::raw вы также можете использовать следующие методы для вставки необработанных выражений в разные части запроса. Помните, Laravel не гарантирует защиту от SQL-инъекций при использовании необработанных выражений.

#selectRaw

Метод selectRaw можно использовать вместо addSelect(DB::raw(/* ... */)). Он принимает необязательный массив привязок вторым аргументом:

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

Конструктор запросов также можно использовать для добавления операторов соединения к вашим запросам. Чтобы выполнить простое «внутреннее соединение», вы можете использовать метод join у экземпляра конструктора запросов. Первый аргумент, передаваемый методу join, — это имя таблицы, к которой нужно присоединиться, а оставшиеся аргументы задают ограничения по столбцам для соединения. Вы даже можете соединять несколько таблиц в одном запросе:

use Illuminate\Support\Facades\DB;

$users = DB::table('users')
            ->join('contacts', 'users.id', '=', 'contacts.user_id')
            ->join('orders', 'users.id', '=', 'orders.user_id')
            ->select('users.*', 'contacts.phone', 'orders.price')
            ->get();

#Левое / правое объединение (Left Join / Right Join)

Если нужно выполнить "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. В замыкание передаётся экземпляр Illuminate\Database\Query\JoinClause, позволяющий задавать ограничения для объединения:

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

Если хотите использовать условие "where" в объединениях, используйте методы where и orWhere объекта JoinClause. Вместо сравнения двух столбцов эти методы сравнивают столбец со значением:

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

#Объединения с подзапросами

Можно использовать методы joinSub, leftJoinSub и rightJoinSub, чтобы присоединить запрос к подзапросу. Каждый из этих методов принимает три аргумента: подзапрос, его псевдоним таблицы и замыкание, которое определяет связанные столбцы. В этом примере мы получим коллекцию пользователей, в каждой записи которых будет присутствовать временная метка created_at самого недавно опубликованного поста блога этого пользователя:

$latestPosts = DB::table('posts')
                   ->select('user_id', DB::raw('MAX(created_at) as last_post_created_at'))
                   ->where('is_published', true)
                   ->groupBy('user_id');

$users = DB::table('users')
        ->joinSub($latestPosts, 'latest_posts', function (JoinClause $join) {
            $join->on('users.id', '=', 'latest_posts.user_id');
        })->get();

#Латеральные объединения (Lateral Joins)

Методы joinLateral и leftJoinLateral позволяют выполнить "lateral join" с подзапросом. Каждый метод принимает два аргумента: подзапрос и его псевдоним. Условия объединения задаются в where подзапроса. Латеральные объединения выполняются для каждой строки и могут ссылаться на столбцы вне подзапроса.

В этом примере мы получим коллекцию пользователей и три их последних блога. Каждый пользователь может иметь до трёх строк в результате: по одной на каждый из последних блогов. Условие объединения задаётся с помощью whereColumn в подзапросе, ссылаясь на текущую строку пользователя:

$latestPosts = DB::table('posts')
                   ->select('id as post_id', 'title as post_title', 'created_at as post_created_at')
                   ->whereColumn('user_id', 'users.id')
                   ->orderBy('created_at', 'desc')
                   ->limit(3);

$users = DB::table('users')
            ->joinLateral($latestPosts, 'latest_posts')
            ->get();

#Объединения запросов (Unions)

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

use Illuminate\Support\Facades\DB;

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

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

Кроме метода union, конструктор запросов предоставляет метод unionAll. Запросы, объединённые с помощью unionAll, не удаляют дубликаты результатов. Метод unionAll имеет такую же сигнатуру, как и union.

#Основные условия WHERE

#Условия WHERE

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

Например, следующий запрос выбирает пользователей, у которых значение столбца votes равно 100, а значение столбца age больше 35:

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

Для удобства, если вы хотите проверить, что столбец равен заданному значению, вы можете передать значение вторым аргументом методу where. Laravel будет считать, что вы хотите использовать оператор =:

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

Как уже упоминалось, вы можете использовать любой оператор, поддерживаемый вашей базой данных:

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

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

$users = DB::table('users')
                ->where('name', 'like', 'T%')
                ->get();

Вы также можете передать массив условий в метод where. Каждый элемент массива должен быть массивом из трёх аргументов, обычно передаваемых в метод where:

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

#Условия OR WHERE

При последовательном вызове метода where условия объединяются оператором and. Однако метод orWhere позволяет добавить условие с оператором or. Метод orWhere принимает те же аргументы, что и where:

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

Если нужно сгруппировать условие "or" в скобках, передайте замыкание первым аргументом метода orWhere:

$users = DB::table('users')
            ->where('votes', '>', 100)
            ->orWhere(function (Builder $query) {
                $query->where('name', 'Abigail')
                      ->where('votes', '>', 50);
            })
            ->get();

Приведённый пример сгенерирует следующий SQL:

select * from users where votes > 100 or (name = 'Abigail' and votes > 50)

#Отрицательные условия WHERE

Методы whereNot и orWhereNot позволяют отрицать группу условий. Например, следующий запрос исключает товары, которые находятся на распродаже или имеют цену меньше десяти:

$products = DB::table('products')
                ->whereNot(function (Builder $query) {
                    $query->where('clearance', true)
                          ->orWhere('price', '<', 10);
                })
                ->get();

#Условия WHERE для любого / всех столбцов

Иногда нужно применить одинаковые ограничения запроса к нескольким столбцам. Например, вы можете захотеть получить все записи, где любой столбец из заданного списка совпадает с заданным значением по шаблону LIKE. Это можно сделать с помощью метода whereAny:

$users = DB::table('users')
            ->where('active', true)
            ->whereAny([
                'name',
                'email',
                'phone',
            ], 'LIKE', 'Example%')
            ->get();

Этот запрос сгенерирует следующий SQL:

SELECT *
FROM users
WHERE active = true AND (
    name LIKE 'Example%' OR
    email LIKE 'Example%' OR
    phone LIKE 'Example%'
)

Аналогично, метод whereAll позволяет получить записи, где все указанные столбцы соответствуют условию:

$posts = DB::table('posts')
            ->where('published', true)
            ->whereAll([
                'title',
                'content',
            ], 'LIKE', '%Laravel%')
            ->get();

Этот запрос сгенерирует следующий SQL:

SELECT *
FROM posts
WHERE published = true AND (
    title LIKE '%Laravel%' AND
    content LIKE '%Laravel%'
)

#Условия WHERE для JSON

Laravel поддерживает запросы к JSON-столбцам в базах данных, которые это позволяют. В настоящее время это MySQL 5.7+, PostgreSQL, SQL Server 2016 и SQLite 3.39.0 (с расширением JSON1). Для запроса к JSON-столбцу используйте оператор ->:

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

Для поиска по JSON-массивам используйте метод whereJsonContains:

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

whereBetweenColumns / whereNotBetweenColumns / orWhereBetweenColumns / orWhereNotBetweenColumns

Метод whereBetweenColumns проверяет, что значение столбца находится между значениями двух других столбцов в той же строке:

$patients = DB::table('patients')
                       ->whereBetweenColumns('weight', ['minimum_allowed_weight', 'maximum_allowed_weight'])
                       ->get();

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

$patients = DB::table('patients')
                       ->whereNotBetweenColumns('weight', ['minimum_allowed_weight', 'maximum_allowed_weight'])
                       ->get();

whereIn / whereNotIn / orWhereIn / orWhereNotIn

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

$users = DB::table('users')
                    ->whereIn('id', [1, 2, 3])
                    ->get();

Метод whereNotIn проверяет, что значение указанного столбца не содержится в заданном массиве:

$users = DB::table('users')
                    ->whereNotIn('id', [1, 2, 3])
                    ->get();

Вы также можете передать объект запроса в качестве второго аргумента метода whereIn:

$activeUsers = DB::table('users')->select('id')->where('is_active', 1);

$users = DB::table('comments')
                    ->whereIn('user_id', $activeUsers)
                    ->get();

Пример выше сгенерирует следующий SQL-запрос:

select * from comments where user_id in (
    select id
    from users
    where is_active = 1
)

whereNull / whereNotNull / orWhereNull / orWhereNotNull

Метод whereNull проверяет, что значение указанного столбца равно NULL:

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

Метод whereNotNull проверяет, что значение столбца не равно NULL:

$users = DB::table('users')
                ->whereNotNull('updated_at')
                ->get();

whereDate / whereMonth / whereDay / whereYear / whereTime

Метод whereDate используется для сравнения значения столбца с датой:

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

Метод whereMonth используется для сравнения значения столбца с конкретным месяцем:

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

Метод whereDay используется для сравнения значения столбца с конкретным днём месяца:

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

Метод whereYear используется для сравнения значения столбца с конкретным годом:

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

Метод whereTime используется для сравнения значения столбца с конкретным временем:

$users = DB::table('users')
                ->whereTime('created_at', '=', '11:20:45')
                ->get();

whereColumn / orWhereColumn

Метод whereColumn используется для проверки равенства двух столбцов:

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

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

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

Вы можете передать массив сравнений столбцов в метод whereColumn. Эти условия будут объединены оператором and:

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

#Логическое группирование

Иногда необходимо сгруппировать несколько условий "where" в скобках для правильного логического объединения в запросе. На практике рекомендуется всегда группировать вызовы метода orWhere в скобках, чтобы избежать неожиданных результатов. Для этого можно передать замыкание в метод where:

$users = DB::table('users')
           ->where('name', '=', 'John')
           ->where(function (Builder $query) {
               $query->where('votes', '>', 100)
                     ->orWhere('title', '=', 'Admin');
           })
           ->get();

Как видно, передача замыкания в метод where указывает конструктору запросов начать группу условий. В замыкание передаётся экземпляр query builder, с помощью которого можно задать условия, которые будут заключены в скобки. Пример выше сгенерирует следующий SQL:

select * from users where name = 'John' and (votes > 100 or title = 'Admin')

#Расширенные условия Where

#Условия Where Exists

Метод whereExists позволяет писать SQL-условия "where exists". Метод whereExists принимает замыкание, которому будет передан экземпляр конструктора запросов, что даёт возможность определить запрос, который будет помещён внутрь выражения "exists":

$users = DB::table('users')
           ->whereExists(function (Builder $query) {
               $query->select(DB::raw(1))
                     ->from('orders')
                     ->whereColumn('orders.user_id', 'users.id');
           })
           ->get();

Вместо замыкания вы можете передать объект запроса в метод whereExists:

$orders = DB::table('orders')
                ->select(DB::raw(1))
                ->whereColumn('orders.user_id', 'users.id');

$users = DB::table('users')
                    ->whereExists($orders)
                    ->get();

Оба приведённых примера сгенерируют следующий SQL:

select * from users
where exists (
    select 1
    from orders
    where orders.user_id = users.id
)

#Условия Where с подзапросами

Иногда нужно построить условие "where", сравнивающее результат подзапроса с заданным значением. Это можно сделать, передав замыкание и значение в метод where. Например, следующий запрос выберет всех пользователей с недавним членством определённого типа:

use App\Models\User;
use Illuminate\Database\Query\Builder;

$users = User::where(function (Builder $query) {
    $query->select('type')
        ->from('membership')
        ->whereColumn('membership.user_id', 'users.id')
        ->orderByDesc('membership.start_date')
        ->limit(1);
}, 'Pro')->get();

Или можно построить условие "where", сравнивающее столбец с результатом подзапроса. Для этого передайте столбец, оператор и замыкание в метод where. Например, следующий запрос выберет все записи доходов, где сумма меньше среднего:

use App\Models\Income;
use Illuminate\Database\Query\Builder;

$incomes = Income::where('amount', '<', function (Builder $query) {
    $query->selectRaw('avg(i.amount)')->from('incomes as i');
})->get();

#Условия полнотекстового поиска Where

Методы whereFullText и orWhereFullText позволяют добавить полнотекстовые условия where для столбцов с полнотекстовыми индексами. Laravel преобразует эти методы в соответствующий SQL для используемой базы данных. Например, для MySQL будет сгенерирован оператор MATCH AGAINST:

$users = DB::table('users')
           ->whereFullText('bio', 'web developer')
           ->get();

#Сортировка, группировка, лимит и смещение

#Сортировка

#Метод orderBy

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

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

Чтобы отсортировать по нескольким столбцам, вызовите orderBy столько раз, сколько нужно:

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

#Методы latest и oldest

Методы latest и oldest позволяют легко отсортировать результаты по дате. По умолчанию сортировка происходит по столбцу created_at. Можно передать имя другого столбца для сортировки:

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

#Случайная сортировка

Метод inRandomOrder сортирует результаты запроса в случайном порядке. Например, можно получить случайного пользователя:

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

#Удаление существующих сортировок

Метод reorder удаляет все ранее применённые условия "order by":

$query = DB::table('users')->orderBy('name');

$unorderedUsers = $query->reorder()->get();

Вы можете передать столбец и направление в метод reorder, чтобы удалить все существующие сортировки и применить новую:

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

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

Метод when выполняет переданное замыкание только если первый аргумент равен true. Если первый аргумент равен false, замыкание выполняться не будет. Таким образом, в приведённом выше примере замыкание, переданное методу when, будет вызвано только если поле role присутствует во входящем запросе и оценивается как true.

Вы можете передать другое замыкание как третий аргумент методу when. Это замыкание выполнится только если первый аргумент оценится как false. Чтобы показать, как можно использовать эту возможность, мы используем её для настройки порядка по умолчанию в запросе:

$sortByVotes = $request->boolean('sort_by_votes');

$users = DB::table('users')
                ->when($sortByVotes, function (Builder $query, bool $sortByVotes) {
                    $query->orderBy('votes');
                }, function (Builder $query) {
                    $query->orderBy('name');
                })
                ->get();

#Вставка записей

Конструктор запросов также предоставляет метод insert, который можно использовать для вставки записей в таблицу базы данных. Метод insert принимает массив имён столбцов и значений:

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

Можно вставить несколько записей сразу, передав массив массивов. Каждый вложенный массив представляет одну запись:

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

Метод insertOrIgnore игнорирует ошибки при вставке записей. При использовании этого метода ошибки дублирования записей будут проигнорированы, а другие ошибки могут быть проигнорированы в зависимости от СУБД. Например, insertOrIgnore обходит строгий режим MySQL:

DB::table('users')->insertOrIgnore([
    ['id' => 1, 'email' => 'sisko@example.com'],
    ['id' => 2, 'email' => 'archer@example.com'],
]);

Метод insertUsing вставляет новые записи, используя подзапрос для определения данных:

DB::table('pruned_users')->insertUsing([
    'id', 'name', 'email', 'email_verified_at'
], DB::table('users')->select(
    'id', 'name', 'email', 'email_verified_at'
)->where('updated_at', '<=', now()->subMonth()));

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

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

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

#Upserts

Метод 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 уже существует, будет обновлено поле price.

#Обновление записей

Кроме вставки записей в базу данных, конструктор запросов также может обновлять существующие записи с помощью метода 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]);

#Инкремент и декремент

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

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

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

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

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

При необходимости можно указать дополнительные столбцы для обновления во время операции инкремента или декремента:

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

Кроме того, можно увеличить или уменьшить несколько столбцов одновременно с помощью методов incrementEach и decrementEach:

DB::table('users')->incrementEach([
    'votes' => 5,
    'balance' => 100,
]);

#Удаление записей

Метод delete конструктора запросов можно использовать для удаления записей из таблицы. Метод delete возвращает количество затронутых строк. Вы можете ограничить операторы delete, добавив "where" условия перед вызовом метода delete:

$deleted = DB::table('users')->delete();

$deleted = DB::table('users')->where('votes', '>', 100)->delete();

Если нужно очистить всю таблицу, удалив все записи и сбросив автоинкрементный ID, используйте метод truncate:

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

#Очистка таблицы и PostgreSQL

При очистке таблицы в PostgreSQL применяется поведение CASCADE, что означает удаление всех связанных записей в других таблицах по внешним ключам.

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

Конструктор запросов включает методы для реализации пессимистической блокировки при выполнении select запросов. Для выполнения запроса с "shared lock" вызовите метод sharedLock. Shared lock предотвращает изменение выбранных строк до фиксации транзакции:

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

Альтернативно можно использовать метод lockForUpdate. Блокировка "for update" предотвращает изменение выбранных записей или их выборку с другим shared lock:

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

#Отладка

Во время построения запроса можно использовать методы dd и dump для вывода текущих параметров запроса и SQL. Метод dd выводит информацию и останавливает выполнение запроса, а dump выводит информацию и продолжает выполнение:

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

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

Методы dumpRawSql и ddRawSql выводят SQL-запрос с подставленными параметрами:

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

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