Base de datos: Constructor de consultas

• Introducción
• Ejecutando consultas a la base de datos
  • Procesar resultados en bloques
  • Transmitir resultados de forma perezosa
  • Agregados
• Sentencias Select
• Expresiones Raw
• Joins
• Uniones
• Cláusulas básicas Where
  • Cláusulas Where
  • Cláusulas Or Where
  • Cláusulas Where Not
  • Cláusulas Where Any / All
  • Cláusulas JSON Where
  • Cláusulas Where adicionales
  • Agrupación lógica
• Cláusulas Where avanzadas
  • Cláusulas Where Exists
  • Cláusulas Where con subconsultas
  • Cláusulas Where de texto completo
• Ordenar, agrupar, límite y offset
  • Ordenar
  • Agrupar
  • Límite y offset
• Cláusulas condicionales
• Sentencias Insert
  • Upserts
• Sentencias Update
  • Actualizar columnas JSON
  • Incrementar y decrementar
• Sentencias Delete
• Bloqueo pesimista
• Depuración

#Introducción

El query builder de base de datos de Laravel proporciona una interfaz fluida y conveniente para crear y ejecutar consultas a la base de datos. Puede usarse para realizar la mayoría de las operaciones de base de datos en su aplicación y funciona perfectamente con todos los sistemas de base de datos soportados por Laravel.

El query builder 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 sanitizar las cadenas que se pasan al query builder como bindings de consulta.

#Ejecutando consultas a la base de datos

#Recuperar todas las filas de una tabla

Puede usar el método table proporcionado por el facade DB para iniciar una consulta. El método table devuelve una instancia fluida del query builder para la tabla dada, permitiéndole encadenar más restricciones a la consulta y finalmente recuperar los resultados usando el método get:

<?php

namespace App\Http\Controllers;

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

class UserController extends Controller
{
    /**
     * Mostrar una lista de todos los usuarios de la aplicación.
     */
    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. Puede 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;
}

#Recuperar una sola fila / columna de una tabla

Si solo necesita recuperar una sola fila de una tabla de base de datos, puede usar el método first del facade DB. Este método devolverá un único objeto stdClass:

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

return $user->email;

Si no necesita una fila completa, puede extraer un solo valor de un registro usando el método value. Este método devolverá directamente el valor de la columna:

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

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

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

#Recuperar una lista de valores de columna

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

use Illuminate\Support\Facades\DB;

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

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

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

#Procesar resultados en bloques

Si necesita trabajar con miles de registros de base de datos, considere usar el método chunk proporcionado por el facade DB. Este método recupera un pequeño bloque de resultados a la vez y pasa cada bloque a un closure para su procesamiento. Por ejemplo, recuperemos toda la tabla users en bloques 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) {
        // ...
    }
});

Puede detener el procesamiento de bloques adicionales retornando false desde el closure:

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

    return false;
});

Si está actualizando registros de base de datos mientras procesa los bloques, los resultados podrían cambiar de forma inesperada. Si planea actualizar los registros recuperados mientras procesa los bloques, siempre es mejor usar el método chunkById. Este método paginará automáticamente los resultados basándose en 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]);
        }
    });

#Transmitir 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 un callback, el método lazy() devuelve una LazyCollection, que le permite interactuar con los resultados como un flujo único:

use Illuminate\Support\Facades\DB;

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

Una vez más, si planea actualizar los registros recuperados mientras los itera, es mejor usar los métodos lazyById o lazyByIdDesc. Estos métodos paginarán automáticamente los resultados basándose en 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 query builder también proporciona una variedad de métodos para recuperar valores agregados como count, max, min, avg y sum. Puede llamar a cualquiera de estos métodos después de construir su consulta:

use Illuminate\Support\Facades\DB;

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

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

Por supuesto, puede combinar estos métodos con otras cláusulas para afinar cómo se calcula su valor agregado:

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

#Determinar si existen registros

En lugar de usar el método count para determinar si existen registros que coincidan con las restricciones de su consulta, puede 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

#Especificar una cláusula Select

