Base de datos: Query Builder

• Introducción
• Ejecutando Consultas a la Base de Datos
  • Dividiendo Resultados
  • Transmitiendo Resultados de Forma Perezosa
  • Agregados
• Sentencias Select
• Expresiones Crudas
• Joins
• Uniones
• Cláusulas Where Básicas
  • Cláusulas Where
  • Cláusulas Or Where
  • Cláusulas Where Not
  • Cláusulas Where Any / All / None
  • Cláusulas Where JSON
  • Cláusulas Where Adicionales
  • Agrupación Lógica
• Cláusulas Where Avanzadas
  • Cláusulas Where Exists
  • Cláusulas Where Subconsulta
  • Cláusulas Where de Texto Completo
• Ordenación, Agrupación, Límite y Desplazamiento
  • Ordenación
  • Agrupación
  • Límite y Desplazamiento
• Cláusulas Condicionales
• Sentencias Insert
  • Upserts
• Sentencias Update
  • Actualizando Columnas JSON
  • Incrementar y Decrementar
• Sentencias Delete
• Bloqueo Pesimista
• Depuración

Introducción

El constructor de consultas de base de datos de Laravel proporciona una interfaz fluida y conveniente para crear y ejecutar consultas de base de datos. Se puede usar para realizar la mayoría de las operaciones de base de datos en tu aplicación y funciona perfectamente con todos los sistemas de bases de datos soportados por Laravel. El constructor de consultas de Laravel utiliza el enlace de parámetros PDO para proteger su aplicación contra ataques de inyección SQL. No es necesario limpiar o sanear las cadenas pasadas al constructor de consultas como vinculaciones de consulta.

Ejecutando Consultas a la Base de Datos

Recuperando Todas las Filas de una Tabla

Puedes usar el método table proporcionado por la fachada DB para iniciar una consulta. El método table devuelve una instancia de un generador de consultas fluente para la tabla dada, lo que te permite encadenar más restricciones a la consulta y luego recuperar los resultados de la consulta utilizando el método get:

<?php

namespace App\Http\Controllers;

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

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

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

El método get devuelve una instancia de Illuminate\Support\Collection que contiene los resultados de la consulta, donde cada resultado es una instancia del objeto PHP stdClass. Puedes acceder al valor de cada columna accediendo a la columna como una propiedad del objeto:

use Illuminate\Support\Facades\DB;

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

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

Recuperando una Sola Fila / Columna de una Tabla

Si solo necesitas recuperar una sola fila de una tabla de base de datos, puedes usar el método first de la fachada DB. Este método devolverá un solo objeto stdClass:

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

return $user->email;

Si deseas recuperar una sola fila de una tabla de base de datos, pero lanzar una Illuminate\Database\RecordNotFoundException si no se encuentra ninguna fila coincidente, puedes usar el método firstOrFail. Si la RecordNotFoundException no se captura, se envía automáticamente una respuesta HTTP 404 de vuelta al cliente:

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

Si no necesitas toda una fila, puedes extraer un solo valor de un registro utilizando el método value. Este método devolverá el valor de la columna directamente:

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

Para recuperar una sola fila por el valor de su columna id, utiliza el método find:

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

Recuperando una Lista de Valores de Columna

Si deseas obtener una instancia de Illuminate\Support\Collection que contenga los valores de una sola columna, puedes usar el método pluck. En este ejemplo, recuperaremos una colección de títulos de usuario:

use Illuminate\Support\Facades\DB;

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

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

Puedes especificar la columna que la colección resultante debe usar como sus claves proporcionando un segundo argumento al método pluck:

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

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

Dividiendo Resultados

Si necesitas trabajar con miles de registros de base de datos, considera usar el método chunk proporcionado por la facade DB. Este método recupera un pequeño fragmento de resultados a la vez y alimenta cada fragmento a una función anónima para su procesamiento. Por ejemplo, recuperemos toda la tabla users en fragmentos de 100 registros a la vez:

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

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

Puedes detener el procesamiento de más segmentos devolviendo false desde la función anónima:

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

    return false;
});

Si estás actualizando registros de base de datos mientras haces fragmentos de resultados, los resultados de tus fragmentos podrían cambiar de maneras inesperadas. Si planeas actualizar los registros recuperados mientras fragmentas, siempre es mejor usar el método chunkById. Este método paginará automáticamente los resultados en función de la clave primaria del 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]);
        }
    });

