5 способов использования «сырых» запросов к базе данных

Laravel использует великолепный механизм работы с базами данных под названием Eloquent, плюс имеет мощный Конструктор Запросов (Query Builder), но иногда необходимо написать просто «сырой» запрос (Raw Queries). В этой статье я покажу вам несколько способов, как это сделать.

1. Самый типичный: selectRaw() с вычислениями Avg/Sum/Count

Если вам нужно выполнить groupBy(), а затем использовать какую-либо агрегирующую функцию MySQL, например AVG() или COUNT(), то будет полезным выполнение «сырого» запроса для конкретной секции.

Пример из документации Laravel:

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

Другой пример:

$products = DB::table('products')
    ->leftjoin('category','category.product_id','=','products.id')
    ->selectRaw('COUNT(*) as nbr', 'products.*')
    ->groupBy('products.id')
    ->get();

Еще один пример — мы даже можем выполнить одновременно и avg(), и count().

$salaries = DB::table('salaries')
    ->selectRaw('companies.name as company_name, avg(salary) as avg_salary, count(*) as people_count')
    ->join('companies', 'salaries.company_id', '=', 'companies.id')
    ->groupBy('companies.id')
    ->orderByDesc('avg_salary')
    ->get();

2. Фильтруем Год: groupByRaw, orderByRaw и havingRaw

А если вам нужно добавить какие-либо SQL-вычисления в group by или order by? Для этого у нас есть такие методы, как groupByRaw() и orderByRaw(). Кроме того, мы можем использовать дополнительный where после группировки, используя оператор havingRaw().

Например, как сгруппировать по ГОДУ определенные поля форматов дата/время?

$results = User::selectRaw('YEAR(birth_date) as year, COUNT(id) as amount')
    ->groupByRaw('YEAR(birth_date)')
    ->havingRaw('YEAR(birth_date) > 2000')
    ->orderByRaw('YEAR(birth_date)')
    ->get();

3. Вычисление поля с помощью подзапроса: selectRaw()

Если вам нужно получить конкретный результат путем расчета данных в имеющихся полях и вы хотите, чтобы это происходило в SQL-запросе, то вот как это можно сделать:

$products = Product::select('id', 'name')
    ->selectRaw('price - discount_price AS discount')
    ->get();

Другой пример — оператор CASE:

$users = DB::table('users')
    ->select('name', 'surname')  
    ->selectRaw("(CASE WHEN (gender = 1) THEN 'M' ELSE 'F' END) as gender_text")
    ->get();

4. Старый добрый SQL-запрос? Просто используйте DB::select()

Довольно типичный пример: у вас есть SQL-выражение из старого проекта, и вам нужно использовать его в Laravel. Тут поможет DB::select().

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

5. DB::Statement() — обычно для миграций

Если вам нужно выполнить какой-либо SQL-запрос без обработки результата, например, INSERT или UPDATE без параметров, то вы можете использовать DB::Statement().

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

DB::statement('UPDATE users SET role_id = 1 WHERE role_id IS NULL AND YEAR(created_at) > 2020');

Кроме того, DB::Statement() может выполнить любой SQL-запрос для управления таблицами и базами данных.

DB::statement('DROP TABLE users');
DB::statement('ALTER TABLE projects AUTO_INCREMENT=123');

Предупреждение: будьте осторожны, всегда проверяйте параметры

Самая большая опасность сырых запросов заключается в том, что они не защищаются автоматически, поэтому, если вы передаете в запрос какие-либо параметры, пожалуйста, трижды их проверьте! Убедитесь в валидности их значений (например, что это число, а не строка) и формата.

Автор: Povilas Korop
Перевод: Алексей Широков