No siempre querrá seleccionar todas las columnas de una tabla de base de datos. Usando el método select, puede especificar una cláusula "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 le permite forzar que la consulta devuelva resultados distintos:

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

Si ya tiene una instancia del query builder y desea agregar una columna a su cláusula select existente, puede usar el método addSelect:

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

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

#Expresiones Raw

A veces puede necesitar insertar una cadena arbitraria en una consulta. Para crear una expresión raw, puede usar el método raw proporcionado por el facade DB:

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

#Métodos Raw

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

#selectRaw

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

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

#whereRaw / orWhereRaw

Los métodos whereRaw y orWhereRaw pueden usarse para inyectar una cláusula "where" raw en su consulta. Estos métodos aceptan un array opcional de bindings como segundo argumento:

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

#havingRaw / orHavingRaw

Los métodos havingRaw y orHavingRaw pueden usarse para proporcionar una cadena raw como valor de la cláusula "having". Estos métodos aceptan un array opcional de bindings como 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 puede usarse para proporcionar una cadena raw como valor de la cláusula "order by":

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

#groupByRaw

El método groupByRaw puede usarse para proporcionar una cadena raw como valor de la cláusula group by:

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

#Joins

#Cláusula Inner Join

El query builder también puede usarse para agregar cláusulas join a sus consultas. Para realizar un "inner join" básico, puede usar el método join en una instancia del query builder. El primer argumento pasado al método join es el nombre de la tabla a la que desea unir, mientras que los argumentos restantes especifican las restricciones de columna para el join. Incluso puede unir 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 desea realizar un "left join" o "right join" en lugar de un "inner join", use 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 Cross Join

Puede 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 Join avanzadas

También puede especificar cláusulas join más avanzadas. Para comenzar, pase un closure como segundo argumento al método join. El closure recibirá una instancia de Illuminate\Database\Query\JoinClause que le permite especificar restricciones en la cláusula "join":

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

Si desea usar una cláusula "where" en sus joins, puede usar 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();

#Joins con subconsultas

Puede 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 un closure que define las columnas relacionadas. En este ejemplo, recuperaremos una colección de usuarios donde cada registro de usuario también contiene 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

Puede usar los métodos joinLateral y leftJoinLateral para realizar un "lateral join" con una subconsulta. Cada uno de estos métodos recibe dos argumentos: la subconsulta y su alias de tabla. La(s) condición(es) del join deben especificarse dentro de la cláusula where de la subconsulta dada. Los joins laterales se evalúan para cada fila y pueden referenciar columnas fuera de la subconsulta.

En este ejemplo, recuperaremos una colección de usuarios así como las tres publicaciones de blog más recientes del usuario. Cada usuario puede producir hasta tres filas en el conjunto de resultados: una por cada una de sus publicaciones de blog más recientes. La condición del join se especifica con una cláusula whereColumn dentro de la subconsulta, haciendo referencia a la fila actual del usuario:

$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 query builder también proporciona un método conveniente para "unir" dos o más consultas. Por ejemplo, puede 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 query builder proporciona un método unionAll. Las consultas combinadas usando el método unionAll no eliminarán resultados duplicados. El método unionAll tiene la misma firma que el método union.

#Cláusulas básicas Where

#Cláusulas Where

Puede usar el método where del query builder para agregar 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 contra el cual 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 mayor comodidad, si desea verificar que una columna sea = a un valor dado, puede pasar el valor como segundo argumento al método where. Laravel asumirá que desea usar el operador =:

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

Como se mencionó anteriormente, puede usar cualquier operador que sea soportado por su 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 puede pasar un array de condiciones al método where. Cada elemento del array debe ser un array que contenga los tres argumentos que normalmente 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 query builder, las cláusulas "where" se unirán usando el operador and. Sin embargo, puede usar el método orWhere para unir una cláusula a la consulta usando 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 necesita agrupar una condición "or" dentro de paréntesis, puede pasar un closure 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();

El ejemplo anterior producirá el siguiente SQL:

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

