Laravel. Работа с БД. Часть вторая — конструктор запроса
21.11.2020
Теги: Laravel • MySQL • PHP • Web-разработка • БазаДанных • Класс • Таблица • Теория • Фреймворк
Конструктор запроса (Query Builder)
Конструктор запроса предоставляет удобный интерфейс для создания и выполнения запросов к базе данных. И может использоваться для выполнения большинства типов операций. Конструктор использует привязку параметров к запросам средствами PDO для защиты от SQL-инъекций. Нет необходимости экранировать строки перед их передачей в запрос.
8. Слияние запросов (UNION)
Можно создать начальный запрос и с помощью метода union() слить его со вторым запросом:
$first = DB::table('users') ->whereNull('first_name'); $users = DB::table('users') ->whereNull('last_name') ->union($first) ->get();
9. Условие выборки (WHERE)
Для добавления в запрос условий WHERE используйте метод where на экземпляре конструктора запросов. Самый простой вызов where() требует три аргумента: имя столбца, оператор и значение для сравнения со столбцом.
$users = DB::table('users')->select('name', 'email')->where('name', '=', 'Сергей Иванов')->get(); $users = DB::table('users')->select('name', 'email')->where('name', 'Сергей Иванов')->get();
SELECT `name`, `email` FROM `users` WHERE `name` = 'Сергей Иванов'
В метод where() также можно передать массив условий:
$users = DB::table('users') ->select('name', 'email') ->where([ ['admin', '=', false], ['name', 'like', '%Иванов%'], ])->get();
SELECT `name`, `email` FROM `users` WHERE (`admin` = 0 AND `name` LIKE '%Иванов%')
Если where() вызывается несколько раз — это означает использование AND:
$users = DB::table('users') ->select('name', 'email') ->where('admin', '=', false) ->where('name', 'like', '%Иванов%') ->get();
SELECT `name`, `email` FROM `users` WHERE `admin` = 0 AND `name` LIKE '%Иванов%'
Метод orWhere() позволяет использовать OR в условии WHERE:
$users = DB::table('users') ->select('name', 'email') ->where('email', 'like', '%ivanov%') ->orWhere('name', 'like', '%Иванов%') ->get();
SELECT `name`, `email` FROM `users` WHERE `email` LIKE '%ivanov%' OR `name` LIKE '%Иванов%'
Метод whereBetween() проверяет, что значение столбца находится в указанном интервале:
$users = DB::table('users') ->select('name', 'email') ->whereBetween('role', [1,3]) ->get();
SELECT `name`, `email` FROM `users` WHERE `role` BETWEEN 1 AND 3
Метод whereNotBetween() проверяет, что значение столбца находится вне указанного интервала:
$users = DB::table('users') ->select('name', 'email') ->whereNotBetween('role', [1,3]) ->get();
SELECT `name`, `email` FROM `users` WHERE `role` NOT BETWEEN 1 AND 3
Метод whereIn() проверяет, что значение столбца содержится в указанном массиве:
$users = DB::table('users') ->select('name', 'email') ->whereIn('id', [1,2,3]) ->get();
SELECT `name`, `email` FROM `users` WHERE `id` IN (1, 2, 3)
Метод whereNotIn() проверяет, что значение столбца содержится в указанном массиве:
$users = DB::table('users') ->select('name', 'email') ->whereNotIn('id', [1,2,3]) ->get();
SELECT `name`, `email` FROM `users` WHERE `id` NOT IN (1, 2, 3)
Метод whereNull() проверяет, что значение столбца равно NULL:
$users = DB::table('users') ->select('name', 'email') ->whereNull('deleted_at') ->get();
SELECT `name`, `email` FROM `users` WHERE `deleted_at` IS NULL
Метод whereNotNull() проверяет, что значение столбца не равно NULL:
$users = DB::table('users') ->select('name', 'email') ->whereNotNull('deleted_at') ->get();
SELECT `name`, `email` FROM `users` WHERE `deleted_at` IS NOT NULL
10. Условие выборки по дате
Метод whereDate() служит для сравнения значения столбца с указанной датой:
$users = DB::table('users') ->select('name', 'email') ->whereDate('created_at', '2020-10-09') ->get();
SELECT `name`, `email` FROM `users` WHERE DATE(`created_at`) = '2020-10-09'
Метод whereMonth() служит для сравнения значения столбца с месяцем в году:
$users = DB::table('users') ->select('name', 'email') ->whereMonth('created_at', '10') ->get();
SELECT `name`, `email` FROM `users` WHERE MONTH(`created_at`) = '10'
Метод whereDay() служит для сравнения значения столбца с днём месяца:
$users = DB::table('users') ->select('name', 'email') ->whereDay('created_at', '09') ->get();
SELECT `name`, `email` FROM `users` WHERE DAY(`created_at`) = '09'
Метод whereYear() служит для сравнения значения столбца с указанным годом:
$users = DB::table('users') ->select('name', 'email') ->whereYear('created_at', '2020') ->get();
SELECT `name`, `email` FROM `users` WHERE YEAR(`created_at`) = '2020'
11. Сравнение двух колонок
Для проверки на совпадение двух столбцов можно использовать метод whereColumn():
$users = DB::table('users') ->select('first_name', 'last_name') ->whereColumn('first_name', 'last_name') ->get();
SELECT `first_name`, `last_name` FROM `users` WHERE `first_name` = `last_name`
В метод также можно передать оператор сравнения:
$users = DB::table('users') ->select('name', 'email') ->whereColumn('updated_at', '>', 'created_at') ->get();
SELECT `name`, `email` FROM `users` WHERE `updated_at` > `created_at`
Также можно передать массив с несколькими условиями — они будут объединены оператором AND:
$users = DB::table('users') ->select('first_name', 'last_name') ->whereColumn([ ['first_name', '=', 'last_name'], ['updated_at', '>', 'created_at'] ]) ->get();
SELECT `first_name`, `last_name` FROM `users` WHERE (`first_name` = `last_name` AND `updated_at` > `created_at`)
12. Группировка условий WHERE
Иногда нужно сделать выборку по более сложной группировкой условий, например (условие OR условие) AND (условие OR условие):
$users = DB::table('users') ->select('name', 'email') ->where(function ($query) { $query->where('admin', '=', false) ->orWhere('role', '<>', 'admin'); }) ->where(function ($query) { $query->where('votes', '>', 100) ->orWhereColumn('updated_at', '>', 'created_at'); }) ->get();
SELECT
`name`, `email`
FROM
`users`
WHERE
(`admin` = 0 OR `role` <> 'admin') AND (`votes` > 100 OR `updated_at` > `created_at`)
Метод whereExists() позволяет написать SQL-условие WHERE EXISTS:
DB::table('users') ->whereExists(function ($query) { $query->select(DB::raw(1)) ->from('orders') ->where('orders.user_id', '=', 'users.id'); }) ->get();
SELECT
*
FROM
`users`
WHERE
EXISTS (SELECT 1 FROM `orders` WHERE `orders`.`user_id` = `users`.`id`)
Этим запросом мы выбираем из таблицы users только тех пользователей, которые сделали хотя бы один заказ в интернет-магазине.
13. Сортировка и группировка
Метод orderBy() позволяет отсортировать результат запроса по заданному столбцу. Первый аргумент метода — столбец для сортировки, а второй — задаёт направление сортировки и может быть либо asc, либо desc.
$users = DB::table('users') ->select('name', 'email') ->orderBy('name', 'desc') ->get();
Методы latest() и oldest() позволяют отсортировать результаты по дате. По умолчанию выполняется сортировка по столбцу created_at, но можно передать имя столбца для сортировки.
$users = DB::table('users') ->select('name', 'email') ->latest() ->first();
Для сортировки результатов запроса в случайном порядке можно использовать метод inRandomOrder():
// получить случайного пользователя $users = DB::table('users') ->select('name', 'email') ->inRandomOrder() ->first();
Методы groupBy() и having() используются для группировки результатов запроса. Тут трудно привести какой-то пример использования, потому что HAVING подразумевает фильтрацию по значениям агрегатных функций.
Метод havingRaw() используется для передачи сырой строки в условие HAVING:
buyers = DB::table('orders') ->select('name', 'email', DB::raw('SUM(amount) as total')) ->groupBy('name', 'email') ->havingRaw('SUM(amount) > 10000') ->get();
SELECT
`name`, `email`, SUM(`amount`) AS `total`
FROM
`orders`
GROUP BY
`name`, `email`
HAVING
SUM(`amount`) > 10000
Illuminate\Support\Collection Object
(
[items:protected] => Array
(
[0] => stdClass Object
(
[name] => Дмитрий Николаев
[email] => nikolaev.d@mail.ru
[total] => 15349.00
)
[1] => stdClass Object
(
[name] => Сергей Иванов
[email] => ivanov.s@mail.ru
[total] => 19824.00
)
)
)
Мы в этом запросе отбираем покупателей интернет-магазина, общая сумма покупок которых больше 10000 рублей.
Для ограничения числа возвращаемых результатов из запроса или для пропуска заданного числа результатов в запросе используются методы skip() и take().
$users = DB::table('users')->skip(10)->take(5)->get();
Или можно использовать методы limit() и offset():
$users = DB::table('users')->offset(10)->limit(5)->get();
14. Условные выражения
Иногда необходимо применять условие к запросу, только если выполняется какое-то другое условие. Например, выполнять where(), только если нужное значение есть во входящем http-запросе.
$role_id = request()->input('role_id'); $users = DB::table('users') ->select('name', 'email') ->when($role_id, function ($query) use ($role_id) { return $query->where('role_id', $role_id); }) ->get();
SELECT `name`, `email` FROM `users` WHERE `role_id` = 2
Метод when() выполняет данное замыкание, только когда первый параметр равен true. Если первый параметр равен false, то замыкание не будет выполнено.
Можно передать ещё одно замыкание третьим параметром метода when(). Это замыкание будет выполнено, если первый параметр будет иметь значение false.
$sortBy = null; $users = DB::table('users') ->select('name', 'email') ->when($sortBy, function ($query) use ($sortBy) { return $query->orderBy($sortBy); }, function ($query) { return $query->orderBy('name'); }) ->get();
- Laravel. Работа с БД. Часть первая — сырой запрос
- Laravel. Мягкое удаление (soft deletes)
- Мини-блог на Laravel, часть 1. Создание таблиц БД, наполнение тестовыми данными
- Блог на Laravel 7, часть 1. Создание таблиц БД, наполнение тестовыми данными
- Магазин на Laravel 7, часть 25. Поиск по каталогу товаров, деплой проекта на хостинг TimeWeb
- Мини-блог на Laravel, часть 4. Создание нового поста, загрузка и обрезка изображения
- Мини-блог на Laravel, часть 3. Постраничная навигация, layout-шаблон и поиск по блогу
Поиск: Laravel • MySQL • PHP • Web-разработка • База данных • Класс • Таблица • Фреймворк • Query Builder