Dado que los métodos chunkById y lazyById añaden sus propias condiciones "where" a la consulta que se está ejecutando, debes agrupar lógicamente tus propias condiciones dentro de una función anónima:

DB::table('users')->where(function ($query) {
    $query->where('credits', 1)->orWhere('credits', 2);
})->chunkById(100, function (Collection $users) {
    foreach ($users as $user) {
        DB::table('users')
          ->where('id', $user->id)
          ->update(['credits' => 3]);
    }
});

Transmitiendo Resultados de Forma Perezosa

El método lazy funciona de manera similar al método chunk en el sentido de que ejecuta la consulta en bloques. Sin embargo, en lugar de pasar cada bloque a una función de callback, el método lazy() devuelve una LazyCollection, que te permite interactuar con los resultados como si fueran un solo flujo:

use Illuminate\Support\Facades\DB;

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

Una vez más, si planeas actualizar los registros recuperados mientras los iteras, es mejor usar los métodos lazyById o lazyByIdDesc. Estos métodos paginarán automáticamente los resultados en función de la clave primaria del registro:

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

Agregados

El generador de consultas también ofrece una variedad de métodos para recuperar valores agregados como count, max, min, avg y sum. Puedes llamar a cualquiera de estos métodos después de construir tu consulta:

use Illuminate\Support\Facades\DB;

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

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

Por supuesto, puedes combinar estos métodos con otras cláusulas para ajustar cómo se calcula tu valor agregado:

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

Determinando si Existen Registros

En lugar de utilizar el método count para determinar si existen registros que coinciden con las restricciones de tu consulta, puedes usar los métodos exists y doesntExist:

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

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

Sentencias Select

Especificando una Cláusula de Selección

Puede que no siempre desees seleccionar todas las columnas de una tabla de base de datos. Usando el método select, puedes especificar una cláusula de "select" personalizada para la consulta:

use Illuminate\Support\Facades\DB;

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

El método distinct te permite forzar que la consulta devuelva resultados distintos:

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

Si ya tienes una instancia del generador de consultas y deseas agregar una columna a su cláusula de selección existente, puedes usar el método addSelect:

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

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

Expresiones Crudas

A veces puede que necesites insertar una cadena arbitraria en una consulta. Para crear una expresión de cadena en bruto, puedes usar el método raw proporcionado por la facade DB:

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

Métodos Crudos

En lugar de utilizar el método DB::raw, también puedes usar los siguientes métodos para insertar una expresión en bruto en varias partes de tu consulta. Recuerda, Laravel no puede garantizar que cualquier consulta que use expresiones en bruto esté protegida contra vulnerabilidades de inyección SQL.

selectRaw

El método selectRaw se puede utilizar en lugar de addSelect(DB::raw(/* ... */)). Este método acepta un array opcional de enlaces como su segundo argumento:

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

whereRaw / orWhereRaw

Los métodos whereRaw y orWhereRaw se pueden usar para inyectar una cláusula "where" en bruto en tu consulta. Estos métodos aceptan un array opcional de vinculaciones como su segundo argumento:

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

havingRaw / orHavingRaw

Los métodos havingRaw y orHavingRaw se pueden utilizar para proporcionar una cadena en bruto como el valor de la cláusula "having". Estos métodos aceptan un array opcional de vinculaciones como su segundo argumento:

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

orderByRaw

El método orderByRaw se puede utilizar para proporcionar una cadena en bruto como el valor de la cláusula "order by":

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

groupByRaw

El método groupByRaw se puede utilizar para proporcionar una cadena en bruto como el valor de la cláusula group by:

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

Joins

Cláusula Inner Join

El constructor de consultas también se puede utilizar para añadir cláusulas de unión a tus consultas. Para realizar un "inner join" básico, puedes usar el método join en una instancia del constructor de consultas. El primer argumento que se pasa al método join es el nombre de la tabla a la que necesitas unirte, mientras que los argumentos restantes especifican las restricciones de columna para la unión. Incluso puedes unirte a múltiples tablas en una sola 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 Left Join / Right Join

Si deseas realizar un "left join" o "right join" en lugar de un "inner join", utiliza los métodos leftJoin o rightJoin. Estos métodos tienen la misma firma que el 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();

Cláusula de Join Cruzado

Puedes usar el método crossJoin para realizar un "cross join". Los cross joins generan un producto cartesiano entre la primera tabla y la tabla unida:

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

Cláusulas de Unión Avanzadas

