Query Builder
Query Builder
Query builder de Laravel provee una forma eficiente de crear y ejecutar queries.
Query builder usa PDO para realizar el bindeo de parámetros para proteger a la aplicación de ataques de Sql Inyección, por lo que no es necesario limpiar o sanear los strings pasados a query builder como query bindings.
Running Database Queries
Retrieving All Rows From a Table
Puedes usar el método table que dispone el facade de DB para empezar una query. El método table devuelve una instancia de fluent query builder para la tabla seleccionada, permitiéndote encadenar más restricciones en la query y finalmente devolver el resultado con el método get
<?php
namespace App\Http\Controllers;
use App\Http\Controllers\Controller;
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 Illuminate\Support**Collection** que contiene el resultado de la query, donde cada valor es almacenado en un stdClass permitiendo acceder al valor de cada columna como una propiedad del objeto
use Illuminate\Support\Facades\DB;
$users = DB::table('users')->get();
foreach ($users as $user) {
echo $user->name;
}Laravel collection provee una variedad de poderosos métodos para mapear y reducir la información.
Retrieving a Single Row / Column From a Table
Si necesitas obtener una única fila de una tabla, lo puedes hacer con first
$user = DB::table('users')->where('name', 'John')->first();
return $user->email;Si solo necesitas obtener el valor de una columna, se puede obtener usando value, esto devuelve el valor de la columna directamente.
$email = DB::table('users')->where('name', 'John')->value('email');Para obtener un único valor por su id, se puede usar find
$user = DB::table('users')->find(3);Retrieving a List of Column Values
Si quieres recuperar una colección con los valores de una sola columna, puedes usar el método pluck
Ejemplo recuperar el título de los usuarios
use Illuminate\Support\Facades\DB;
$titles = DB::table('users')->pluck('title');
foreach ($titles as $title) {
echo $title;
}Se puede especificar una clave para que guarde los valores dentro de esta clave en un array asociativo. Para ello hay que indicar un segundo valor dentro de pluck
$titles = DB::table('users')->pluck('title', 'name');
foreach ($titles as $name => $title) {
echo $title;
}Chunking Results
Si necesitas recuperar muchos registros considera usar el método chunk. Este método permite recuperar una porción de elementos e introduce cada porción en una función para su procesamiento
Ejemplo:
Recuperar un fragmento de 100 registros cada vez
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
DB::table('users')->orderBy('id')->chunk(100, function (Collection $users) {
foreach ($users as $user) {
// ...
}
});Se puede detener el procesamiento de más fragmentos retornando false
DB::table('users')->orderBy('id')->chunk(100, function (Collection $users) {
// Process the records...
return false;
});Si dentro del procesamiento de fragmentos se requiere actualizar registros es mejor usar chunkById que página los resultados según la key 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]);
}
});⚠️ Cuando haces update o delete dentro de registros dentro de la función anónima de chunk, cualquier cambio en la primary key o en los valores de los campos que son foreign key, puede afectar a la query que se realiza usando chunk. Esto puede provocar que algunos registros no se incluyan dentro del resultado.
Streaming Results Lazily
El método lazy trabaja de forma similar a el metodo chunk, en el sentido de que ejecuta la query por fragmentos. Sin embargo, en vez de pasarle cada fragmento de registros a un callback o función, el método lazy devuelve una LazyCollection, la cual te deja interactuar con los resultados como un solo stream.
use Illuminate\Support\Facades\DB;
DB::table('users')->orderBy('id')->lazy()->each(function (object $user) {
// ...
});Una vez más, si planeas realizar un update de los registros mientras iteras sobre ellos, es mejor usar los métodos lazyById or LazyByIdDesc. Estos métodos automáticamente paginarán los resultados en base a la primary key de los registros
DB::table('users')->where('active', false)
->lazyById()->each(function (object $user) {
DB::table('users')
->where('id', $user->id)
->update(['active' => true]);
});⚠️ Cuando realizas un update o un delete mientras iteras sobre ellos, cualquier cambio en la primary key o en la foreign key podría afectar a los resultados devueltos (que haya registros no incluidos en la lista).
Aggregates
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 también se pueden combinar estos métodos con otros métodos para agrupar dependiendo de una condición
$price = DB::table('orders')
->where('finalized', 1)
->avg('price');Determining if Records Exist
En lugar de usar el método count para determinar si existen registros que coincidan 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()) {
// ...
}Select Statements
No siempre se va a querer seleccionar todas las columnas de una tabla. Usando el método select, se puede especificar las columnas a seleccionar.
use Illuminate\Support\Facades\DB;
$users = DB::table('users')
->select('name', 'email as user_email')
->get();El método distinct te permite que los resultados sean distintos, es decir únicos.
$users = DB::table('users')->distinct()->get();Si ya tienes una instancia de query builder y quieres agregarle una columna a una cláusula select, puedes usar addSelect
$query = DB::table('users')->select('name');
$users = $query->addSelect('age')->get();Raw Expressions
Algunas veces se necesita incluir una cadena dentro de una query. Para crear expresión cruda dentro de una query se usa raw.
$users = DB::table('users')
->select(DB::raw('count(*) as user_count, status'))
->where('status', '<>', 1)
->groupBy('status')
->get();En este ejemplo se están sacando dos columnas, user_count que cuenta el número de usuarios y otro el status.
⚠️ Las expresiones crudas serán inyectadas en la query como una cadena de texto, por lo que se tiene que tener especial precaución para evitar la inyección sql.
Raw Methods
En vez de usar el método DB::raw, puedes usar tambien los siguientes metodos para insertar expresiones crudas en varias partes de tu query. Recuerda que Laravel no garantiza que ninguna query usando expresiones crudas esta protegida contra SQL Injection
selectRaw
Este método puede ser usando en lugar de addSelect(DB::raw(/* …. */)). Este método acepta un array opcional de bindeos como segundo argumento.
$orders = DB::table('orders')
->selectRaw('price * ? as price_with_tax', [1.0825])
->get();whereRaw / orWhereRaw
Los metodos whereRaw y orWhereRaw pueden ser usados para inyectar un where crudo en tu query. Estos metodos aceptan un segundo parámetro opcional de tipo array con los bindeos
$orders = DB::table('orders')
->whereRaw('price > IF(state = "TX", ?, 100)', [200])
->get();havingRaw / orHavingRaw
Estos métodos pueden ser usados para proveer una cadena con una expresión cruda para la cláusula “having”. Estos metodos aceptan un segundo parámetro opcional de tipo array con los bindeos
$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 ser usado para proveer una cadena como valor para la cláusula “order by”.
$orders = DB::table('orders')
->orderByRaw('updated_at - created_at DESC')
->get();groupByRaw
El método groupByRaw puede ser usado para proveer una cadena como valor para la cláusula “group by”.
$orders = DB::table('orders')
->select('city', 'state')
->groupByRaw('city, state')
->get();JOINS
Inner Join Clause
El query builder también puede ser utilizado para agregar cláusulas join a tus consultas. Para realizar un inner join básico, puedes usar el método join en una instancia del query builder.
El primer argumento es el nombre de la tabla con la que hacer el join, los siguientes argumentos son los requerimientos para realizar el join.
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();Left Join / Right Join Clause
Si se requiere un left join o rigth 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 Clause
Puedes usar este método para generar la unión cartesiana entre la primera tabla y la tabla del join.
$sizes = DB::table('sizes')
->crossJoin('colors')
->get();Cross join: Para cada elemento de la primera tabla, queremos todos los elementos de la segunda, así por cada elemento de la primera tabla.
Advanced Join Clauses
Puedes especificar joins más avanzadas. Pasando una closure como segundo argumento al método join que recibe Illuminate\Database\Query\JoinClause la cual permite especificar la constraint en la cláusula join.
DB::table('users')
->join('contacts', function (JoinClause $join) {
$join->on('users.id', '=', 'contacts.user_id')->orOn(/* ... */);
})
->get();Si quieres usar “where” en tus joins, puedes usar where y orWhere provista por la JoinClause. En vez de comparar dos columnas, estos métodos van a comparar 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();Subquery Joins
Puedes usar joinSub, leftJoinSub y rightJoinSub para unir una query con una subquery. Cada uno de los tres métodos recibe 3 argumentos. la subquery, el alias de la tabla, y una funcion que define las columnas relacionadas.
En este ejemplo estamos devolviendo una colección de usuarios donde cada registro de usuario contiene la fecha de la ultima creación de sus posts.
$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();Lateral Joins
⚠️ Lateral joins solamente están soportadas por PostgreSQL, MySQL >= 8.0.14, and SQL Server.
Puedes usar los métodos joinLateral y leftJoinLateral para realizar una “unión lateral” con una subconsulta. Cada uno de estos métodos recibe dos argumentos: la subconsulta y su alias de tabla. La(s) condición(es) de unión deben especificarse dentro de la cláusula where de la subconsulta dada. Las uniones 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 para cada una de sus publicaciones de blog más recientes. La condición de unión se especifica con una cláusula whereColumn dentro de la subconsulta, referenciando 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();Unions
Laravel provee un método para unir dos o más queries. Por ejemplo, puedes crear una query inicial y usar union para unirla con más queries.
use Illuminate\Support\Facades\DB;
$first = DB::table('users')
->whereNull('first_name');
$users = DB::table('users')
->whereNull('last_name')
->union($first)
->get();Adicionalmente, query builder provee un método unionAll. Las queries que se combinen usando unionAll no tendrán sus valores duplicados eliminados. El método unionAll se define igual que union.
Basic Where Clauses
Where Clauses
Puedes usar query builder con el método where. La llamada más básica al método where requiere de tres parámetros. El primer argumento es el nombre de la columna, el segundo argumento es un operador, que puede ser cualquiera soportado por la base de datos, el tercer argumento el valor con el que se compara.
Por ejemplo la siguiente query devuelve los usuarios que tengan el valor de votos igual a 100 y el valor de edad sea mayor de 35.
$users = DB::table('users')
->where('votes', '=', 100)
->where('age', '>', 35)
->get();Por conveniencia, si pasas el valor como segundo parámetro sin él =, se asumirá que vas a utilizar el =
$users = DB::table('users')->where('votes', 100)->get();Como se ha mencionado anteriormente, se puede usar cualquier operador soportado por la 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 se le puede pasar un array al método where, cada elemento del array debe ser un array con los 3 parámetros mencionados.
$users = DB::table('users')->where([
['status', '=', '1'],
['subscribed', '<>', '1'],
])->get();⚠️ PDO no soporta el bindeo para los nombres de las columna. A pesar de esto, no se debe nunca permitir que el input del usuario decida el nombre de la columna, incluso en el order by.
Or Where Clauses
Cuando se encaden llamadas a where es como usar el operador and. Puedes usar orWhere para usar el operador or en los where
$users = DB::table('users')
->where('votes', '>', 100)
->orWhere('name', 'John')
->get();Si necesitas agrupar varios where dentro de un orWhere, puedes pasarle una función como primer argumento y definir varios where dentro del orWhere
$users = DB::table('users')
->where('votes', '>', 100)
->orWhere(function (Builder $query) {
$query->where('name', 'Abigail')
->where('votes', '>', 50);
})
->get();Este fragmento de código producirá está query.
select * from users where votes > 100 or (name = 'Abigail' and votes > 50)⚠️ Siempre deberás agrupar llamadas orWhere para evitar funcionamiento inadecuado cuando se aplican scopes globales.
Where Not Clauses
Las cláusulas whereNot o OrWhereNot se usan para negar un grupo de restricciones. Por ejemplo la siguiente query obtiene los productos que no son “clearance”(autorizados) o que tienen un precio menor de 10
$products = DB::table('products')
->whereNot(function (Builder $query) {
$query->where('clearance', true)
->orWhere('price', '<', 10);
})
->get();SELECT * FROM products WHERE NOT (
clearance = 1
OR price < 10
);Where Any / All Clauses
A veces, es posible que necesites aplicar las mismas restricciones de consulta a varias columnas. Por ejemplo, es posible que desees recuperar todos los registros donde cualquier columna de una lista dada sea LIKE un valor determinado. Puedes lograr esto usando el método whereAny.
$users = DB::table('users')
->where('active', true)
->whereAny([
'name',
'email',
'phone',
], 'like', 'Example%')
->get();Esto se traduce en esta consulta
SELECT *
FROM users
WHERE active = true AND (
name LIKE 'Example%' OR
email LIKE 'Example%' OR
phone LIKE 'Example%'
)De manera similar, se puede usar el método whereAll para recuperar registros donde todas las columnas dadas cumplan con una restricción determinada.
$posts = DB::table('posts')
->where('published', true)
->whereAll([
'title',
'content',
], 'like', '%Laravel%')
->get();Esto produce la siguiente query
SELECT *
FROM posts
WHERE published = true AND (
title LIKE '%Laravel%' AND
content LIKE '%Laravel%'
)JSON Where Clauses
Laravel también admite consultas en tipos de columnas JSON en bases de datos que ofrecen soporte para tipos de columnas 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();También puedes usar whereJsonContains para los Json Arrays.
$users = DB::table('users')
->whereJsonContains('options->languages', 'en')
->get();Si tu aplicación usa mysql o postgres puedes usar un array dentro de whereJsonContains
$users = DB::table('users')
->whereJsonContains('options->languages', ['en', 'de'])
->get();Puedes usar whereJsonLength para hacer la query por la longitud del array
$users = DB::table('users')
->whereJsonLength('options->languages', 0)
->get();
$users = DB::table('users')
->whereJsonLength('options->languages', '>', 1)
->get();Additional Where Clauses
whereLike / orWhereLike / whereNotLike / orWhereNotLike
El método whereLike te permite agregar cláusulas “LIKE” a tu consulta para la coincidencia de patrones. Estos métodos proporcionan una forma independiente de la base de datos para 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 no distingue entre mayúsculas y minúsculas.
$users = DB::table('users')
->whereLike('name', '%John%')
->get();Para distinguir entre mayúsculas y minúsculas:
$users = DB::table('users')
->whereLike('name', '%John%', caseSensitive: true)
->get();El método orWhereLike permite agregar un or con una cláusula “Like”.
$users = DB::table('users')
->where('votes', '>', 100)
->orWhereLike('name', '%John%')
->get();El método whereNotLike te permite agregar cláusulas “NOT LIKE” a tu consulta:
$users = DB::table('users')
->whereNotLike('name', '%John%')
->get();De manera similar, puedes usar orWhereNotLike para agregar una cláusula “or” con una condición NOT LIKE:
⚠️ La opción de búsqueda whereLike sensible a mayúsculas y minúsculas actualmente no es compatible con SQL Server.
whereIn / whereNotIn / orWhereIn / orWhereNotIn
El método whereIn verifica que el valor de una columna dada esté contenido dentro del array proporcionado.
$users = DB::table('users')
->whereIn('id', [1, 2, 3])
->get();El método whereNotIn verifica que no este en el array dado.
$users = DB::table('users')
->whereNotIn('id', [1, 2, 3])
->get();También puedes proporcionar un objeto a la 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 de arriba va a producir la siguiente query
select * from comments where user_id in (
select id
from users
where is_active = 1
)⚠️ Si estás agregando un gran array de valores enteros a tu consulta, puedes usar los métodos whereIntegerInRaw o whereIntegerNotInRaw para reducir significativamente el uso de memoria.
whereBetween / orWhereBetween
La cláusula whereBetween comprueba que un valor está entre dos valores
$users = DB::table('users')
->whereBetween('votes', [1, 100])
->get();La cláusula whereNotBetween es la negación, que no esté entre esos 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 es la negación.
$patients = DB::table('patients')
->whereNotBetweenColumns('weight', ['minimum_allowed_weight', 'maximum_allowed_weight'])
->get();De la misma forma para los orWhere …
whereNull / whereNotNull / orWhereNull / orWhereNotNull
El método whereNull verifica que el valor del campo es nulo.
$users = DB::table('users')
->whereNull('updated_at')
->get();El método whereNotNull verifica que el valor del campo no es nulo.
$users = DB::table('users')
->whereNotNull('updated_at')
->get();De la misma forma para los orWhereNull y orWhereNotNul
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 con una hora específica:
$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 sean iguales:
$users = DB::table('users')
->whereColumn('first_name', 'last_name')
->get();También se le puede pasar un operador
$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();Logical Grouping
A veces, es necesario agrupar varias cláusulas “where” dentro de paréntesis para lograr la agrupación lógica deseada en tu consulta. De hecho, generalmente deberías agrupar siempre las llamadas al método orWhere entre paréntesis para evitar un comportamiento inesperado en la consulta. Para lograr esto, puedes pasar una 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 puedes ver, puedes pasar una closure al método where al query builder que inicia un grupo de restricciones. La closure recibirá una instancia del query builder que puedes usar para establecer las restricciones que deben estar contenidas dentro del paréntesis. El ejemplo anterior generará el siguiente SQL:
select * from users where name = 'John' and (votes > 100 or title = 'Admin')⚠️ Siempre debes agrupar las llamadas a orWhere para evitar comportamientos inesperados cuando se aplican scopes globales.
Advanced Where Clauses
Where Exists Clauses
El método whereExists permite escribir cláusulas SQL “where exists”. El método whereExists acepta una closure que recibirá una instancia del query builder, lo que permite definir la consulta que se debe colocar 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();De forma alternativa, se puede usar un query object al metodo whereExists en vez de la closure.
$orders = DB::table('orders')
->select(DB::raw(1))
->whereColumn('orders.user_id', 'users.id');
$users = DB::table('users')
->whereExists($orders)
->get();Los dos ejemplos anteriores producirán esta query.
select * from users
where exists (
select 1
from orders
where orders.user_id = users.id
)Subquery Where Clauses
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 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 bien, 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 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();Full Text Where Clauses
⚠ Las cláusulas de texto completo son compatibles actualmente con MariaDB, MySQL y PostgreSQL.
Los métodos whereFullText y orWhereFullText pueden utilizarse para agregar cláusulas “where” de texto completo a una consulta para columnas que tienen índices de texto completo. Laravel transformará estos métodos en el SQL apropiado para el sistema de base de datos subyacente. Por ejemplo, se generará una cláusula MATCH AGAINST para aplicaciones que utilizan MariaDB o MySQL:
$users = DB::table('users')
->whereFullText('bio', 'web developer')
->get();Ordering, Grouping, Limit and Offset
Ordering
The orderBy Method
El método orderBy te 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 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 varias columnas, puedes invocar orderBy las veces que sean necesarias:
$users = DB::table('users')
->orderBy('name', 'desc')
->orderBy('email', 'asc')
->get();The latest and oldest Methods
Los métodos latest y oldest te permiten ordenar fácilmente los resultados por fecha. Por defecto, el resultado se ordenará por la columna created_at de la tabla. También puedes pasar el nombre de la columna por la que deseas ordenar:
$user = DB::table('users')
->latest()
->first();Random Ordering
El método inRandomOrder puede utilizarse para ordenar los resultados de la consulta de manera aleatoria. Por ejemplo, puedes usar este método para obtener un usuario aleatorio:
$randomUser = DB::table('users')
->inRandomOrder()
->first();Removing Existing Orderings
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();Puedes pasar una columna y una 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();Grouping
The groupBy and having Methods
Como podrías 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();Para filtrar valores en un rango, se puede usar havingBetween
$report = DB::table('orders')
->selectRaw('count(id) as number_of_orders, customer_id')
->groupBy('customer_id')
->havingBetween('number_of_orders', [5, 15])
->get();Le puedes pasar varios argumentos al metodo groupBy para agrupar por multiples columnas
$users = DB::table('users')
->groupBy('first_name', 'status')
->having('account_id', '>', 100)
->get();Para construir declaraciones having mas complejas, mirar **havingRaw** (En esta misma pagina en raw methods)
Limit and Offset
Puedes utilizar 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();De manera similar puedes usar el metodo offset o limit para realizar lo mismo. Estos y los anteriores son metodos equivalentes.
$users = DB::table('users')
->offset(10)
->limit(5)
->get();Conditional Clauses
A veces, es posible que desees que ciertas cláusulas se apliquen a una consulta en función de otra condición. Por ejemplo, es posible que solo quieras 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->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 verdadero. Si el primer argumento es falso, el closure no se ejecutará. Por lo tanto, en el ejemplo anterior, el closure proporcionado al método when solo se invocará si el campo role está presente en la solicitud entrante y evaluado como verdadero.
Puedes pasar otro closure como tercer argumento al método when. Este closure solo se ejecutará si el primer argumento es evaluado como falso. Para ilustrar cómo se puede usar esta característica, la utilizaremos para configurar el ordenamiento por defecto 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();Insert Statements
El query builder también proporciona un método insert que se puede utilizar 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
]);Se pueden insertar muchos registros a la vez pasando un array de arrays. Cada array representa un record que debera ser insertado.
DB::table('users')->insert([
['email' => 'picard@example.com', 'votes' => 0],
['email' => 'janeway@example.com', 'votes' => 0],
]);El método insertOrIgnore ignorará los errores que se produzcan al insertar registros en la base de datos. Al utilizar este método, debes 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 utilizando una subconsulta para determinar los datos que deben ser insertados:
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()));En este ejemplo se guarda en una tabla llamada pruned_users (usuarios podados ⇒ usuarios seleccionados por ciertas condiciones). En este caso inserta en esta tabla los usuarios que no han sido actualizados en el ultimo mes.
Auto-Incrementing IDs
Si la tabla tiene un campo de ID autoincremental, 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]
);⚠ Cuando se utiliza PostgreSQL, el método insertGetId espera que la columna autoincremental se llame id. Si deseas recuperar el ID de una secuencia diferente, puedes pasar el nombre de la columna como segundo parámetro al método insertGetId.
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 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.
⚠ Todas las bases de datos, excepto SQL Server, requieren que las columnas en el segundo argumento del método upsert tengan un índice “primario” o “único”. Además, los controladores de bases de datos MariaDB y MySQL ignoran el segundo argumento del método upsert y siempre utilizan los índices “primarios” y “únicos” de la tabla para detectar registros existentes.
Update Statements
Además de insertar registros en la base de datos, el query builder 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 columnas y valores que indican las columnas que deben ser actualizadas. El método update devuelve el número de filas afectadas. Puedes restringir la consulta de actualización utilizando cláusulas where:
$affected = DB::table('users')
->where('id', 1)
->update(['votes' => 1]);Update or Insert
A veces, es posible que desees actualizar un registro existente en la base de datos o crearlo si no existe ningún registro coincidente. En este escenario, se puede utilizar 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 columnas y valores 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 un closure al método updateOrInsert para personalizar los atributos que se actualizan o se insertan en la base de datos en función de 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,
],
);Updating JSON Columns
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]);Increment and Decrement
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 que se desea modificar. Se puede proporcionar un segundo argumento para especificar la cantidad en la que la columna debe ser incrementada o decrementada:
DB::table('users')->increment('votes');
DB::table('users')->increment('votes', 5);
DB::table('users')->decrement('votes');
DB::table('users')->decrement('votes', 5);Si lo necesitas, puedes especificar columnas adicionales para actualizar durante la operación de incremento o decremento
DB::table('users')->increment('votes', 1, ['name' => 'John']);Además, se puede incrementar o decrementar multiples columnas a la vez con incrementEach o decrementEach
DB::table('users')->incrementEach([
'votes' => 5,
'balance' => 100,
]);Delete Statements
El método delete del query builder se puede utilizar para eliminar registros de la tabla. El método delete devuelve el número de filas afectadas. Puedes restringir las declaraciones de eliminación 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 quieres borrar todos los registros de una tabla, existe el método truncate
DB::table('users')->truncate();Al truncar una base de datos de PostgreSQL, se aplicará el comportamiento CASCADE. Esto significa que también se eliminarán todos los registros relacionados por claves foráneas en otras tablas.
Pessimistic Locking
El constructor de consultas también incluye algunas funciones para ayudarte a lograr un “pessimistic locking” al ejecutar tus sentencias select. Para ejecutar una sentencia con un “bloqueo compartido”, puedes llamar al método sharedLock. Un bloqueo compartido impide que las filas seleccionadas sean modificadas hasta que tu transacción sea commited:
DB::table('users')
->where('votes', '>', 100)
->sharedLock()
->get();De forma alternativa, puedes usar lockForUpdate que previene que las filas seleccionadas sean modificadas o seleccionadas por otro shared lock.
DB::table('users')
->where('votes', '>', 100)
->lockForUpdate()
->get();Debugging
Puedes usar los métodos dd y dump mientras construyes una consulta para mostrar la consulta que se ejecutará.
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 mostrar el SQL de la consulta con todos los bindings debidamente sustituidos:
DB::table('users')->where('votes', '>', 100)->dumpRawSql();
DB::table('users')->where('votes', '>', 100)->ddRawSql();