Query Builder · Laravel

Конструктор запитів (query builder) Laravel дає зручний плавний інтерфейс для створення й виконання запитів до бази даних. Ним можна виконати більшість операцій з базою у вашому застосунку, і він однаково добре працює з усіма СУБД, які підтримує Laravel.

Конструктор запитів Laravel використовує привʼязку параметрів PDO, щоб захистити застосунок від SQL-інʼєкцій. Рядки, які передаються в конструктор як привʼязки запиту, не потрібно окремо очищати чи екранувати.

Увага! PDO не підтримує привʼязку назв стовпців. Тому ніколи не дозволяйте користувацькому вводу визначати назви стовпців у ваших запитах, зокрема стовпців у «order by».

Виконання запитів до бази даних

Отримання всіх рядків таблиці

Щоб почати запит, скористайтеся методом 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;
}

Примітка. Колекції Laravel мають багато потужних методів для перетворення й згортання даних. Докладніше про колекції Laravel читайте в документації по колекціях.

Отримання одного рядка або стовпця з таблиці

Якщо потрібно отримати з таблиці лише один рядок, скористайтеся методом first фасаду DB. Цей метод поверне один обʼєкт stdClass:

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

return $user->email;

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

$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]);
    }
});

Увага! Коли ви оновлюєте або видаляєте записи всередині колбека chunk, будь-які зміни первинного або зовнішніх ключів можуть вплинути на запит порції. Через це частина записів може не потрапити в результати.

Ліниве потокове читання результатів

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

Увага! Сирі вирази вставляються в запит як рядки, тому будьте вкрай обережні, щоб не створити вразливість до SQL-інʼєкцій.

Сирі методи

Замість методу 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. Для звичайного «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

Якщо замість «inner join» потрібен «left join» чи «right 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

Можна задавати й складніші умови приєднання. Для цього передайте замикання другим аргументом методу 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)

Увага! Латеральні приєднання наразі підтримують PostgreSQL, MySQL >= 8.0.14 і SQL Server.

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

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

$users = DB::table('users')
    ->whereNull('last_name')
    ->union($usersWithoutFirstName)
    ->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();

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

$users = DB::table('users')->where([
    'first_name' => 'Jane',
    'last_name' => 'Doe',
])->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();

Увага! PDO не підтримує привʼязку назв стовпців. Тому ніколи не дозволяйте користувацькому вводу визначати назви стовпців у ваших запитах, зокрема стовпців у «order by».

Увага! MySQL і MariaDB автоматично приводять рядки до цілих чисел у порівняннях «рядок - число». При цьому нечислові рядки перетворюються на 0, що призводить до несподіваних результатів. Наприклад, якщо в таблиці є стовпець secret зі значенням aaa, а ви виконаєте User::where('secret', 0), цей рядок буде повернуто. Щоб цього уникнути, приводьте всі значення до відповідних типів перед використанням у запитах.

Секції or where

Коли ви ланцюжком викликаєте метод where конструктора запитів, секції «where» зʼєднуються оператором and. Проте метод orWhere приєднує умову до запиту оператором or. Метод orWhere приймає ті самі аргументи, що й where:

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

Якщо потрібно згрупувати умову «or» у дужках, передайте замикання першим аргументом методу 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)

Увага! Завжди групуйте виклики orWhere, щоб уникнути несподіваної поведінки при застосуванні глобальних скоупів.

Секції 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 дозволяє отримати записи, де жоден із заданих стовпців не відповідає умові:

$albums = 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%'
)

Секції where для JSON

Laravel підтримує запити до стовпців типу 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();

$users = DB::table('users')
    ->whereIn('preferences->dining->meal', ['pasta', 'salad', 'sandwiches'])
    ->get();

Для запитів до JSON-масивів є методи whereJsonContains і whereJsonDoesntContain:

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

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

Якщо ваш застосунок працює з MariaDB, MySQL або PostgreSQL, методам whereJsonContains і whereJsonDoesntContain можна передати масив значень:

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

$users = DB::table('users')
    ->whereJsonDoesntContain('options->languages', ['en', 'de'])
    ->get();

Крім того, методи whereJsonContainsKey і whereJsonDoesntContainKey дозволяють отримати результати, які містять або не містять певний JSON-ключ:

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

$users = DB::table('users')
    ->whereJsonDoesntContainKey('preferences->dietary_requirements')
    ->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 додає секцію «or» з умовою LIKE:

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

Метод whereNotLike додає до запиту секції «NOT LIKE»:

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

Так само orWhereNotLike додає секцію «or» з умовою NOT LIKE:

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

Увага! Опція чутливого до регістру пошуку в whereLike наразі не підтримується на SQL Server.

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

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

Приклад вище дасть такий SQL:

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

Увага! Якщо ви додаєте до запиту великий масив привʼязок цілих чисел, методи whereIntegerInRaw або whereIntegerNotInRaw суттєво зменшать споживання памʼяті.

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

whereValueBetween / whereValueNotBetween / orWhereValueBetween / orWhereValueNotBetween

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

$products = DB::table('products')
    ->whereValueBetween(100, ['min_price', 'max_price'])
    ->get();

Метод whereValueNotBetween перевіряє, що значення лежить поза значеннями двох стовпців того самого рядка таблиці:

$products = DB::table('products')
    ->whereValueNotBetween(100, ['min_price', 'max_price'])
    ->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();

whereNullSafeEquals / orWhereNullSafeEquals

Методи whereNullSafeEquals і orWhereNullSafeEquals порівнюють значення стовпця із заданим значенням, вважаючи два значення NULL рівними:

