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

• Вступ
• Виконання запитів до бази даних
  • Розбиття Результатів на Частини
  • Відкладене потокове передавання результатів
  • Агрегатні функції
• Інструкції Select
• Сирі вирази
• Об'єднання
• Основні оператори Where
  • Where
  • Or Where
  • Where Not
  • Any / All / None
  • JSON Умови Where
  • Додаткові Where Умови
  • Логічне групування
• • Where Exists
  • Підзапити в умовах Where
  • Повнотекстові умови Where
• Сортування, Групування, Ліміт та Зміщення
  • Сортування
  • Групування
  • Ліміт і Зміщення
• Умовні оператори
• Інструкції Insert
  • Upserts
• Інструкції Update
  • Оновлення JSON стовпців
  • Інкремент і Декремент
• Видалення операторів
• Песимістичне блокування
• Компоненти запитів, що повторно використовуються
• Налагодження

Вступ

Конструктор запитів бази даних 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;

Якщо ви хочете отримати один рядок з таблиці бази даних, але викинути Illuminate\Database\RecordNotFoundException, якщо не знайдено жодного відповідного рядка, ви можете використовувати метод firstOrFail. Якщо RecordNotFoundException не перехоплено, 404 HTTP-відповідь автоматично відправляється назад клієнту:

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

Якщо вам не потрібен весь рядок, ви можете витягти одне значення з запису, використовуючи метод 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]);
        }
    });

Оскільки методи chunkById та lazyById додають власні умови "where" до запиту, що виконується, зазвичай слід логічно групувати власні умови в межах замикання:

DB::table('users')->where(function ($query) {
    $query->where('credits', 1)->orWhere('credits', 2);
})->chunkById(100, function (Collection $users) {
    foreach ($users as $user) {
        DB::table('users')
            ->where('id', $user->id)
            ->update(['credits' => 3]);
    }
});

Відкладене потокове передавання результатів

Метод lazy працює подібно до методу chunk в тому сенсі, що виконує запит частинами. Однак, замість передачі кожної частини у зворотний виклик, метод 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

Ви можете не завжди хотіти вибирати всі стовпці з таблиці бази даних. Використовуючи метод 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();

Якщо у вас вже є екземпляр конструктора запитів і ви бажаєте додати стовпець до його існуючого select виразу, ви можете використати метод 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();

Методи Raw

Замість використання методу 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();

Об'єднання

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 для виконання "перехресного з'єднання". Перехресні з'єднання генерують декартів добуток між першою таблицею та приєднаною таблицею:

$sizes = DB::table('sizes')
    ->crossJoin('colors')
    ->get();

Розширені Умови Об'єднання

Ви також можете вказати більш складні умови об'єднання. Щоб почати, передайте замикання як другий аргумент методу join. Замикання отримає екземпляр Illuminate\Database\Query\JoinClause, що дозволяє вам вказати обмеження на умову "join":

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 для виконання "бічного з'єднання" з підзапитом. Кожен з цих методів отримує два аргументи: підзапит та його псевдонім таблиці. Умову(и) з'єднання слід вказувати в межах 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 для об'єднання його з іншими запитами:

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 конструктора запитів, умови "where" будуть об'єднані за допомогою оператора and. Однак, ви можете використовувати метод orWhere, щоб приєднати умову до запиту за допомогою оператора or. Метод orWhere приймає ті ж аргументи, що і метод where:

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

Якщо вам потрібно згрупувати умову "або" в дужках, ви можете передати замикання як перший аргумент методу orWhere:

use Illuminate\Database\Query\Builder;
 
$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 Not

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

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

Where Any / All / None

Іноді вам може знадобитися застосувати ті самі обмеження запиту до кількох стовпців. Наприклад, ви можете захотіти отримати всі записи, де будь-які стовпці в заданому списку 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%'
)

Метод whereNone може бути використаний для отримання записів, де жодна з вказаних колонок не відповідає заданому обмеженню:

$posts = DB::table('albums')
    ->where('published', true)
    ->whereNone([
        'title',
        'lyrics',
        'tags',
    ], 'like', '%explicit%')
    ->get();

Запит вище призведе до наступного SQL:

SELECT *
FROM albums
WHERE published = true AND NOT (
    title LIKE '%explicit%' OR
    lyrics LIKE '%explicit%' OR
    tags LIKE '%explicit%'
)