También puedes especificar cláusulas de unión más avanzadas. Para empezar, pasa una función anónima como segundo argumento al método join. La función anónima recibirá una instancia de Illuminate\Database\Query\JoinClause, lo que te permite especificar restricciones en la cláusula de "unión":

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

Si deseas usar una cláusula "where" en tus uniones, puedes utilizar los métodos where y orWhere proporcionados por la instancia JoinClause. En lugar de comparar dos columnas, estos métodos compararán la columna contra un valor:

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

Uniones de Subconsultas

Puedes usar los métodos joinSub, leftJoinSub y rightJoinSub para unir una consulta a una subconsulta. Cada uno de estos métodos recibe tres argumentos: la subconsulta, su alias de tabla y una función anónima que define las columnas relacionadas. En este ejemplo, recuperaremos una colección de usuarios donde cada registro de usuario también contenga la marca de tiempo created_at de la publicación de blog más reciente del usuario:

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

Joins Laterales

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

Uniones

El constructor de consultas también ofrece un método conveniente para "unir" dos o más consultas. Por ejemplo, puedes crear una consulta inicial y usar el método union para unirla con más consultas:

use Illuminate\Support\Facades\DB;

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

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

Además del método union, el constructor de consultas proporciona un método unionAll. Las consultas que se combinan utilizando el método unionAll no tendrán sus resultados duplicados eliminados. El método unionAll tiene la misma firma de método que el método union.

Cláusulas Where Básicas

Cláusulas Where

Puedes usar el método where del constructor de consultas para añadir cláusulas "where" a la consulta. La llamada más básica al método where requiere tres argumentos. El primer argumento es el nombre de la columna. El segundo argumento es un operador, que puede ser cualquiera de los operadores soportados por la base de datos. El tercer argumento es el valor con el que comparar el valor de la columna. Por ejemplo, la siguiente consulta recupera usuarios donde el valor de la columna votes es igual a 100 y el valor de la columna age es mayor que 35:

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

Para conveniencia, si deseas verificar que una columna es = a un valor dado, puedes pasar el valor como segundo argumento al método where. Laravel asumirá que te gustaría usar el operador =:

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

Como se mencionó anteriormente, puedes usar cualquier operador que sea compatible con tu sistema de base de datos:

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

También puedes pasar un array de condiciones a la función where. Cada elemento del array debe ser un array que contenga los tres argumentos que típicamente se pasan al método where:

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

Cláusulas Or Where

Al encadenar llamadas al método where del generador de consultas, las cláusulas "where" se unirán utilizando el operador and. Sin embargo, puedes usar el método orWhere para unir una cláusula a la consulta utilizando el operador or. El método orWhere acepta los mismos argumentos que el método where:

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

Si necesitas agrupar una condición "o" entre paréntesis, puedes pasar una función anónima como primer argumento al método orWhere:

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

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

Cláusulas Where Not

Los métodos whereNot y orWhereNot se pueden usar para negar un grupo dado de restricciones de consulta. Por ejemplo, la siguiente consulta excluye productos que están en liquidación o que tienen un precio que es menos de diez:

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

Cláusulas Where Any / All / None

A veces es posible que necesites aplicar las mismas restricciones de consulta a múltiples columnas. Por ejemplo, es posible que desees recuperar todos los registros donde cualquier columna en una lista dada sea LIKE un valor dado. Puedes lograr esto utilizando el método whereAny:

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

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

De manera similar, el método whereAll se puede utilizar para recuperar registros donde todas las columnas dadas coinciden con una restricción dada:

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

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

El método whereNone se puede utilizar para recuperar registros donde ninguna de las columnas dadas coincida con una restricción dada:

$posts = DB::table('albums')
            ->where('published', true)
            ->whereNone([
                'title',
                'lyrics',
                'tags',
            ], 'like', '%explicit%')
            ->get();

La consulta anterior resultará en el siguiente SQL:

SELECT *
FROM albums
WHERE published = true AND NOT (
    title LIKE '%explicit%' OR
    lyrics LIKE '%explicit%' OR
    tags LIKE '%explicit%'
)

Cláusulas Where JSON

Laravel también admite la consulta de tipos de columna JSON en bases de datos que proporcionan soporte para tipos de columna JSON. Actualmente, esto incluye MariaDB 10.3+, MySQL 8.0+, PostgreSQL 12.0+, SQL Server 2017+ y SQLite 3.39.0+. Para consultar una columna JSON, utiliza el operador ->:

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

