Laravel. Работа с БД. Часть первая — сырой запрос

20.11.2020

Теги: Laravel • MySQL • PHP • Web-разработка • БазаДанных • Запрос • Класс • Таблица • Теория • Фреймворк

Сырой запрос (Raw Query)

У Laravel для работы с базой данных есть механизм под названием Eloquent, плюс имеется мощный конструктор запросов (Query Builder), но иногда необходимо написать просто «сырой» запрос (Raw Query). Это можно сделать с помощью фасада Illuminate\Support\Facades\DB, который имеет методы для каждого типа запроса: select, update, insert, delete и statement.

1. Выполнение запроса SELECT

Метод фасада select() позволяет выполнить SELECT запрос: 

namespace App\Http\Controllers;

use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\DB;

class UserController extends Controller {
    /**
     * Показать список всех пользователей
     *
     * @return \Illuminate\Http\Response
     */
    public function index() {
        $users = DB::select('select * from users where admin = ?', [0]);
        return view('user.index', ['users' => $users]);
    }
}

Первый аргумент метода select() — сырой SQL-запрос, второй — значения параметров для прикрепления к запросу. Обычно это значения для формирования условия WHERE. Привязка параметров обеспечивает защиту от SQL-инъекций. Метод select()возвращает массив объектов stdClass. 

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

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

$user = DB::select('select * from users where id = :id', ['id' => 1]);

2. Выполнение запроса INSERT

Метод фасада insert() позволяет выполнить INSERT запрос: 

DB::insert('insert into users (id, name, email) values (?, ?, ?)', [1, 'Сергей Иванов', 'ivanov.s@mail.ru']);
DB::insert(
    'insert into users (id, name, email) values (:id, :name, :email)',
    ['id' => 1, 'name' => 'Сергей Иванов',  'email' => 'ivanov.s@mail.ru']
);

3. Выполнение запроса UPDATE

Для обновления существующих записей используется метод update(), который возвращает количество изменённых записей: 

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

4. Выполнение запроса DELETE

Для удаления записей из БД используется метод delete(), который возвращает количество изменённых записей: 

$deleted = DB::delete('delete from users where id = :id', ['id' => 1]);

5. Выполнение запроса общего типа

Некоторые запросы к БД не возвращают никаких значений. Для операций такого типа предназначен метод statement() фасада: 

DB::statement('drop table users');

6. Транзакции

Для выполнения набора запросов внутри одной транзакции предназначен метод transaction(). Если в функции-замыкании произойдёт исключение, транзакция автоматически откатится. А если функция выполнится успешно, транзакция автоматически применится. 

DB::transaction(function () {
    DB::table('users')->update(['votes' => 1]);
    DB::table('posts')->delete();
});

Метод transaction() принимает второй необязательный аргумент, с помощью которого задаётся число повторных попыток транзакции при возникновении взаимной блокировки. После истечения этих попыток будет выброшено исключение: 

DB::transaction(function () {
    DB::table('users')->update(['votes' => 1]);
    DB::table('posts')->delete();
}, 5);

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

DB::beginTransaction();

Можно откатить транзакцию методом rollBack(): 

DB::rollBack();

Можно применить транзакцию методом commit(): 

DB::commit();

Конструктор запроса (Query Builder)

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

1. Получение всех записей таблицы

Метод table() возвращает экземпляр конструктора запросов для данной таблицы, позволяя в дальнейшем «прицепить» к запросу дополнительные условия и в итоге получить результат методом get(). 

namespace App\Http\Controllers;

use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\DB;

class UserController extends Controller {
    /**
     * Показать список всех пользователей
     *
     * @return \Illuminate\Http\Response
     */
    public function index() {
        $users = DB::table('users')->get();
        return view('user.index', ['users' => $users]);
    }
}

Метод get() возвращает объект Illuminate\Support\Collection c результатами, в котором каждый результат — это экземпляр класса stdClass. 

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

2. Получение одной строки или столбца

Метод first() возвращает одну строку таблицы базы данных в виде объекта stdClass: 

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

Если не нужна вся строка, можно извлечь только одно значение методом value(): 

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

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

$emails = DB::table('users')->pluck('email');

foreach ($emails as $email) {
    echo $email;
}

Можно указать произвольный ключ для возвращаемой коллекции: 

$users = DB::table('users')->pluck('email', 'id');

foreach ($users as $id => $email) {
    echo $email;
}

3. Разделение результатов на куски

Если необходимо обработать тысячи записей БД, можно использовать метод chunk(). Этот метод получает небольшой «кусок» результатов за раз и отправляет его в замыкание для обработки. Этот метод очень полезен для написания artisan-команд, которые обрабатывают тысячи записей. 

DB::table('users')->orderBy('id')->chunk(100, function ($users) {
    foreach ($users as $user) {
        // обработка очередной порции
    }
});

