Laravel - Query Builder

Вступление

Компоновщик запросов к базе данных Laravel предоставляет удобный и удобный интерфейс для создания и выполнения запросов к базе данных. Он может использоваться для выполнения большинства операций с базами данных в вашем приложении и работает на всех поддерживаемых системах баз данных.

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

PDO не поддерживает связывание имен столбцов. Поэтому вы никогда не должны позволять пользовательскому вводу указывать имена столбцов, на которые ссылаются ваши запросы, включая столбцы «упорядочить по» и т. Д. Если необходимо разрешить пользователю выбирать определенные столбцы для запроса, всегда проверяйте имена столбцов по белому. список разрешенных столбцов.

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

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

Вы можете использовать tableметод на DBфасаде, чтобы начать запрос. tableМетод возвращает беглый экземпляр построителя запросов для данной таблицы, что позволяет цепи более ограничений на запрос , а затем , наконец , получить результаты с помощью getметода:

<?php

namespace App\Http\Controllers;

use Illuminate\Support\Facades\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]);
    }
}

getМетод возвращает содержащие результаты , где каждый результат представляет собой экземпляр РНР объекта. Вы можете получить доступ к значению каждого столбца, обратившись к столбцу как к свойству объекта:Illuminate\Support\CollectionstdClass

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

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

Если вам просто нужно извлечь одну строку из таблицы базы данных, вы можете использовать firstметод. Этот метод вернет один stdClassобъект:

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

echo $user->name;

Если вам даже не нужна вся строка, вы можете извлечь одно значение из записи, используя valueметод. Этот метод будет возвращать значение столбца напрямую:

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

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

$user = DB::table('users')->find(3);

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

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

$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;
}

Результаты Chunking

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

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

Вы можете остановить обработку следующих фрагментов, вернувшись falseиз Closure:

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

    return false;
});

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

DB::table('users')->where('active', false)
    ->chunkById(100, function ($users) {
        foreach ($users as $user) {
            DB::table('users')
                ->where('id', $user->id)
                ->update(['active' => true]);
        }
    });

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

Сводные показатели

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

Определение наличия записей

Вместо того , чтобы использовать countметод , чтобы определить, какие записи существуют , которые соответствуют ограничениям вашего запроса, вы можете использовать existsи doesntExistметоды:

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

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

SELECT

Указание предложения выбора

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

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

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

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

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

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

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

Необработанные выражения

Иногда вам может понадобиться использовать необработанное выражение в запросе. Чтобы создать необработанное выражение, вы можете использовать метод:DB::raw

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

Необработанные операторы будут вставлены в запрос в виде строк, поэтому вы должны быть крайне осторожны, чтобы не создавать уязвимости SQL-инъекций.

Необработанные методы

Вместо использования вы также можете использовать следующие методы для вставки необработанного выражения в различные части вашего запроса.DB::raw

`selectRaw`

selectRawМетод может быть использован вместо . Этот метод принимает необязательный массив привязок в качестве второго аргумента:select(DB::raw(...))

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

`whereRaw / orWhereRaw`

whereRawИ orWhereRawметоды могут быть использованы для введения исходного whereположение в запросе. Эти методы принимают необязательный массив привязок в качестве второго аргумента:

$orders = DB::table('orders')
                ->whereRaw('price > IF(state = "TX", ?, 100)', [200])
                ->get();

`havingRaw / orHavingRaw`

havingRawИ orHavingRawметоды может быть использованы для установки исходной строки в качестве значения havingпункта. Эти методы принимают необязательный массив привязок в качестве второго аргумента:

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

`orderByRaw`

orderByRawМетод может быть использован для установки исходной строки в качестве значения order byпункта:

$orders = DB::table('orders')
                ->orderByRaw('updated_at - created_at DESC')
                ->get();

Joins

Inner Join Clause

Построитель запросов также может использоваться для написания операторов соединения. Чтобы выполнить базовое «внутреннее соединение», вы можете использовать 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или rightJoin. Эти методы имеют ту же сигнатуру, что и joinметод:

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

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

Кросс-соединение

Для выполнения перекрестного объединения используйте crossJoinметод с именем таблицы, к которой вы хотите выполнить перекрестное соединение. Перекрестные объединения генерируют декартово произведение между первой таблицей и объединенной таблицей:

$users = DB::table('sizes')
            ->crossJoin('colours')
            ->get();