#Cláusulas Where Not

Los métodos whereNot y orWhereNot pueden usarse 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 menor que diez:

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

#Cláusulas Where Any / All

A veces puede necesitar aplicar las mismas restricciones de consulta a múltiples columnas. Por ejemplo, puede querer recuperar todos los registros donde cualquiera de las columnas en una lista dada sea LIKE a un valor dado. Puede lograr esto usando el método whereAny:

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

La consulta anterior resultará en el siguiente SQL:

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 puede usarse para recuperar registros donde todas las columnas dadas coincidan con una restricción dada:

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

La consulta anterior resultará en el siguiente SQL:

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

#Cláusulas JSON Where

Laravel también soporta consultas en columnas JSON en bases de datos que proporcionan soporte para tipos de columna JSON. Actualmente, esto incluye MySQL 5.7+, PostgreSQL, SQL Server 2016 y SQLite 3.39.0 (con la extensión JSON1). Para consultar una columna JSON, use el operador ->:

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

Puede usar whereJsonContains para consultar arrays JSON:

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

Si su aplicación usa las bases de datos MySQL o PostgreSQL, puede pasar un array de valores al método whereJsonContains:

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

Puede 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

whereBetween / orWhereBetween

El método whereBetween verifica que el valor de una columna esté entre dos valores:

$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 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 valores de dos columnas en la misma fila de la tabla:

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

whereIn / whereNotIn / orWhereIn / orWhereNotIn

El método whereIn verifica que el valor de una columna dada esté contenido dentro del array dado:

$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 proporcionado:

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

También puede 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 generará el siguiente SQL:

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

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 puede usarse 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 puede usarse 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 puede usarse 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 puede usarse 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 puede usarse para comparar el valor de una columna con una hora específica:

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

whereColumn / orWhereColumn

El método whereColumn puede usarse para verificar que dos columnas sean iguales:

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

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

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

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

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

#Agrupación lógica

A veces puede necesitar agrupar varias cláusulas "where" dentro de paréntesis para lograr la agrupación lógica deseada en su consulta. De hecho, generalmente debería agrupar siempre las llamadas al método orWhere entre paréntesis para evitar comportamientos inesperados en la consulta. Para lograr esto, puede pasar un closure al método where:

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

Como puede ver, pasar un closure al método where indica al query builder que inicie un grupo de restricciones. El closure recibirá una instancia del query builder que puede usar para establecer las restricciones que deben estar contenidas dentro del grupo de paréntesis. El ejemplo anterior generará 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 le permite escribir cláusulas SQL "where exists". El método whereExists acepta un closure que recibirá una instancia del query builder, permitiéndole definir la consulta que debe 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, puede proporcionar un objeto de consulta al método whereExists en lugar de un closure:

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

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

Ambos ejemplos anteriores generarán el siguiente SQL:

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

#Cláusulas Where con subconsultas

A veces puede necesitar construir una cláusula "where" que compare los resultados de una subconsulta con un valor dado. Puede lograr esto pasando un closure y un valor al método where. Por ejemplo, la siguiente consulta recuperará todos los usuarios que tengan 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 necesitar construir una cláusula "where" que compare una columna con los resultados de una subconsulta. Puede lograr esto pasando una columna, un operador y un closure al método where. Por ejemplo, la siguiente consulta recuperará todos los registros de ingresos donde la cantidad sea 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

Los métodos whereFullText y orWhereFullText pueden usarse para agregar cláusulas "where" de texto completo a una consulta para columnas que tengan índices de texto completo. Estos métodos serán transformados en el SQL apropiado para el sistema de base de datos subyacente por Laravel. Por ejemplo, se generará una cláusula MATCH AGAINST para aplicaciones que utilicen MySQL:

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

#Ordenamiento, agrupación, límite y offset

#Ordenamiento

#El método orderBy

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

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

Para ordenar por múltiples columnas, simplemente puede 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 le permiten ordenar fácilmente los resultados por fecha. Por defecto, el resultado se ordenará por la columna created_at de la tabla. O bien, puede pasar el nombre de la columna por la que desea ordenar:

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