Можно остановить обработку последующих «кусков» вернув false из замыкания: 

DB::table('users')->orderBy('id')->chunk(100, function ($users) {
    // обработка очередной порции
    return false;
});

4. Использование агрегатных функций

Конструктор запросов содержит множество агрегатных методов — count(), max(), min(), avg() и sum(). 

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

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

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

5. Указание столбцов для выборки

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

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

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

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

Если уже есть экземпляр конструктора и нужно добавить столбец к существующему набору: 

$query = DB::table('users')->select('name');
$users = $query->addSelect('email')->get();

6. Сырые выражения

Иногда может понадобиться использовать уже готовое SQL-выражение в запросе: 

$users = DB::table('users')
    ->select(DB::raw('count(*) as user_count, status'))
    ->where('status', '<>', 1)
    ->groupBy('status')
    ->get();

7. Объединение таблиц (JOIN)

Для выполнения объединения INNER JOIN предназначен метод join() конструктора запросов. Первый аргумент метода join() — имя таблицы, которую необходимо присоединить, а остальные аргументы указывают условия для присоединения. 

$userOrders = DB::table('users')
    ->join('profiles', 'users.id', '=', 'profiles.user_id')
    ->join('orders', 'users.id', '=', 'orders.user_id')
    ->select('users.name', 'users.email', 'profiles.phone', 'orders.amount')
    ->where('users.id', '=', auth()->user()->id)
    ->where('profiles.default', '=', true)
    ->get();
SELECT
    `users`.`name`, `users`.`email`, `profiles`.`phone`, `orders`.`amount`
FROM
    `users`
    INNER JOIN `profiles` ON `users`.`id` = `profiles`.`user_id`
    INNER JOIN `orders` on `users`.`id` = `orders`.`user_id`
WHERE
    `users`.`id` = 2 AND `profiles`.`default` = 1
Illuminate\Support\Collection Object
(
    [items:protected] => Array
        (
            [0] => stdClass Object
                (
                    [name] => Сергей Иванов
                    [email] => ivanov.s@mail.ru
                    [phone] => +7 (926) 111-11-11
                    [amount] => 4895.00
                )
            [1] => stdClass Object
                (
                    [name] => Сергей Иванов
                    [email] => ivanov.s@mail.ru
                    [phone] => +7 (926) 111-11-11
                    [amount] => 4331.00
                )
            [2] => stdClass Object
                (
                    [name] => Сергей Иванов
                    [email] => ivanov.s@mail.ru
                    [phone] => +7 (926) 111-11-11
                    [amount] => 10200.00
                )
        )
)

Таблица profiles содержит профили пользователя — те данные, которые необходимы для оформления заказа в магазине (телефон и адрес доставки). Мы получаем все заказы пользователя, объединяя таблицы users, orders и profiles. Из таблицы users берем имя и адрес почты, из таблицы profiles — номер телефона, а из таблицы orders — сумму каждого заказа. Поскольку профилей может несколько, мы берем данные из основного профиля.

Чтобы создать более сложные условия объединения таблиц, нужно передать вторым аргументом метода join() функцию-замыкание: 

$userOrders = DB::table('users')
    ->join('profiles', function ($join) {
        $join->on('users.id', '=', 'profiles.user_id')
            ->where('users.id', '=', auth()->user()->id)
            ->where('profiles.default', '=', true);
    })
    ->join('orders', function ($join) {
        $join->on('users.id', '=', 'orders.user_id');
    })
    ->select('users.name', 'users.email', 'profiles.phone', 'orders.amount')
    ->where('orders.amount', '>', 10000)
    ->get();
SELECT
    `users`.`name`, `users`.`email`, `profiles`.`phone`, `orders`.`amount`
FROM
    `users`
    INNER JOIN `profiles` ON `users`.`id` = `profiles`.`user_id` AND `users`.`id` = 2 AND `profiles`.`default` = 1
    INNER JOIN `orders` ON `users`.`id` = `orders`.`user_id`
WHERE
    `orders`.`amount` > 10000
Illuminate\Support\Collection Object
(
    [items:protected] => Array
        (
            [0] => stdClass Object
                (
                    [name] => Сергей Иванов
                    [email] => ivanov.s@mail.ru
                    [phone] => +7 (926) 111-11-11
                    [amount] => 10200.00
                )
        )
)

Это практически тот же запрос, только мы отбираем не все заказы пользователя, а только те, сумма которых больше 10000 рублей.

Для выполнения объединения LEFT JOIN предназначен метод leftJoin(), который имеет ту же сигнатуру, что и метод join().

Поиск: Laravel • MySQL • PHP • Web-разработка • База данных • Запрос • Таблица • Фреймворк • Теория • Класс • Raw Query

Каталог оборудования
Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.
Производители
Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.
Функциональные группы
Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.