Puedes usar whereJsonContains para consultar arreglos JSON:

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

Si tu aplicación utiliza las bases de datos MariaDB, MySQL o PostgreSQL, puedes pasar un array de valores al método whereJsonContains:

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

Puedes usar el método whereJsonLength para consultar arrays JSON por su longitud:

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

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

Cláusulas Where Adicionales

whereLike / orWhereLike / whereNotLike / orWhereNotLike El método whereLike te permite añadir cláusulas "LIKE" a tu consulta para la coincidencia de patrones. Estos métodos proporcionan una forma independiente de la base de datos de realizar consultas de coincidencia de cadenas, con la capacidad de alternar la sensibilidad a mayúsculas y minúsculas. Por defecto, la coincidencia de cadenas es insensible a mayúsculas y minúsculas:

$users = DB::table('users')
           ->whereLike('name', '%John%')
           ->get();

Puedes habilitar una búsqueda que distingue entre mayúsculas y minúsculas a través del argumento caseSensitive:

$users = DB::table('users')
           ->whereLike('name', '%John%', caseSensitive: true)
           ->get();

El método orWhereLike te permite añadir una cláusula "or" con una condición LIKE:

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

El método whereNotLike te permite añadir cláusulas "NOT LIKE" a tu consulta:

$users = DB::table('users')
           ->whereNotLike('name', '%John%')
           ->get();

De manera similar, puedes usar orWhereNotLike para añadir una cláusula "o" con una condición NOT LIKE:

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

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

El método whereNotIn verifica que el valor de la columna dada no esté contenido en el array dado:

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

También puedes proporcionar un objeto de consulta como segundo argumento del método whereIn:

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

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

El ejemplo anterior producirá el siguiente SQL:

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

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

whereNotBetween / orWhereNotBetween El método whereNotBetween verifica que el valor de una columna esté fuera de dos valores:

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

whereBetweenColumns / whereNotBetweenColumns / orWhereBetweenColumns / orWhereNotBetweenColumns El método whereBetweenColumns verifica que el valor de una columna esté entre los dos valores de dos columnas en la misma fila de la tabla:

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

El método whereNotBetweenColumns verifica que el valor de una columna esté fuera de los dos valores de dos columnas en la misma fila de la tabla:

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

whereNull / whereNotNull / orWhereNull / orWhereNotNull El método whereNull verifica que el valor de la columna dada sea NULL:

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

El método whereNotNull verifica que el valor de la columna no sea NULL:

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

whereDate / whereMonth / whereDay / whereYear / whereTime El método whereDate se puede utilizar para comparar el valor de una columna con una fecha:

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

El método whereMonth se puede utilizar para comparar el valor de una columna con un mes específico:

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

El método whereDay se puede utilizar para comparar el valor de una columna con un día específico del mes:

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

El método whereYear se puede utilizar para comparar el valor de una columna con un año específico:

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

El método whereTime se puede utilizar para comparar el valor de una columna contra un tiempo específico:

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

whereColumn / orWhereColumn El método whereColumn se puede utilizar para verificar que dos columnas son iguales:

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

También puedes pasar un operador de comparación al método whereColumn:

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

También puedes pasar un array de comparaciones de columnas al método whereColumn. Estas condiciones se unirán utilizando el operador and:

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

Agrupación Lógica

A veces es posible que necesites agrupar varias cláusulas "where" entre paréntesis para lograr el agrupamiento lógico deseado en tu consulta. De hecho, generalmente deberías agrupar las llamadas al método orWhere entre paréntesis para evitar un comportamiento inesperado en la consulta. Para lograr esto, puedes pasar una función anónima al método where:

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

Como puedes ver, pasar una función anónima al método where instruye al generador de consultas a comenzar un grupo de restricciones. La función anónima recibirá una instancia del generador de consultas que puedes usar para establecer las restricciones que deben estar contenidas dentro del grupo entre paréntesis. El ejemplo anterior producirá el siguiente SQL:

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

Cláusulas Where Avanzadas

Cláusulas Where Exists

El método whereExists te permite escribir cláusulas SQL de "where exists". El método whereExists acepta una función anónima que recibirá una instancia del generador de consultas, lo que te permitirá definir la consulta que debería colocarse dentro de la cláusula "exists":

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

Alternativamente, puedes proporcionar un objeto de consulta al método whereExists en lugar de una función anónima:

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

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