#Orden aleatorio

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

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

#Eliminar ordenamientos existentes

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

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

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

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

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

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

#Agrupación

#Los métodos groupBy y having

Como puede esperar, los métodos groupBy y having pueden usarse 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();

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

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

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

Para construir sentencias having más avanzadas, consulte el método havingRaw.

#Límite y offset

#Los métodos skip y take

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

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

Alternativamente, puede 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 puede querer que ciertas cláusulas de consulta se apliquen solo si se cumple otra condición. Por ejemplo, puede querer aplicar una sentencia where solo si un valor de entrada dado está presente en la solicitud HTTP entrante. Puede lograr esto usando el método when:

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

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

El método when solo ejecuta el closure dado cuando el primer argumento es true. Si el primer argumento es false, el closure no se ejecutará. Así, en el ejemplo anterior, el closure dado al método when solo se invocará si el campo role está presente en la solicitud entrante y evalúa a true.

Puede pasar otro closure como tercer argumento al método when. Este closure solo se ejecutará si el primer argumento evalúa como false. Para ilustrar cómo se puede usar esta característica, la usaremos 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 query builder también proporciona un método insert que puede usarse 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
]);

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

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

El método insertOrIgnore ignorará los errores al insertar registros en la base de datos. Al usar este método, debe tener en cuenta que los errores por registros duplicados serán ignorados y otros tipos de errores también pueden ser ignorados dependiendo del motor de base de datos. Por ejemplo, insertOrIgnore evitará 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 usando una subconsulta para determinar los datos que deben insertarse:

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 auto-incrementales

Si la tabla tiene un id auto-incremental, use 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 usted especifique. El primer argumento del método consiste en los valores a insertar o actualizar, mientras que el segundo argumento lista la(s) columna(s) que identifican de forma única los registros dentro de la tabla asociada. El tercer y último argumento 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 en las columnas departure y destination, Laravel actualizará la columna price de ese registro.

#Sentencias Update

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

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

#Actualizar o insertar

A veces puede querer actualizar un registro existente en la base de datos o crearlo si no existe un registro coincidente. En este escenario, puede usarse el método updateOrInsert. El método updateOrInsert acepta dos argumentos: un array de condiciones para encontrar el registro, y un array de pares columna-valor que indican las columnas a actualizar.

El método updateOrInsert intentará localizar un registro coincidente en la base de datos usando los pares columna-valor del primer argumento. Si el registro existe, será actualizado con los valores del segundo argumento. Si no se encuentra el registro, se insertará uno nuevo con los atributos combinados de ambos argumentos:

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

#Actualización de columnas JSON

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

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

#Incrementar y decrementar

El query builder 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. Puede proporcionarse un segundo argumento para especificar la cantidad por la cual la columna debe incrementarse o decrementarse:

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 puede especificar columnas adicionales para actualizar durante la operación de incremento o decremento:

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

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

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

#Sentencias Delete

El método delete del query builder puede usarse para eliminar registros de la tabla. El método delete devuelve el número de filas afectadas. Puede restringir las sentencias delete agregando cláusulas "where" antes de llamar al método delete:

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

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

Si desea truncar una tabla completa, lo que eliminará todos los registros de la tabla y reiniciará el ID auto-incremental a cero, puede usar el método truncate:

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

#Truncado de tablas y PostgreSQL

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

#Bloqueo pesimista

El query builder también incluye algunas funciones para ayudarle a lograr el "bloqueo pesimista" al ejecutar sus sentencias select. Para ejecutar una sentencia con un "bloqueo compartido", puede llamar al método sharedLock. Un bloqueo compartido evita que las filas seleccionadas sean modificadas hasta que su transacción sea confirmada:

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

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

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

#Depuración

Puede usar los métodos dd y dump mientras construye una consulta para volcar las vinculaciones actuales y el SQL. 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 pueden invocarse en una consulta para volcar el SQL de la consulta con todas las vinculaciones de parámetros correctamente sustituidas:

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

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