Конструктор Запросов Laravel 5.*

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

 

Получение результатов

Получение всех строк из таблицы

<?php

namespace App\Http\Controllers;

use DB;
use App\Http\Controllers\Controller;

class UserController extends Controller
{
    /**
     * Show a list of all of the application's users.
     *
     * @return Response
     */
    public function index()
    {
        $users = DB::table('users')->get();

        return view('user.index', ['users' => $users]);
    }
}

Получение одной строки/колонки из таблицы

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

echo $user->name;

Если из этой одной строки требуется только одна колонка

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

Разбиение результатов на части (Chunking Results From A Table)

DB::table('users')->chunk(100, function($users) {
    foreach ($users as $user) {
        //
    }
});

Остановить отбор результатов можно, возвратив false из функции-замыкания:

DB::table('users')->chunk(100, function($users) {
    // Process the records...

    return false;
});

Получение значений столбца

$titles = DB::table('roles')->pluck('title');

foreach ($titles as $title) {
    echo $title;
}

Также можно задать столбец, значения которого будут ключами для возвращаемого массива:

$roles = DB::table('roles')->pluck('title', 'name');

foreach ($roles as $name => $title) {
    echo $title;
}

Групповые функции (aggregates)

Конструктор предоставляет разнообразные групповые методы, такие как count, max, min, avg, и sum.

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

$price = DB::table('orders')->max('price');

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

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

 

Выборка данных (Selects)

Уточнение результата выборки (Specifying A Select Clause)

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

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

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

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

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

$users = $query->addSelect('age')->get();

Использование SQL (Raw Expressions)

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

 

Объединение таблиц (Joins)

Внутреннее объединение (Inner Join Statement)

$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 Statement)

$users = DB::table('users')
            ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
            ->get();

Расширенные объединения (Advanced Join Statements)

DB::table('users')
        ->join('contacts', function ($join) {
            $join->on('users.id', '=', 'contacts.user_id')->orOn(...);
        })
        ->get();

Если использовать выражение where, то доступны методы where и orWhere. Вместо сравнения двух столбцов эти методы будут сравнивать столбец с указанным значением:

DB::table('users')
        ->join('contacts', function ($join) {
            $join->on('users.id', '=', 'contacts.user_id')
                 ->where('contacts.user_id', '>', 5);
        })
        ->get();

 

Объединение результата запросов (Unions)

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

$users = DB::table('users')
            ->whereNull('last_name')
            ->union($first)
            ->get();

Также доступен метод unionAll с такой же сигнатурой как у union.

 

Условия отбора (Where Clauses)

Простые условия (Simple Where Clauses)

$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('votes', '<>', 100)
                ->get();

$users = DB::table('users')
                ->where('name', 'like', 'T%')
                ->get();

Массив условий

$users = DB::table('users')->where([
    ['status','1'],
    ['subscribed','<>','1'],
])->get();

Выражения ИЛИ (Or Statements)

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

Дополнительные условия отбора (Additional Where Clauses)

whereBetween — метод проверяет, что значение поля находится между двух указанных значений

$users = DB::table('users')
                    ->whereBetween('votes', [1, 100])->get();

whereNotBetween — метод противоположен методу whereBetween:

$users = DB::table('users')
                    ->whereNotBetween('votes', [1, 100])
                    ->get();

whereIn — значение должно находиться в указанном массиве:

$users = DB::table('users')
                    ->whereIn('id', [1, 2, 3])
                    ->get();

whereNotIn — значение не должно находиться в массиве:

$users = DB::table('users')
                    ->whereNotIn('id', [1, 2, 3])
                    ->get();

whereNull — значение поля должно быть NULL:

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

whereNotNull — значение поля не должно быть NULL:

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

 

Расширенные условия отбора (Advanced Where Clauses)

Группировка (Parameter Grouping)

DB::table('users')
            ->where('name', '=', 'John')
            ->orWhere(function ($query) {
                $query->where('votes', '>', 100)
                      ->where('title', '<>', 'Admin');
            })
            ->get();

Пример выше создаст следующий SQL запрос:

select * from users where name = 'John' or (votes > 100 and title <> 'Admin')

 

Выражения Exists (Exists Statements)

DB::table('users')
            ->whereExists(function ($query) {
                $query->select(DB::raw(1))
                      ->from('orders')
                      ->whereRaw('orders.user_id = users.id');
            })
            ->get();

Пример выше сформирует следующий запрос SQL:

select * from users
where exists (
    select 1 from orders where orders.user_id = users.id
)

 

Условия отбора для полей типа JSON (JSON Where Clauses)

Laravel поддерживает запросы к полям с типом JSON для СУБД с поддержкой этого типа. На текущий момент это MySQL 5.7 and Postgres. Для запроса к такому полю используйте оператор ->:

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

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

 

Сортировка, Группировка, Лимит, Отступ (Ordering, Grouping, Limit, & Offset)

Сортировка (orderBy)

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

Группировка (groupBy / having / havingRaw)

$users = DB::table('users')
                ->groupBy('account_id')
                ->having('account_id', '>', 100)
                ->get();

Пример SQL: найти все отделы, где продажи превысили $2500

$users = DB::table('orders')
                ->select('department', DB::raw('SUM(price) as total_sales'))
                ->groupBy('department')
                ->havingRaw('SUM(price) > 2500')
                ->get();

Отступ/лимит (skip / take)

$users = DB::table('users')->skip(10)->take(5)->get();

Ограничить количество данных в запросе

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

 

Вставка (Inserts)

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

Для вставки более одной строки

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

Получение id вставленного столбца (Auto-Incrementing IDs)

$id = DB::table('users')->insertGetId(
    ['email' => 'john@example.com', 'votes' => 0]
);
Когда используется PostgreSQL метод ожидает, что такой столбец называется id. Если требуется получать значение из другого «sequence», то его имя нужно указывать во втором аргументе метода insertGetId

 

Обновление (Updates)

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

Инкремент/Декремент (Increment / Decrement)

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

 

Удаление (Deletes)

Удаление всех строк из таблицы:

DB::table('users')->delete();

Удаление строк, соответствующих условию:

DB::table('users')->where('votes', '<', 100)->delete();

Удаление всех строк и сброс автоинкремента (очистка таблицы):

DB::table('users')->truncate();

 

Пессимистичная блокировка (Pessimistic Locking)

Для использования блокировки «shared lock» предназначен метод sharedLock. Метод предохраняет выбранные строки от изменения до завершения вашей транзакции:

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

Другой вариант использовать метод lockForUpdate. Блокировка «for update» предохраняет выбранные строки от изменения и от установки блокировки «shared lock»:

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