$lastLoginIp = $request->input('last_login_ip');

$users = DB::table('users')
    ->whereNullSafeEquals('last_login_ip', $lastLoginIp)
    ->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')

Увага! Завжди групуйте виклики orWhere, щоб уникнути несподіваної поведінки при застосуванні глобальних скоупів.

Складніші секції where

Секції where exists

Метод whereExists дозволяє писати SQL-секції «where exists». Він приймає замикання, яке отримує екземпляр конструктора запитів, і в ньому ви визначаєте запит, що має опинитися всередині секції «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 замикання і значення. Наприклад, наступний запит отримає всіх користувачів, у яких є свіже «membership» заданого типу:

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 для повнотекстового пошуку

Увага! Секції where для повнотекстового пошуку наразі підтримують MariaDB, MySQL і PostgreSQL.

Методи whereFullText і orWhereFullText додають до запиту повнотекстові секції «where» для стовпців, які мають повнотекстові індекси. Laravel перетворить їх на відповідний SQL для вашої СУБД. Наприклад, для застосунків на MariaDB чи MySQL буде згенеровано секцію MATCH AGAINST:

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

Секції векторної подібності

Примітка. Секції векторної подібності наразі підтримуються на зʼєднаннях PostgreSQL із розширенням pgvector і на MariaDB 11.7 та новіших. Про визначення векторних стовпців та індексів читайте в документації по міграціях.

Метод whereVectorSimilarTo фільтрує результати за косинусною подібністю до заданого вектора й упорядковує їх за релевантністю. Поріг minSimilarity має бути значенням між 0.0 і 1.0, де 1.0 означає ідентичність:

$documents = DB::table('documents')
    ->whereVectorSimilarTo('embedding', $queryEmbedding, minSimilarity: 0.4)
    ->limit(10)
    ->get();

Якщо аргументом вектора передано звичайний рядок, Laravel автоматично згенерує для нього ембединги через Laravel AI SDK:

$documents = DB::table('documents')
    ->whereVectorSimilarTo('embedding', 'Best wineries in Napa Valley')
    ->limit(10)
    ->get();

Типово whereVectorSimilarTo також сортує результати за відстанню (найподібніші першими). Це сортування можна вимкнути, передавши false в аргумент order:

$documents = DB::table('documents')
    ->whereVectorSimilarTo('embedding', $queryEmbedding, minSimilarity: 0.4, order: false)
    ->orderBy('created_at', 'desc')
    ->limit(10)
    ->get();

Якщо потрібен більший контроль, використовуйте методи selectVectorDistance, whereVectorDistanceLessThan і orderByVectorDistance окремо:

$documents = DB::table('documents')
    ->select('*')
    ->selectVectorDistance('embedding', $queryEmbedding, as: 'distance')
    ->whereVectorDistanceLessThan('embedding', $queryEmbedding, maxDistance: 0.3)
    ->orderByVectorDistance('embedding', $queryEmbedding)
    ->limit(10)
    ->get();

Для PostgreSQL розширення pgvector має бути завантажене, перш ніж можна створювати стовпці vector:

Schema::ensureVectorExtensionExists();

Сортування, групування, limit і offset

Сортування

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

Методи 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' => '[email protected]',
    'votes' => 0
]);

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

DB::table('users')->insert([
    ['email' => '[email protected]', 'votes' => 0],
    ['email' => '[email protected]', 'votes' => 0],
]);

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

DB::table('users')->insertOrIgnore([
    ['id' => 1, 'email' => '[email protected]'],
    ['id' => 2, 'email' => '[email protected]'],
]);

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

Автоінкрементні ID

Якщо таблиця має автоінкрементний id, скористайтеся методом insertGetId, щоб вставити запис і одразу отримати його ID:

$id = DB::table('users')->insertGetId(
    ['email' => '[email protected]', 'votes' => 0]
);

Увага! У PostgreSQL метод insertGetId очікує, що автоінкрементний стовпець має назву id. Якщо ви хочете отримати ID з іншої «послідовності», передайте назву стовпця другим параметром методу insertGetId.

Upsert

Метод 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.

Увага! Усі бази даних, окрім SQL Server, вимагають, щоб стовпці з другого аргументу методу upsert мали «первинний» або «унікальний» індекс. До того ж драйвери MariaDB і MySQL ігнорують другий аргумент методу upsert і завжди використовують «первинні» та «унікальні» індекси таблиці для пошуку наявних записів.

Оновлення записів

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

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

Оновлення або вставка

Іноді потрібно оновити наявний запис у базі даних або створити його, якщо відповідного запису немає. Для такого випадку є метод updateOrInsert. Він приймає два аргументи: масив умов, за якими шукати запис, і масив пар «стовпець - значення» зі стовпцями, які треба оновити.

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

DB::table('users')
    ->updateOrInsert(
        ['email' => '[email protected]', '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. Щоб виконати запит із «розділюваним блокуванням» (shared lock), викличте метод 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) { // [tl! remove]
        $query->where('destination', $destination); // [tl! remove]
    }) // [tl! remove]
    ->tap(new DestinationFilter($destination)) // [tl! add]
    ->orderByDesc('price')
    ->get();

// ...

DB::table('flights')
    ->when($destination, function (Builder $query, string $destination) { // [tl! remove]
        $query->where('destination', $destination); // [tl! remove]
    }) // [tl! remove]
    ->tap(new DestinationFilter($destination)) // [tl! add]
    ->where('user', $request->user()->id)
    ->orderBy('destination')
    ->get();

Конвеєри запитів

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