JSON Умови Where

Laravel також підтримує запити до стовпців типу JSON у базах даних, які надають підтримку для стовпців типу JSON. Наразі це включає MariaDB 10.3+, MySQL 8.0+, PostgreSQL 12.0+, SQL Server 2017+ та SQLite 3.39.0+. Щоб виконати запит до стовпця JSON, використовуйте оператор ->:

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

Ви можете використовувати whereJsonContains для запитів до JSON масивів:

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

Якщо ваш застосунок використовує бази даних MariaDB, 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 Умови

whereLike / orWhereLike / whereNotLike / orWhereNotLike

Метод whereLike дозволяє додавати до вашого запиту умови "LIKE" для пошуку за шаблоном. Ці методи забезпечують незалежний від бази даних спосіб виконання запитів на відповідність рядків з можливістю перемикання чутливості до регістру. За замовчуванням відповідність рядків не чутлива до регістру:

$users = DB::table('users')
    ->whereLike('name', '%John%')
    ->get();

Ви можете увімкнути пошук з урахуванням регістру за допомогою аргументу caseSensitive:

$users = DB::table('users')
    ->whereLike('name', '%John%', caseSensitive: true)
    ->get();

Метод orWhereLike дозволяє додати "або" умову з LIKE умовою:

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

Метод whereNotLike дозволяє додавати до вашого запиту умови "NOT LIKE":

$users = DB::table('users')
    ->whereNotLike('name', '%John%')
    ->get();

Аналогічно, ви можете використовувати orWhereNotLike для додавання "або" умови з NOT LIKE умовою:

$users = DB::table('users')
    ->where('votes', '>', 100)
    ->orWhereNotLike('name', '%John%')
    ->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
)

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

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

wherePast / whereFuture / whereToday / whereBeforeToday / whereAfterToday

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

$invoices = DB::table('invoices')
    ->wherePast('due_at')
    ->get();
 
$invoices = DB::table('invoices')
    ->whereFuture('due_at')
    ->get();

Методи whereNowOrPast та whereNowOrFuture можуть бути використані для визначення, чи значення стовпця знаходиться в минулому або майбутньому, включаючи поточну дату та час:

$invoices = DB::table('invoices')
    ->whereNowOrPast('due_at')
    ->get();
 
$invoices = DB::table('invoices')
    ->whereNowOrFuture('due_at')
    ->get();

Методи whereToday, whereBeforeToday та whereAfterToday можуть бути використані для визначення, чи значення стовпця є сьогодні, до сьогодні або після сьогодні, відповідно:

$invoices = DB::table('invoices')
    ->whereToday('due_at')
    ->get();
 
$invoices = DB::table('invoices')
    ->whereBeforeToday('due_at')
    ->get();
 
$invoices = DB::table('invoices')
    ->whereAfterToday('due_at')
    ->get();

Аналогічно, методи whereTodayOrBefore та whereTodayOrAfter можуть бути використані для визначення, чи значення стовпця є до сьогоднішнього дня або після сьогоднішнього дня, включаючи сьогоднішню дату:

$invoices = DB::table('invoices')
    ->whereTodayOrBefore('due_at')
    ->get();
 
$invoices = DB::table('invoices')
    ->whereTodayOrAfter('due_at')
    ->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 інструктує конструктор запитів почати групу обмежень. Замикання отримає екземпляр конструктора запитів, який ви можете використовувати для встановлення обмежень, що повинні бути вміщені в групу в дужках. Наведений вище приклад створить наступний 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" до запиту для стовпців, які мають повнотекстові індекси. Ці методи будуть перетворені у відповідний SQL для базової системи бази даних за допомогою Laravel. Наприклад, для застосунків, що використовують MariaDB або 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();

Напрямок сортування є необов'язковим і за замовчуванням є зростаючим. Якщо ви хочете сортувати в порядку спадання, ви можете вказати другий параметр для методу orderBy, або просто використати orderByDesc:

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

Нарешті, використовуючи оператор ->, результати можуть бути відсортовані за значенням у стовпці JSON:

$corporations = DB::table('corporations')
    ->where('country', 'US')
    ->orderBy('location->state')
    ->get();