Расширенные условия присоединения

Вы также можете указать более сложные условия соединения. Для начала передайте в Closureкачестве второго аргумента joinметод. ClosureПолучит JoinClauseобъект , который позволяет задать ограничения на joinпункте:

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

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

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

Присоединения к подзапросу

Вы можете использовать joinSub, leftJoinSubи rightJoinSubметоды , чтобы присоединиться к запросу к югу от запроса. Каждый из этих методов получает три аргумента: подзапрос, псевдоним его таблицы и Closure, который определяет связанные столбцы:

$latestPosts = DB::table('posts')
                   ->select('user_id', DB::raw('MAX(created_at) as last_post_created_at'))
                   ->where('is_published', true)
                   ->groupBy('user_id');

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

Unions

Конструктор запросов также обеспечивает быстрый способ объединения двух запросов. Например, вы можете создать начальный запрос и использовать unionметод для объединения его со вторым запросом:

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

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

unionAllМетод также доступен и имеет один и тот же метод , как подпись union.

Where

Простые Where

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

Например, вот запрос, который проверяет значение столбца «голоса», равное 100:

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

Для удобства, если вы хотите убедиться, что столбец равен заданному значению, вы можете передать значение непосредственно в качестве второго аргумента whereметоду:

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

При написании whereпредложения вы можете использовать множество других операторов :

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

OR

Вы можете объединять ограничения, а также добавлять orпредложения в запрос. orWhereМетод принимает те же аргументы, что и whereметод:

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

Дополнительные условия «Где»

whereBetween / orWhereBetween

whereBetweenМетод проверяет , что значение столбца находится между двумя значениями:

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

whereNotBetween / orWhereNotBetween

whereNotBetweenМетод проверяет , что значение столбца лежит вне двух значений:

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

whereIn / whereNotIn / orWhereIn / orWhereNotIn

whereInМетод проверяет , что значение данного столбца содержится в данном массиве:

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

whereNotInМетод проверяет , что значение данного столбца является не содержится в данном массиве:

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

whereNull / whereNotNull / илиWhereNull / илиWhereNotNull

whereNullМетод проверяет , что значение данного столбца NULL:

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

whereNotNullМетод проверяет , что значение столбца не является NULL:

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

whereDate / whereMonth / whereDay / whereYear / whereTime

Этот whereDateметод можно использовать для сравнения значения столбца с датой:

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

Этот whereMonthметод можно использовать для сравнения значения столбца с конкретным месяцем года:

$users = DB::table('users')
                ->whereMonth('created_at', '12')
                ->get();

Этот whereDayметод можно использовать для сравнения значения столбца с конкретным днем месяца:

$users = DB::table('users')
                ->whereDay('created_at', '31')
                ->get();

Этот whereYearметод можно использовать для сравнения значения столбца с конкретным годом:

$users = DB::table('users')
                ->whereYear('created_at', '2016')
                ->get();

Этот whereTimeметод можно использовать для сравнения значения столбца с определенным временем:

$users = DB::table('users')
                ->whereTime('created_at', '=', '11:20:45')
                ->get();

whereColumn / orWhereColumn

whereColumnМетод может быть использован для проверки того, что два столбец равен:

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

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

$users = DB::table('users')
                ->whereColumn('updated_at', '>', 'created_at')
                ->get();

whereColumnМетод также может быть передан массив из нескольких условий. Эти условия будут присоединены с помощью andоператора:

$users = DB::table('users')
                ->whereColumn([
                    ['first_name', '=', 'last_name'],
                    ['updated_at', '>', 'created_at']
                ])->get();

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

Иногда вам может понадобиться создать более продвинутые предложения where, такие как предложения «where there» или группировки вложенных параметров. Построитель запросов Laravel также может их обработать. Для начала давайте рассмотрим пример группировки ограничений в скобках:

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

Как видите, передача Closureв whereметод инструктирует построителя запросов начинать группу ограничений. ClosureПолучит экземпляр построителя запроса , который можно использовать , чтобы установить ограничение , которые должны содержаться в группе скобки. В приведенном выше примере будет получен следующий SQL:

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

Вы должны всегда группировать orWhereвызовы, чтобы избежать непредвиденного поведения при применении глобальных областей.

Where Exists

whereExistsМетод позволяет писать where existsположения SQL. whereExistsМетод принимает Closureаргумент, который получит экземпляр построителя запросов позволяет определить запрос , который должен быть расположен внутри «существует» оговорка:

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 Where

