Banco de dados: construtor de consultas

Introdução

O construtor de consultas do banco de dados Laravel fornece uma interface conveniente e fluente para criação e execução de consultas ao banco de dados. Pode ser usado para realizar a maioria das operações do banco de dados em sua aplicação e funciona perfeitamente com todos os sistemas de banco de dados suportados pelo Laravel.

O construtor de consulta do Laravel utiliza vinculação de parâmetros PDO para proteger seu aplicativo contra ataques de injeção de SQL. Não há necessidade de limpar ou sanear strings passadas para o construtor de consultas como associações por exemplo.

ALERTA

O PDO não oferece suporte à vinculação de nomes de colunas. Por esta razão, você nunca deve permitir que entradas do usuário citem os nomes das colunas referenciados em suas consultas, incluindo colunas "order by".

Executando consultas de banco de dados

Recuperando todas as linhas de uma tabela

Você pode usar o método table fornecido pela facade DB para iniciar uma consulta. O método table retorna um construtor de consulta fluente para a tabela especificada, permitindo que você encadeie mais restrições na consulta e finalmente recupere os resultados usando o método get:

    <?php

    namespace App\Http\Controllers;

    use Illuminate\Support\Facades\DB;
    use Illuminate\View\View;

    class UserController extends Controller
    {
        /**
         * Exibe uma lista de todos os usuários do aplicativo.
         */
        public function index(): View
        {
            $users = DB::table('users')->get();

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

O método get retorna uma instância de Illuminate\Support\Collection que contém os resultados da consulta em que cada resultado é uma instância do objeto PHP stdClass. Você pode acessar o valor de cada coluna acessando-a como propriedade do objeto:

    use Illuminate\Support\Facades\DB;

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

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

NOTA

Laravel Collections oferece um grande número de métodos extremamente poderosos para mapear e reduzir dados. Para mais informações sobre o Laravel Collections, veja a documentação em coleções

Recuperando uma única linha/coluna de uma tabela

Se você precisa apenas recuperar uma única linha de uma tabela do banco de dados, você pode usar o método first da classe DB. Este método retornará um único objeto stdClass:

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

    return $user->email;

Se você não precisa de uma linha inteira, você pode extrair um valor único de um registro usando o método value. Esse método retornará o valor da coluna diretamente:

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

Para recuperar uma única linha pelo valor da coluna id, utilize o método find:

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

Recuperando uma Lista de Valores da Coluna

Se você gostaria de recuperar uma instância de Illuminate\Support\Collection contendo os valores de uma única coluna, você pode usar o método pluck. Neste exemplo, vamos buscar uma coleção de títulos de usuário:

    use Illuminate\Support\Facades\DB;

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

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

Você pode especificar a coluna que o resultado da coleção deve usar como chaves fornecendo um segundo argumento para o método pluck:

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

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

Resultados em chunking

Se você precisa trabalhar com milhares de registros em um banco de dados, considere usar o método chunk fornecido pela facade DB. Este método recupera um pequeno pedaço de resultados de cada vez e alimenta cada pedaço em uma função para processamento. Por exemplo, vamos recuperar a tabela users inteira em pedaços de 100 registros cada:

    use Illuminate\Support\Collection;
    use Illuminate\Support\Facades\DB;

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

Você pode parar a execução de mais pedaços retornando false do closure:

    DB::table('users')->orderBy('id')->chunk(100, function (Collection $users) {
        // Processa os registros...

        return false;
    });

Se você está atualizando registros de banco de dados enquanto os resultados são agrupados, seus resultados agrupados podem mudar de maneiras inesperadas. Se você planeja atualizar os registros recuperados enquanto os resultados são agrupados, é sempre melhor usar o método chunkById em vez disso. Este método paginará automaticamente os resultados com base na chave primária do registro:

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

ALERTA

Ao atualizar ou excluir registros dentro do retorno de chamada do chunk, qualquer alteração na chave primária ou chaves estrangeiras pode afetar a consulta em chunk. Isso pode potencialmente resultar em registros não incluídos nos resultados em pedaços.

Resultados por streaming lazily (preguiçosos)

O método lazy() funciona de maneira similar ao método chunk na medida em que executa a consulta em pedaços. No entanto, em vez de passar cada pedaço para uma função de retorno de chamada, o lazy() retorna um LazyCollection, permitindo que você interaja com os resultados como um único fluxo:

use Illuminate\Support\Facades\DB;

DB::table('users')->orderBy('id')->lazy()->each(function (object $user) {
    // ...
});

Mais uma vez, se você planeja atualizar os registros recuperados enquanto iteram sobre eles, é melhor usar o método lazyById ou lazyByIdDesc em vez disso. Esses métodos paginarão automaticamente os resultados com base na chave primária do registro:

DB::table('users')->where('active', false)
    ->lazyById()->each(function (object $user) {
        DB::table('users')
            ->where('id', $user->id)
            ->update(['active' => true]);
    });

ALERTA

Ao atualizar ou excluir registros enquanto iteramos sobre eles, qualquer alteração na chave primária ou chaves estrangeiras pode afetar a consulta em partes. Isso pode potencialmente resultar em registros não incluídos nos resultados.

Agregados

O construtor de consultas também fornece uma variedade de métodos para recuperar valores agregados como count, max, min, avg e sum. Você pode chamar qualquer um desses métodos após construir sua consulta.

    use Illuminate\Support\Facades\DB;

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

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

É claro que você pode combinar esses métodos com outras cláusulas para ajustar como o seu valor agregado é calculado.

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

Determinar se Registros Existem

Em vez de usar o método count para determinar se há algum registro que corresponda aos critérios da sua consulta, você pode utilizar os métodos exists e doesntExist:

    if (DB::table('orders')->where('finalized', 1)->exists()) {
        // ...
    }

    if (DB::table('orders')->where('finalized', 1)->doesntExist()) {
        // ...
    }

Declarações Selecionadas

Especificando uma Cláusula Selecionada

Você pode não querer selecionar todas as colunas de uma tabela do banco de dados. Usando o método select, você pode especificar um "select" personalizado para a consulta:

    use Illuminate\Support\Facades\DB;

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

O método distinct permite forçar a consulta a retornar resultados distintos:

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

Se você já tem uma instância de um construtor de consulta e deseja adicionar uma coluna a sua cláusula SELECT existente, você pode usar o método addSelect:

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

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

Expressões cruas

Às vezes, você pode precisar inserir uma sequência arbitrária em uma consulta. Para criar uma expressão de string crua, você pode usar o método raw fornecido pela facade DB:

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

ATENÇÃO

As declarações brutas serão injetadas na consulta como strings, então você deve ser extremamente cuidadoso para evitar criar vulnerabilidades de injeção SQL.

Métodos Brutos

Em vez de usar o método DB::raw, você também pode usar os seguintes métodos para inserir uma expressão bruta em diferentes partes da sua consulta. Lembre-se, o Laravel não pode garantir que qualquer consulta usando expressões brutas é protegida contra vulnerabilidades de injeção SQL.

selectRaw

O método selectRaw pode ser usado no lugar de addSelect(DB::raw(/* ... */)). Este método aceita um array opcional de vinculações como segundo argumento:

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

whereRaw / orWhereRaw

Os métodos whereRaw e orWhereRaw podem ser usados para injetar uma cláusula where bruta na sua consulta. Esses métodos aceitam um array opcional de associações como seu segundo argumento:

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

havingRaw / orHavingRaw

Os métodos havingRaw e orHavingRaw podem ser usados para fornecer uma string bruta como o valor da cláusula "having". Estes métodos aceitam um array opcional de vinculações no segundo argumento:

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

orderByRaw

O método orderByRaw pode ser usado para fornecer uma string bruta como o valor da cláusula "ordenar por" (order by):

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

groupByRaw

O método groupByRaw pode ser usado para fornecer uma string bruta como o valor da cláusula GROUP BY:

    $orders = DB::table('orders')
                    ->select('city', 'state')
                    ->groupByRaw('city, state')
                    ->get();

Junções (Joins)

A cláusula inner join

O construtor de consulta também pode ser usado para adicionar cláusulas JOIN às suas consultas. Para realizar um "INNER JOIN" básico, você pode usar o método join em uma instância do construtor de consulta. O primeiro argumento passado ao método join é o nome da tabela que você precisa unir à sua tabela atual, enquanto os argumentos restantes especificam as restrições de coluna para a junção. Você até pode unir múltiplas tabelas em uma única consulta:

    use Illuminate\Support\Facades\DB;

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

Cláusula de Junção Esquerda / Direita

Se você quiser executar um "left join" ou um "right join" em vez de um "inner join", utilize o método leftJoin ou 'rightJoin'. Estes métodos têm a mesma assinatura do método 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();

Cross Join / Junção cruzada

Você pode usar o método crossJoin para realizar um "cross join". Cross joins geram um produto cartesiano entre a primeira tabela e a tabela unida:

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

Cláusulas de Junção Avançadas

Você também pode especificar cláusulas de junção mais avançadas. Para começar, passe um closure como o segundo argumento para o método 'join'. O closure receberá uma instância de Illuminate/Database/Query/JoinClause que permite especificar restrições na cláusula join:

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

Se você quiser usar uma cláusula where em suas junções, você pode usar os métodos where e orWhere fornecidos pela instância JoinClause. Em vez de comparar duas colunas, esses métodos irão comparar a coluna com um valor.

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

Junções de subconsulta

Você pode usar os métodos joinSub, leftJoinSub e rightJoinSub para juntar uma consulta a uma subconsulta. Cada um desses métodos recebe três argumentos: a subconsulta, seu nome de tabela e uma função lambda que define as colunas relacionadas. Neste exemplo, vamos obter uma coleção de usuários onde cada registro do usuário também contém o carimbo de data e hora created_at do último blog post publicado pelo usuário:

    $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 (JoinClause $join) {
                $join->on('users.id', '=', 'latest_posts.user_id');
            })->get();

Junções Laterais

ALERTA

As junções laterais atualmente são suportadas pelo PostgreSQL, MySQL >= 8.0.14 e SQL Server.

Você pode usar os métodos joinLateral e leftJoinLateral para realizar uma "lateral join" com uma subconsulta. Cada um desses métodos recebe dois argumentos: a subconsulta e sua tabela alias. As condições de junção(s) devem ser especificadas dentro da cláusula where da subconsulta dada. Junções laterais são avaliadas por linha, e podem referir colunas fora da subconsulta.

Neste exemplo, vamos buscar uma coleção de usuários assim como as três últimas postagens no blog. Cada usuário pode produzir até três linhas na tabela de resultados: uma para cada um das postagens do blog mais recentes. A condição de junção é especificada com a cláusula whereColumn dentro da subconsulta, referenciando a linha atual do usuário:

    $latestPosts = DB::table('posts')
                       ->select('id as post_id', 'title as post_title', 'created_at as post_created_at')
                       ->whereColumn('user_id', 'users.id')
                       ->orderBy('created_at', 'desc')
                       ->limit(3);

    $users = DB::table('users')
                ->joinLateral($latestPosts, 'latest_posts')
                ->get();

Unions / União

A construção de consultas também fornece um método conveniente para a "união" de duas ou mais consultas juntas. Por exemplo, você pode criar uma consulta inicial e usar o método unionP para uni-la com mais consultas:

    use Illuminate\Support\Facades\DB;

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

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

Além do método union, o construtor de consultas fornece um método unionAll. Consultas que são combinadas usando o método unionAll não terão seus resultados duplicados removidos. O método unionAll tem a mesma assinatura de método como o union.

Cláusulas básicas de where

Cláusulas Where

Você pode usar o método where do construtor de consultas para acrescentar where. A chamada básica do método where requer três argumentos. O primeiro argumento é o nome da coluna. O segundo argumento é um operador, que pode ser qualquer um dos operadores suportados pelo banco de dados. O terceiro argumento é o valor para comparação contra o valor da coluna.

Por exemplo, a seguinte consulta recupera os usuários onde o valor da coluna votes é igual a 100 e o valor da coluna age é maior que 35:

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

Para conveniência, caso queira verificar se uma coluna é igual a um determinado valor, você pode passar o valor como segundo argumento para o método where. O Laravel presumirá que você quer usar o operador =:

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

Como já mencionado, você pode utilizar qualquer operador que seu sistema de banco de dados suporte:

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

Você também pode passar uma matriz de condições para o método where. Cada elemento da matriz deve ser uma matriz que contenha os três argumentos tipicamente passados para o método where:

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

ATENÇÃO

O PDO não suporta nomes de coluna vinculados. Portanto, nunca permita que a entrada do usuário dicte os nomes da coluna referenciada em suas consultas, incluindo colunas "order by".

Cláusulas or Where

Quando encadeamos chamadas para o método where do construtor de consulta, as cláusulas where serão encadeadas usando o operador and. No entanto, você pode usar o método orWhere para unir uma cláusula à consulta usando o operador or. O método orWhere aceita os mesmos argumentos que o método where:

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

Se você precisa agrupar uma condição "or" dentro de parênteses, você pode passar um closure como o primeiro argumento para o método 'orWhere':

    $users = DB::table('users')
                ->where('votes', '>', 100)
                ->orWhere(function (Builder $query) {
                    $query->where('name', 'Abigail')
                          ->where('votes', '>', 50);
                })
                ->get();

O exemplo acima produzirá o seguinte SQL:

select * from users where votes > 100 or (name = 'Abigail' and votes > 50)

ATENÇÃO

Você deve agrupar as chamadas orWhere para evitar um comportamento inesperado quando escopos globais são aplicados.

Cláusulas where not

Os métodos whereNot e orWhereNot podem ser usados para negar um determinado grupo de restrições de consulta. Por exemplo, a seguinte consulta exclui produtos que estão em liquidação ou os quais têm um preço inferior a dez:

    $products = DB::table('products')
                    ->whereNot(function (Builder $query) {
                        $query->where('clearance', true)
                              ->orWhere('price', '<', 10);
                    })
                    ->get();

Cláusulas Where Any / All

Às vezes você pode precisar aplicar as mesmas restrições de consulta a várias colunas. Por exemplo, você pode querer buscar todos os registros onde qualquer coluna em uma determinada lista é LIKE um determinado valor. Você pode fazer isso usando o método whereAny:

    $users = DB::table('users')
                ->where('active', true)
                ->whereAny([
                    'name',
                    'email',
                    'phone',
                ], 'LIKE', 'Example%')
                ->get();

A consulta acima produzirá o seguinte SQL:

SELECT *
FROM users
WHERE active = true AND (
    name LIKE 'Example%' OR
    email LIKE 'Example%' OR
    phone LIKE 'Example%'
)

Da mesma forma, o método whereAll pode ser usado para obter registros onde todas as colunas fornecidas correspondam a uma determinada restrição:

    $posts = DB::table('posts')
                ->where('published', true)
                ->whereAll([
                    'title',
                    'content',
                ], 'LIKE', '%Laravel%')
                ->get();

A consulta acima produz a seguinte SQL:

SELECT *
FROM posts
WHERE published = true AND (
    title LIKE '%Laravel%' AND
    content LIKE '%Laravel%'
)

Cláusulas JSON Where

O Laravel também suporta consultar colunas de tipo JSON em bancos que fornecem suporte para o tipo de coluna JSON. Atualmente, isso inclui MySQL 8.0+, PostgreSQL 12.0+, SQL Server 2017+, e SQLite 3.39.0+ (com a extensão JSON1). Para consultar uma coluna de tipo JSON, use o operador ->:

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

Você pode usar o comando whereJsonContains para fazer pesquisa de arrays em formato JSON:

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

Se sua aplicação utilizar bancos de dados MySQL ou PostgreSQL, você pode passar um array de valores para o método whereJsonContains:

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

Você pode usar o método whereJsonLength para consultar matrizes de JSON por sua extensão:

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

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

Cláusulas Adicionais where

whereBetween / orWhereBetween

O método WhereBetween verifica se o valor de uma coluna está entre dois valores:

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

whereNotBetween / orWhereNotBetween

O método whereNotBetween verifica que o valor de uma coluna está fora de dois valores:

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

whereBetweenColumns / whereNotBetweenColumns / orWhereBetweenColumns / orWhereNotBetweenColumns

O método whereBetweenColumns verifica que o valor de uma coluna se encontra entre os dois valores de duas colunas na mesma linha da tabela.

    $patients = DB::table('patients')
                           ->whereBetweenColumns('weight', ['minimum_allowed_weight', 'maximum_allowed_weight'])
                           ->get();

O método whereNotBetweenColumns verifica que o valor de uma coluna se encontra fora dos valores de duas colunas na mesma linha da tabela.

    $patients = DB::table('patients')
                           ->whereNotBetweenColumns('weight', ['minimum_allowed_weight', 'maximum_allowed_weight'])
                           ->get();

whereIn / whereNotIn / orWhereIn / orWhereNotIn

O método whereIn verifica se o valor de uma coluna dada está contido em um determinado array.

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

O método whereNotIn verifica que o valor da coluna especificada não está contido no array fornecido:

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

Você também pode fornecer um objeto de consulta como segundo argumento do método whereIn:

    $activeUsers = DB::table('users')->select('id')->where('is_active', 1);

    $users = DB::table('comments')
                        ->whereIn('user_id', $activeUsers)
                        ->get();

O exemplo acima produzirá o seguinte SQL:

select * from comments where user_id in (
    select id
    from users
    where is_active = 1
)

ATENÇÃO

Se você estiver adicionando uma grande matriz de variáveis inteiras para sua consulta, o método whereIntegerInRaw ou whereIntegerNotInRaw pode ser usado para reduzir muito seu uso de memória.

whereNull / whereNotNull / orWhereNull / orWhereNotNull

O método whereNull verifica que o valor da coluna fornecida seja NULL:

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

O método whereNotNull verifica que o valor da coluna não é NULL:

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

whereDate / whereMonth / whereDay / whereYear / whereTime

O método whereDate pode ser usado para comparar um valor de coluna com uma data:

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

O método whereMonth pode ser usado para comparar um valor de uma coluna com um mês específico:

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

O método whereDay pode ser utilizado para comparar o valor de uma coluna com um dia específico do mês:

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

A função whereYear pode ser usada para comparar um valor de coluna com um ano específico:

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

O método whereTime pode ser usado para comparar um valor de coluna em relação a uma hora específica:

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

whereColumn / orWhereColumn

O método whereColumn pode ser usado para verificar que duas colunas são iguais:

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

Você também pode passar um operador de comparação para o método whereColumn:

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

Você também pode passar um array de comparações de coluna para o método whereColumn. Essas condições serão unidas usando o operador and:

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

Agrupamento Lógico

Às vezes você pode precisar agrupar várias cláusulas "where" dentro de parênteses para alcançar o agrupamento lógico desejado da sua consulta. Na verdade, você deve geralmente agrupar as chamadas ao método orWhere dentro de parênteses para evitar o comportamento inesperado da consulta. Para fazer isso, você pode passar uma closure ao método where:

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

Como você pode ver, passando uma consulta para o método where instrui o construtor de consultas a começar um grupo de restrições. A consulta receberá uma instância do construtor de consultas que você poderá usar para definir as restrições que devem ser contidas dentro do grupo de parênteses. O exemplo acima produziria o seguinte SQL:

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

ATENÇÃO

Você deve agrupar as chamadas de orWhere para evitar comportamentos inesperados quando escopos globais são aplicados.

Cláusulas avançadas de where

Cláusulas where exists

O método whereExists permite que você escreva as cláusulas WHERE EXISTS. O método whereExists aceita um closure, que receberá uma instância do query builder, permitindo-lhe definir a consulta que deve ser colocada dentro da cláusula exist:

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

Alternativamente, você pode fornecer um objeto de consulta para o método whereExists em vez de uma função anônima:

    $orders = DB::table('orders')
                    ->select(DB::raw(1))
                    ->whereColumn('orders.user_id', 'users.id');

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

Ambos os exemplos acima produzirão o seguinte SQL:

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

Cláusulas da subconsulta WHERE

Às vezes você pode precisar construir uma cláusula where que compara os resultados de uma subconsulta com um determinado valor. Você pode fazer isso passando um closure e um valor para o método where. Por exemplo, a seguinte consulta irá retornar todos os usuários que têm um recente "membro" do tipo dado:

    use App\Models\User;
    use Illuminate\Database\Query\Builder;

    $users = User::where(function (Builder $query) {
        $query->select('type')
            ->from('membership')
            ->whereColumn('membership.user_id', 'users.id')
            ->orderByDesc('membership.start_date')
            ->limit(1);
    }, 'Pro')->get();

Ou, você pode precisar construir uma cláusula "where" que compara uma coluna aos resultados de uma subconsulta. Você pode conseguir isso passando uma coluna, operador e closure para o método 'where'. Por exemplo, a seguinte consulta irá recuperar todos os registros amount onde a quantidade é menor que a média;

    use App\Models\Income;
    use Illuminate\Database\Query\Builder;

    $incomes = Income::where('amount', '<', function (Builder $query) {
        $query->selectRaw('avg(i.amount)')->from('incomes as i');
    })->get();

Cláusulas Where Full Text

ATENÇÃO

Cláusulas full text (texto completo) são atualmente suportadas pelo MySQL e o PostgreSQL.

Os métodos whereFullText e orWhereFullText podem ser utilizados para acrescentar cláusulas "where" de texto completo em uma consulta para colunas que possuem índices de texto completo. Laravel irá converter estes métodos no SQL apropriado para o sistema de banco de dados subjacente. Por exemplo, um MATCH AGAINST será gerado para aplicações utilizando MySQL:

    $users = DB::table('users')
               ->whereFullText('bio', 'web developer')
               ->get();

Ordenar, Agrupar, Limitar e Deslocar

Ordernar

Método orderBy

O método orderBy permite que você classifique os resultados da consulta por uma coluna específica. O primeiro argumento aceito pelo orderBy deve ser a coluna pela qual deseja classificar os resultados, enquanto o segundo argumento determina a direção da classificação e pode ser ou 'asc' ou 'desc':

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

Para classificar por múltiplas colunas, você pode simplesmente invocar orderBy quantas vezes forem necessárias:

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

Métodos latest e oldest

O método latest e oldest permitem ordenar os resultados por data com facilidade. Por padrão, o resultado será ordenado pela coluna created_at da tabela. Ou, você pode passar o nome da coluna que deseja classificar:

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

Ordenação aleatória

O método InRandomOrder pode ser usado para ordenar os resultados da consulta em ordem aleatória. Por exemplo, você poderia usar este método para obter um usuário aleatório:

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

Removendo as Ordenações Existentes

O método reorder remove todas as cláusulas "ORDER BY" que foram previamente aplicadas à consulta:

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

    $unorderedUsers = $query->reorder()->get();

Você pode passar uma coluna e direção quando chamar o método reorder para remover todos os "order by" existentes e aplicar uma nova ordem à consulta.

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

    $usersOrderedByEmail = $query->reorder('email', 'desc')->get();

Grupo

Métodos 'groupBy' e 'having'

Como você poderia esperar, os métodos groupBy e having podem ser usados para agrupar os resultados da consulta. A assinatura do método having é semelhante à do método where:

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

Você pode usar o método havingBetween para filtrar os resultados dentro de um determinado intervalo:

    $report = DB::table('orders')
                    ->selectRaw('count(id) as number_of_orders, customer_id')
                    ->groupBy('customer_id')
                    ->havingBetween('number_of_orders', [5, 15])
                    ->get();

Você pode passar múltiplos argumentos para o método groupBy, agrupando por múltiplas colunas:

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

Para construir declarações mais avançadas de having, veja o método havingRaw.

Limitar e Deslocar

Métodos skip e take

Você pode usar os métodos skip e take para limitar o número de resultados retornados pela consulta ou para pular um determinado número de resultados na consulta:

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

Alternativamente, você pode usar os métodos limit e offset. Estes são funcionalmente equivalentes aos métodos take e skip, respectivamente:

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

Cláusulas condicionais

Às vezes você pode querer que certas cláusulas de consulta sejam aplicadas ao seu próprio consulta com base em outra condição, por exemplo, você só pode aplicar uma cláusula WHERE se um valor específico for fornecido na requisição HTTP de entrada usando o método when.

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

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

O método when executa o closure apenas quando o primeiro argumento for verdadeiro. Se o primeiro argumento for falso, o closure não será executado. Assim, no exemplo acima, o closure fornecido para o método when só será invocado se o campo "role" estiver presente na solicitação recebida e avaliar como verdadeiro.

Você pode passar outro closure como o terceiro argumento para o método when. Este closure será executado somente se o primeiro argumento for avaliado como falso. Para ilustrar como esta feature pode ser empregada, usaremos isso para configurar a ordem padrão de uma consulta:

    $sortByVotes = $request->boolean('sort_by_votes');

    $users = DB::table('users')
                    ->when($sortByVotes, function (Builder $query, bool $sortByVotes) {
                        $query->orderBy('votes');
                    }, function (Builder $query) {
                        $query->orderBy('name');
                    })
                    ->get();

Inserir instruções

O construtor de consultas também fornece um método insert que pode ser usado para inserir registros na tabela do banco de dados. O método insert aceita uma matriz de nomes de colunas e valores:

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

Você pode inserir vários registros de uma vez passando um array de arrays. Cada array representa um registro que deve ser inserido na tabela:

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

O método insertOrIgnore ignorará erros enquanto inserir registros no banco de dados. Ao utilizar esse método, você deve estar ciente de que o erro de registro duplicado será ignorado e outros tipos de erros também podem ser ignorados dependendo do mecanismo do banco de dados. Por exemplo, insertOrIgnore irá ignorar o modo estrito do MySQL:

    DB::table('users')->insertOrIgnore([
        ['id' => 1, 'email' => 'sisko@example.com'],
        ['id' => 2, 'email' => 'archer@example.com'],
    ]);

O método insertUsing inserirá novos registros na tabela usando uma subconsulta para determinar os dados que devem ser inseridos:

    DB::table('pruned_users')->insertUsing([
        'id', 'name', 'email', 'email_verified_at'
    ], DB::table('users')->select(
        'id', 'name', 'email', 'email_verified_at'
    )->where('updated_at', '<=', now()->subMonth()));

ID's incrementados automaticamente

Se o campo é de auto-incremento, utilize o método insertGetId para inserir um registro e então obter o ID:

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

ATENÇÃO

Ao usar o PostgreSQL, o método insertGetId espera que a coluna auto-incrementada seja chamada de id. Se você gostaria de obter a ID de uma "sequence" diferente, você pode passar o nome da coluna como o segundo parâmetro para o método insertGetId.

Upserts

O método upsert inserirá registros que não existem e atualizará os registros existentes com novos valores que você pode especificar. O argumento do método consiste nos valores para inserção ou atualização, enquanto o segundo argumento lista a coluna(s) que identifica exclusivamente os registros na tabela associada. O terceiro e último argumento é uma matriz de colunas que devem ser atualizadas se um registro correspondente já existir no banco de dados:

    DB::table('flights')->upsert(
        [
            ['departure' => 'Oakland', 'destination' => 'San Diego', 'price' => 99],
            ['departure' => 'Chicago', 'destination' => 'New York', 'price' => 150]
        ],
        ['departure', 'destination'],
        ['price']
    );

No exemplo acima, o Laravel tentará inserir dois registros. Se um registro já existir com os mesmos valores de coluna departure e destination, o Laravel atualizará a coluna price daquele registro.

ATENÇÃO

Todos os bancos de dados, exceto o SQL Server, exigem que as colunas no segundo argumento do método upsert tenham um índice "primário" ou "único". Além disso, o driver do banco de dados MySQL ignora o segundo argumento do método upsert e sempre usa os índices "primário" e "único" da tabela para detectar registros existentes.

Declarações de atualização

Além de inserir registros no banco de dados, o construtor de consultas também pode atualizar registros existentes usando o método update. O método update, assim como o insert, aceita um array de pares coluna-valor indicando as colunas a serem atualizadas. O método update retorna o número de linhas afetadas. Você pode restringir a consulta de atualização usando cláusulas where:

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

Atualizar ou inserir

Às vezes você pode querer atualizar um registro existente no banco de dados ou criar um se não houver nenhum registro correspondente. Neste cenário, o método updateOrInsert pode ser usado. O método updateOrInsert aceita dois argumentos: uma matriz de condições para encontrar o registro e uma matriz de pares coluna-valor indicando as colunas a serem atualizadas.

O método updateOrInsert tentará localizar um registro de banco de dados correspondente usando os pares de coluna e valor do primeiro argumento. Se o registro existir, ele será atualizado com os valores no segundo argumento. Se o registro não puder ser encontrado, um novo registro será inserido com os atributos mesclados de ambos os argumentos:

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

Você pode fornecer um closure para o método updateOrInsert para personalizar atributos que são atualizados ou inseridos no banco de dados com base na existência de uma linha correspondente.

DB::table('users')->updateOrInsert(
    ['user_id' => $user_id],
    fn ($exists) => $exists ? [
        'name' => $data['name'],
        'email' => $data['email'],
    ] : [
        'name' => $data['name'],
        'email' => $data['email'],
        'marketable' => true,
    ],
);

Atualizando Colunas JSON

Ao atualizar uma coluna JSON, você deve usar a sintaxe -> para atualizar a chave apropriada no objeto JSON. Esta operação é suportada em MySQL 5.7 e PostgreSQL 9.5+.

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

Incremento e Decremento

O construtor de consulta também fornece métodos convenientes para incrementar ou decrementar o valor de uma determinada coluna. Ambos os métodos aceitam pelo menos um argumento: a coluna a modificar. Um segundo argumento pode ser fornecido para especificar o valor pelo qual a coluna deve ser incrementada ou decrementada.

    DB::table('users')->increment('votes');

    DB::table('users')->increment('votes', 5);

    DB::table('users')->decrement('votes');

    DB::table('users')->decrement('votes', 5);

Se necessário você também pode especificar colunas adicionais para atualizar durante a operação de incremento ou decremento.

    DB::table('users')->increment('votes', 1, ['name' => 'John']);

Além disso, você pode aumentar ou diminuir várias colunas de uma vez usando os métodos incrementEach e decrementEach:

    DB::table('users')->incrementEach([
        'votes' => 5,
        'balance' => 100,
    ]);

Declarações de Delete

O método delete do construtor de consultas pode ser usado para apagar registros da tabela. O método delete retorna o número de linhas afetadas. Você pode restringir declarações para delete adicionando cláusulas where antes de chamar o método delete:

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

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

Se você deseja cortar uma tabela inteira, que vai remover todos os registros da tabela e redefinir o ID incrementado automaticamente para zero, você pode usar o método truncate:

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

Truncar a tabela e o PostgreSQL

Ao truncar um banco de dados PostgreSQL, o comportamento CASCADE será aplicado. Isso significa que todos os registros relacionados à chave estrangeira em outras tabelas serão excluídos também.

Bloqueio pessimista

O construtor de consultas também inclui algumas funções para ajudá-lo a alcançar o "lock pessimistic" quando executar suas instruções select Para executar uma declaração com um "lock compartilhado", você pode chamar o método sharedLock. Um lock compartilhado impede que as linhas selecionadas sejam modificadas até que sua transação seja confirmada:

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

Alternativamente, você pode usar o método lockForUpdate. Uma "trava para atualização" impede que os registros selecionados sejam modificados ou selecionados com outro bloqueio compartilhado:

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

Depuração de código

Você pode usar os métodos dd e dump durante a construção de uma consulta para descartar as associações atuais da consulta e SQL. O método dd exibirá a informação de depuração e então parará de executar o pedido. O método dump exibirá a informação de depuração mas permitirá que o pedido continue em execução:

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

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

Os métodos dumpRawSql e ddRawSql podem ser invocados em uma consulta para gerar o SQL da consulta com todas as ligações de parâmetros substituídas corretamente.

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

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