Методи latest та oldest

Методи latest та oldest дозволяють легко впорядковувати результати за датою. За замовчуванням, результат буде впорядковано за стовпцем таблиці created_at. Або ви можете передати назву стовпця, за яким бажаєте сортувати:

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

Випадкове впорядкування

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

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

Видалення існуючих впорядкувань

Метод reorder видаляє всі умови "order by", які раніше були застосовані до запиту:

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

Ви можете передати стовпець і напрямок при виклику методу reorder, щоб видалити всі існуючі клаузи "order by" і застосувати абсолютно новий порядок до запиту:

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

Для зручності, ви можете використовувати метод reorderDesc для зміни порядку результатів запиту на спадний:

$query = DB::table('users')->orderBy('name');
 
$usersOrderedByEmail = $query->reorderDesc('email')->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.

Ліміт і Зміщення

Ви можете використовувати методи limit та offset, щоб обмежити кількість результатів, що повертаються з запиту, або пропустити задану кількість результатів у запиті:

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

Умовні оператори

Іноді ви можете захотіти, щоб певні умови запиту застосовувалися до запиту на основі іншої умови. Наприклад, ви можете захотіти застосувати оператор where лише якщо певне вхідне значення присутнє у вхідному HTTP-запиті. Ви можете досягти цього, використовуючи метод when:

$role = $request->input('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' => 'example@example.com',
    'votes' => 0
]);

Ви можете вставити кілька записів одночасно, передавши масив масивів. Кожен масив представляє запис, який слід вставити в таблицю:

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

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