Laravel также поддерживает запросы типов столбцов JSON в базах данных, которые обеспечивают поддержку типов столбцов JSON. В настоящее время это включает MySQL 5.7, PostgreSQL, SQL Server 2016 и SQLite 3.9.0 (с расширением JSON1 ). Чтобы запросить столбец JSON, используйте оператор:->

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

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

Вы можете использовать whereJsonContainsдля запроса массивов JSON (не поддерживается в SQLite):

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

Поддержка MySQL и PostgreSQL whereJsonContainsс несколькими значениями:

$users = DB::table('users')
                ->whereJsonContains('options->languages', ['en', 'de'])
                ->get();

Вы можете использовать whereJsonLengthдля запроса массивов JSON по их длине:

$users = DB::table('users')
                ->whereJsonLength('options->languages', 0)
                ->get();

$users = DB::table('users')
                ->whereJsonLength('options->languages', '>', 1)
                ->get();

Ordering, Grouping, Limit, & Offset

orderBy

orderByМетод позволяет сортировать результат запроса по данному колонку. Первый аргумент orderByметода должен быть столбец вы хотите сортировать, а второй аргумент контролирует направление сортировки и может быть ascили desc:

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

latest / oldest

latestИ oldestметоды позволяют легко заказать результаты по дате. По умолчанию результат будет упорядочен по created_atстолбцу. Или вы можете передать имя столбца, по которому вы хотите отсортировать:

$user = DB::table('users')
                ->latest()
                ->first();

inRandomOrder

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

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

groupBy / having

groupByИ havingметоды могут быть использованы для группы результатов запроса. В havingподписи методы подобна тому из whereметода:

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

Для более сложных havingутверждений см. havingRawМетод.

skip / take

Для того, чтобы ограничить количество результатов , возвращаемых из запроса, или пропустить заданное число результатов в запросе, вы можете использовать skipи takeметоды:

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

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

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

Условные условия

Иногда вы можете захотеть, чтобы предложения применялись к запросу только тогда, когда что-то еще является истинным. Например, вы можете захотеть применить whereоператор, только если во входящем запросе присутствует заданное входное значение. Вы можете сделать это, используя whenметод:

$role = $request->input('role');

$users = DB::table('users')
                ->when($role, function ($query, $role) {
                    return $query->where('role_id', $role);
                })
                ->get();

whenМетод только выполняет заданную Closure , когда первый параметр true. Если первый параметр - falseЗакрытие не будет выполнено.

Вы можете передать другое Закрытие в качестве третьего параметра whenметоду. Это Закрытие будет выполнено, если первый параметр оценивается как false. Чтобы проиллюстрировать, как можно использовать эту функцию, мы будем использовать ее для настройки сортировки запроса по умолчанию:

$sortBy = null;

$users = DB::table('users')
                ->when($sortBy, function ($query, $sortBy) {
                    return $query->orderBy($sortBy);
                }, function ($query) {
                    return $query->orderBy('name');
                })
                ->get();

Вставки

Построитель запросов также предоставляет insertметод для вставки записей в таблицу базы данных. insertМетод принимает массив имен столбцов и значений:

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

Вы можете даже вставить несколько записей в таблицу одним вызовом insert, передав массив массивов. Каждый массив представляет строку для вставки в таблицу:

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

Автоинкрементные идентификаторы

Если таблица имеет автоматически увеличивающийся идентификатор, используйте insertGetIdметод для вставки записи, а затем получите идентификатор:

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

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

Updates

Помимо вставки записей в базу данных, построитель запросов также может обновлять существующие записи, используя updateметод. updateМетод, как и в insertспособе, принимает массив столбцов и пар значений , содержащих столбцы , которые будут обновлены. Вы можете ограничить updateзапрос, используя whereпредложения:

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

Update Or Insert

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

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

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

Обновление столбцов JSON

При обновлении столбца JSON вы должны использовать синтаксис для доступа к соответствующему ключу в объекте JSON. Эта операция поддерживается только в MySQL 5.7+:->

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

Increment & Decrement

Построитель запросов также предоставляет удобные методы для увеличения или уменьшения значения данного столбца. Это ярлык, предоставляющий более выразительный и лаконичный интерфейс по сравнению с написанием updateоператора вручную .

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

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

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

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

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

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

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