Ambos ejemplos anteriores producirán el siguiente SQL:

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

Cláusulas Where Subconsulta

A veces es posible que necesites construir una cláusula "where" que compare los resultados de una subconsulta con un valor dado. Puedes lograr esto pasando una función anónima y un valor al método where. Por ejemplo, la siguiente consulta recuperará todos los usuarios que tienen una "membresía" reciente de un 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();

O, puede que necesites construir una cláusula "where" que compare una columna con los resultados de una subconsulta. Puedes lograr esto pasando una columna, un operador y una función anónima al método where. Por ejemplo, la siguiente consulta recuperará todos los registros de ingresos donde el monto es menor que el promedio;

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 de Texto Completo

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

Ordenación, Agrupación, Límite y Desplazamiento

Ordenación

El método orderBy

El método orderBy te permite ordenar los resultados de la consulta por una columna dada. El primer argumento que acepta el método orderBy debe ser la columna por la que deseas ordenar, mientras que el segundo argumento determina la dirección de la ordenación y puede ser asc o desc:

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

Para ordenar por múltiples columnas, simplemente puedes invocar orderBy tantas veces como sea necesario:

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

Los Métodos latest y oldest

Los métodos latest y oldest te permiten ordenar resultados por fecha de manera sencilla. Por defecto, el resultado se ordenará por la columna created_at de la tabla. O puedes pasar el nombre de la columna por la que deseas ordenar:

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

Ordenamiento Aleatorio

El método inRandomOrder se puede utilizar para ordenar los resultados de la consulta de forma aleatoria. Por ejemplo, puedes usar este método para obtener un usuario aleatorio:

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

Eliminando Ordenamientos Existentes

El método reorder elimina todas las cláusulas "order by" que se han aplicado previamente a la consulta:

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

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

Puedes pasar una columna y dirección al llamar al método reorder para eliminar todas las cláusulas "order by" existentes y aplicar un nuevo orden completamente a la consulta:

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

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

Agrupación

Los Métodos groupBy y having

Como era de esperar, los métodos groupBy y having se pueden utilizar para agrupar los resultados de la consulta. La firma del método having es similar a la del método where:

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

Puedes usar el método havingBetween para filtrar los resultados dentro de un rango dado:

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

Puedes pasar múltiples argumentos al método groupBy para agrupar por múltiples columnas:

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

Para construir declaraciones having más avanzadas, consulta el método havingRaw.

Límite y Desplazamiento

Los métodos skip y take

Puedes usar los métodos skip y take para limitar el número de resultados devueltos de la consulta o para omitir un número dado de resultados en la consulta:

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

Alternativamente, puedes usar los métodos limit y offset. Estos métodos son funcionalmente equivalentes a los métodos take y skip, respectivamente:

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

Cláusulas Condicionales

A veces es posible que desees que ciertas cláusulas de consulta se apliquen a una consulta en función de otra condición. Por ejemplo, es posible que solo desees aplicar una declaración where si un valor de entrada dado está presente en la solicitud HTTP entrante. Puedes lograr esto utilizando el método when:

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

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

El método when solo ejecuta la función anónima dada cuando el primer argumento es true. Si el primer argumento es false, la función anónima no se ejecutará. Así que, en el ejemplo anterior, la función anónima dada al método when solo se invocará si el campo role está presente en la solicitud entrante y evalúa a true. Puedes pasar otra función anónima como tercer argumento al método when. Esta función anónima solo se ejecutará si el primer argumento evalúa como false. Para ilustrar cómo se puede usar esta función, la utilizaremos para configurar el ordenamiento predeterminado de una 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();

Sentencias Insert

El generador de consultas también proporciona un método insert que se puede usar para insertar registros en la tabla de la base de datos. El método insert acepta un array de nombres de columnas y valores:

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

Puedes insertar varios registros a la vez pasando un array de arrays. Cada array representa un registro que debe ser insertado en la tabla:

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

El método insertOrIgnore ignorará errores al insertar registros en la base de datos. Al usar este método, debes tener en cuenta que se ignorarán los errores de registros duplicados y otros tipos de errores también pueden ser ignorados dependiendo del motor de la base de datos. Por ejemplo, insertOrIgnore eludirá el modo estricto de MySQL:

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

El método insertUsing insertará nuevos registros en la tabla mientras utiliza una subconsulta para determinar los datos que se deben insertar:

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