DB::table('users')->insertOrIgnore([
    ['id' => 1, 'email' => 'example@example.com'],
    ['id' => 2, 'email' => 'example@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' => 'example@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, Laravel оновить стовпець price цього запису.

Інструкції Update

На додаток до вставки записів у базу даних, конструктор запитів також може оновлювати існуючі записи за допомогою методу update. Метод update, як і метод insert, приймає масив пар стовпців і значень, що вказують на стовпці, які потрібно оновити. Метод update повертає кількість змінених рядків. Ви можете обмежити запит update за допомогою умов where:

$affected = DB::table('users')
    ->where('id', 1)
    ->update(['votes' => 1]);

Оновити або Вставити

Іноді ви можете захотіти оновити існуючий запис у базі даних або створити його, якщо відповідний запис не існує. У цьому випадку можна використовувати метод updateOrInsert. Метод updateOrInsert приймає два аргументи: масив умов, за якими знаходиться запис, і масив пар стовпців та значень, що вказують на стовпці, які потрібно оновити.

Метод updateOrInsert спробує знайти відповідний запис у базі даних, використовуючи пари стовпців і значень першого аргументу. Якщо запис існує, він буде оновлений значеннями з другого аргументу. Якщо запис не вдасться знайти, буде вставлено новий запис з об'єднаними атрибутами обох аргументів:

DB::table('users')
    ->updateOrInsert(
        ['email' => 'example@example.com', 'name' => 'John'],
        ['votes' => '2']
    );

Ви можете надати замикання методу updateOrInsert, щоб налаштувати атрибути, які оновлюються або вставляються в базу даних на основі існування відповідного запису:

DB::table('users')->updateOrInsert(
    ['user_id' => $user_id],
    fn ($exists) => $exists ? [
        'name' => $data['name'],
        'email' => $data['email'],
    ] : [
        'name' => $data['name'],
        'email' => $data['email'],
        'marketable' => true,
    ],
);

Оновлення JSON стовпців

Коли оновлюєте стовпець JSON, слід використовувати синтаксис -> для оновлення відповідного ключа в JSON-об'єкті. Ця операція підтримується в MariaDB 10.3+, 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();

Песимістичне блокування

Конструктор запитів також включає кілька функцій, які допоможуть вам досягти "песимістичного блокування" при виконанні ваших select операторів. Щоб виконати оператор з "спільним блокуванням", ви можете викликати метод sharedLock. Спільне блокування запобігає зміні вибраних рядків, доки ваша транзакція не буде зафіксована:

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

Альтернативно, ви можете використовувати метод lockForUpdate. Блокування "for update" запобігає зміні вибраних записів або їх вибору з іншим спільним блокуванням:

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

Хоча це не обов'язково, рекомендується обгортати песимістичні блокування в межах транзакції. Це гарантує, що отримані дані залишаються незмінними в базі даних до завершення всієї операції. У разі невдачі транзакція відкотить будь-які зміни та автоматично зніме блокування:

DB::transaction(function () {
    $sender = DB::table('users')
        ->lockForUpdate()
        ->find(1);
 
    $receiver = DB::table('users')
        ->lockForUpdate()
        ->find(2);
 
    if ($sender->balance < 100) {
        throw new RuntimeException('Balance too low.');
    }
 
    DB::table('users')
        ->where('id', $sender->id)
        ->update([
            'balance' => $sender->balance - 100
        ]);
 
    DB::table('users')
        ->where('id', $receiver->id)
        ->update([
            'balance' => $receiver->balance + 100
        ]);
});

Компоненти багаторазових запитів

Якщо у вашому застосунку є повторювана логіка запитів, ви можете винести цю логіку в багаторазові об'єкти, використовуючи методи tap та pipe конструктора запитів. Уявіть, що у вашому застосунку є ці два різні запити:

use Illuminate\Database\Query\Builder;
use Illuminate\Support\Facades\DB;
 
$destination = $request->query('destination');
 
DB::table('flights')
    ->when($destination, function (Builder $query, string $destination) {
        $query->where('destination', $destination);
    })
    ->orderByDesc('price')
    ->get();
 
// ...
 
$destination = $request->query('destination');
 
DB::table('flights')
    ->when($destination, function (Builder $query, string $destination) {
        $query->where('destination', $destination);
    })
    ->where('user', $request->user()->id)
    ->orderBy('destination')
    ->get();

Ви можете захотіти витягти фільтрацію призначення, яка є спільною між запитами, в багаторазовий об'єкт:

<?php
 
namespace App\Scopes;
 
use Illuminate\Database\Query\Builder;
 
class DestinationFilter
{
    public function __construct(
        private ?string $destination,
    ) {
        //
    }
 
    public function __invoke(Builder $query): void
    {
        $query->when($this->destination, function (Builder $query) {
            $query->where('destination', $this->destination);
        });
    }
}

Потім, ви можете використовувати метод tap конструктора запитів, щоб застосувати логіку об'єкта до запиту:

use App\Scopes\DestinationFilter;
use Illuminate\Database\Query\Builder;
use Illuminate\Support\Facades\DB;
 
DB::table('flights')
    ->when($destination, function (Builder $query, string $destination) { 
        $query->where('destination', $destination); 
    }) 
    ->tap(new DestinationFilter($destination)) 
    ->orderByDesc('price')
    ->get();
 
// ...
 
DB::table('flights')
    ->when($destination, function (Builder $query, string $destination) { 
        $query->where('destination', $destination); 
    }) 
    ->tap(new DestinationFilter($destination)) 
    ->where('user', $request->user()->id)
    ->orderBy('destination')
    ->get();

Запити через Pipes

Метод tap завжди повертатиме конструктор запитів. Якщо ви хочете отримати об'єкт, який виконує запит і повертає інше значення, ви можете використовувати метод pipe замість цього.

Розгляньте наступний об'єкт запиту, що містить спільну логіку пагінації, яка використовується впродовж усього застосунку. На відміну від DestinationFilter, який застосовує умови запиту до запиту, об'єкт Paginate виконує запит і повертає екземпляр пагінатора:

<?php
 
namespace App\Scopes;
 
use Illuminate\Contracts\Pagination\LengthAwarePaginator;
use Illuminate\Database\Query\Builder;
 
class Paginate
{
    public function __construct(
        private string $sortBy = 'timestamp',
        private string $sortDirection = 'desc',
        private string $perPage = 25,
    ) {
        //
    }
 
    public function __invoke(Builder $query): LengthAwarePaginator
    {
        return $query->orderBy($this->sortBy, $this->sortDirection)
            ->paginate($this->perPage, pageName: 'p');
    }
}

Використовуючи метод pipe конструктора запитів, ми можемо використовувати цей об'єкт для застосування нашої спільної логіки пагінації:

$flights = DB::table('flights')
    ->tap(new DestinationFilter($destination))
    ->pipe(new Paginate);

Налагодження

Ви можете використовувати методи 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();