Использование базы данных (на основе компонента базы данных Laravel)

Получение всех строк

<?php
namespace app\controller;

use support\Request;
use support\Db;

class UserController
{
    public function all(Request $request)
    {
        $users = Db::table('users')->get();
        return view('user/all', ['users' => $users]);
    }
}

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

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

Получение одной строки

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

Получение одного столбца

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

Использовать значение указанного поля id в качестве индекса

$roles = Db::table('roles')->pluck('title', 'id');

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

Получение одного значения (поля)

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

Удаление дубликатов

$email = Db::table('user')->select('nickname')->distinct()->get();

Обработка результатов порциями

Если вам нужно обработать тысячи записей в базе данных, чтение всех этих данных за один раз может занять много времени и привести к переполнению памяти. В этом случае вы можете рассмотреть возможность использования метода chunkById. Этот метод извлекает небольшую порцию результирующего набора за раз и передает ее в замыкание для обработки. Например, мы можем разделить все данные таблицы users на небольшие порции по 100 записей за раз:

Db::table('users')->orderBy('id')->chunkById(100, function ($users) {
    foreach ($users as $user) {
        //
    }
});

Вы можете остановить получение порций, вернув false в замыкании.

Db::table('users')->orderBy('id')->chunkById(100, function ($users) {
    // Обработка записей...

    return false;
});

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

Агрегаты

Конструктор запросов также предоставляет различные методы агрегации, такие как 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');

Проверка существования записей

return Db::table('orders')->where('finalized', 1)->exists();
return Db::table('orders')->where('finalized', 1)->doesntExist();

Низкоуровневые выражения

Прототип

selectRaw($expression, $bindings = [])

Иногда вам может понадобиться использовать низкоуровневые выражения в запросе. Вы можете использовать selectRaw(), чтобы создать низкоуровневое выражение:

$orders = Db::table('orders')
                ->selectRaw('price * ? as price_with_tax', [1.0825])
                ->get();

Также доступны методы низкоуровневых выражений whereRaw(), orWhereRaw(), havingRaw(), orHavingRaw(), orderByRaw(), groupByRaw().

Db::raw($value) также используется для создания низкоуровневого выражения, но у него нет функциональности привязки параметров, поэтому необходимо быть осторожным с проблемами SQL-инъекций.

$orders = Db::table('orders')
                ->select('department', Db::raw('SUM(price) as total_sales'))
                ->groupBy('department')
                ->havingRaw('SUM(price) > ?', [2500])
                ->get();

Join-выражения

// join
$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();

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

// rightJoin
$users = Db::table('users')
            ->rightJoin('posts', 'users.id', '=', 'posts.user_id')
            ->get();

// crossJoin    
$users = Db::table('sizes')
            ->crossJoin('colors')
            ->get();

Union-выражения

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

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

Where-выражения

Прототип

where($column, $operator = null, $value = null)

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

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

Вы также можете передать массив условий в функцию where:

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

Методы orWhere и where принимают одинаковые параметры:

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

Вы также можете передать замыкание в метод orWhere в качестве первого параметра:

// SQL: select * from users where votes > 100 or (name = 'Abigail' and votes > 50)
$users = Db::table('users')
            ->where('votes', '>', 100)
            ->orWhere(function($query) {
                $query->where('name', 'Abigail')
                      ->where('votes', '>', 50);
            })
            ->get();

Методы whereBetween / orWhereBetween проверяют, находится ли значение поля между двумя заданными значениями:

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

Методы whereNotBetween / orWhereNotBetween проверяют, находится ли значение поля вне заданного диапазона:

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

Методы whereIn / whereNotIn / orWhereIn / orWhereNotIn проверяют, что значение поля должно присутствовать в указанном массиве:

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

Методы whereNull / whereNotNull / orWhereNull / orWhereNotNull проверяют, должно ли указанное поле быть NULL:

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

Метод whereNotNull проверяет, должно ли указанное поле не быть NULL:

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