IDs de Auto-Incremento

Si la tabla tiene un ID que se auto-incrementa, utiliza el método insertGetId para insertar un registro y luego recuperar el ID:

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

Upserts

El método upsert insertará registros que no existen y actualizará los registros que ya existen con nuevos valores que puedes especificar. El primer argumento del método consiste en los valores a insertar o actualizar, mientras que el segundo argumento enumera las columnas que identifican de manera única los registros dentro de la tabla asociada. El tercer y último argumento del método es un array de columnas que deben actualizarse si ya existe un registro coincidente en la base de datos:

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

En el ejemplo anterior, Laravel intentará insertar dos registros. Si ya existe un registro con los mismos valores de columna departure y destination, Laravel actualizará la columna price de ese registro.

Sentencias Update

Además de insertar registros en la base de datos, el generador de consultas también puede actualizar registros existentes utilizando el método update. El método update, al igual que el método insert, acepta un array de pares de columna y valor que indican las columnas a actualizar. El método update devuelve el número de filas afectadas. Puedes restringir la consulta update utilizando cláusulas where:

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

Actualizar o Insertar

A veces es posible que desees actualizar un registro existente en la base de datos o crearlo si no existe un registro coincidente. En este escenario, se puede usar el método updateOrInsert. El método updateOrInsert acepta dos argumentos: un array de condiciones para encontrar el registro y un array de pares de columna y valor que indican las columnas a actualizar. El método updateOrInsert intentará localizar un registro de base de datos coincidente utilizando los pares de columnas y valores del primer argumento. Si el registro existe, se actualizará con los valores del segundo argumento. Si no se puede encontrar el registro, se insertará un nuevo registro con los atributos combinados de ambos argumentos:

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

Puedes proporcionar una función anónima al método updateOrInsert para personalizar los atributos que se actualizan o insertan en la base de datos según la existencia de un registro coincidente:

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

Actualizando Columnas JSON

Al actualizar una columna JSON, debes usar la sintaxis -> para actualizar la clave apropiada en el objeto JSON. Esta operación es compatible con MariaDB 10.3+, MySQL 5.7+ y PostgreSQL 9.5+:

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

Incrementar y Decrementar

El generador de consultas también proporciona métodos convenientes para incrementar o decrementar el valor de una columna dada. Ambos métodos aceptan al menos un argumento: la columna a modificar. Se puede proporcionar un segundo argumento para especificar la cantidad por la cual se debe incrementar o decrementar la columna:

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

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

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

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

Si es necesario, también puedes especificar columnas adicionales para actualizar durante la operación de incremento o decremento:

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

Además, puedes incrementar o decrementar múltiples columnas a la vez utilizando los métodos incrementEach y decrementEach:

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

Sentencias Delete

El método delete del generador de consultas se puede utilizar para eliminar registros de la tabla. El método delete devuelve el número de filas afectadas. Puedes restringir las declaraciones delete añadiendo cláusulas "where" antes de llamar al método delete:

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

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

Si deseas truncar una tabla completa, lo que eliminará todos los registros de la tabla y restablecerá la ID autoincremental a cero, puedes usar el método truncate:

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

Truncamiento de Tablas y PostgreSQL

Al truncar una base de datos PostgreSQL, se aplicará el comportamiento CASCADE. Esto significa que todos los registros relacionados con claves foráneas en otras tablas también se eliminarán.

Bloqueo Pesimista

El generador de consultas también incluye algunas funciones para ayudarte a lograr un "bloqueo pesimista" al ejecutar tus declaraciones select. Para ejecutar una declaración con un "bloqueo compartido", puedes llamar al método sharedLock. Un bloqueo compartido evita que las filas seleccionadas sean modificadas hasta que tu transacción sea confirmada:

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

Alternativamente, puedes usar el método lockForUpdate. Un bloqueo "para actualizar" evita que los registros seleccionados sean modificados o seleccionados con otro bloqueo compartido:

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

Depuración

Puedes usar los métodos dd y dump mientras construyes una consulta para mostrar los enlaces de consulta y el SQL actual. El método dd mostrará la información de depuración y luego detendrá la ejecución de la solicitud. El método dump mostrará la información de depuración pero permitirá que la solicitud continúe ejecutándose:

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

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

Los métodos dumpRawSql y ddRawSql se pueden invocar en una consulta para volcar el SQL de la consulta con todos los enlaces de parámetros sustituidos correctamente:

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

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