Методы whereDate / whereMonth / whereDay / whereYear / whereTime используются для сравнения значения поля с заданной датой:

$users = Db::table('users')
                ->whereDate('created_at', '2016-12-31')
                ->get();

Методы whereColumn / orWhereColumn используются для сравнения значений двух полей на равенство:

$users = Db::table('users')
                ->whereColumn('first_name', 'last_name')
                ->get();

// Вы также можете передать оператор сравнения
$users = Db::table('users')
                ->whereColumn('updated_at', '>', 'created_at')
                ->get();

// Метод whereColumn также может принимать массив
$users = Db::table('users')
                ->whereColumn([
                    ['first_name', '=', 'last_name'],
                    ['updated_at', '>', 'created_at'],
                ])->get();

Группировка параметров

// select * from users where name = 'John' and (votes > 100 or title = 'Admin')
$users = Db::table('users')
           ->where('name', '=', 'John')
           ->where(function ($query) {
               $query->where('votes', '>', 100)
                     ->orWhere('title', '=', 'Admin');
           })
           ->get();

whereExists

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

orderBy

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

Случайная сортировка

$randomUser = Db::table('users')
                ->inRandomOrder()
                ->first();

Случайная сортировка может значительно повлиять на производительность сервера, поэтому её не рекомендуется использовать.

groupBy / having

$users = Db::table('users')
                ->groupBy('account_id')
                ->having('account_id', '>', 100)
                ->get();
// Вы можете передать несколько параметров в метод groupBy
$users = Db::table('users')
                ->groupBy('first_name', 'status')
                ->having('account_id', '>', 100)
                ->get();

offset / limit

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

Вставка

Вставка одной записи

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

$id = Db::table('users')->insertGetId(
    ['email' => 'john@example.com', 'votes' => 0]
);

Внимание: При использовании PostgreSQL метод insertGetId по умолчанию рассматривает id как название поля с автоинкрементом. Если вы хотите получить ID из другой «последовательности», вы можете передать имя поля как второй параметр в метод insertGetId.

Обновление

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

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

Иногда вы можете захотеть обновить существующую запись в базе данных или создать ее, если соответствующей записи нет:

Db::table('users')
    ->updateOrInsert(
        ['email' => 'john@example.com', 'name' => 'John'],
        ['votes' => '2']
    );

Метод updateOrInsert сначала пытается найти соответствующую запись в базе данных, используя ключи и значения первого параметра. Если запись существует, значения из второго параметра используются для обновления записи. Если запись не найдена, будет вставлена новая запись, данные для которой будут объединением двух массивов.

Инкремент и декремент

Оба эти метода принимают как минимум один параметр: столбец, который нужно изменить. Второй параметр является необязательным и используется для управления увеличением или уменьшением значения столбца:

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

Удаление

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

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

Если вам нужно очистить таблицу, вы можете использовать метод truncate, который удалит все строки и сбросит автоинкремент ID на ноль:

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

Транзакции

Смотрите Транзакции базы данных

Пессимистическая блокировка

Конструктор запросов также включает несколько функций, которые могут помочь вам реализовать «пессимистическую блокировку» в синтаксисе select. Если вы хотите реализовать «разделяемую блокировку» в запросе, вы можете использовать метод sharedLock. Разделяемая блокировка препятствует изменению выбранных данных до тех пор, пока транзакция не будет зафиксирована:

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

Или вы можете использовать метод lockForUpdate. Использование «обновляющей» блокировки предотвращает изменение или выбор строк другими разделяемыми блокировками:

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

Отладка

Вы можете использовать dd или dump для вывода результатов запросов или SQL-выражений. Метод dd показывает информацию для отладки и завершает выполнение запроса. Метод dump также отображает информацию для отладки, но не завершает выполнение запроса:

Db::table('users')->where('votes', '>', 100)->dd();
Db::table('users')->where('votes', '>', 100)->dump();

Внимание
Для отладки необходимо установить symfony/var-dumper, команда для установки: composer require symfony/var-dumper.