Uso de la base de datos (basado en el componente de base de datos de Laravel)

Obtener todas las filas

<?php
namespace app\controller;

use support\Request;
use support\Db;

class UserController
{
    public function all(Request $request)
    {
        $users = Db::table('users')->get();
        return view('user/all', ['users' => $users]);
    }
}

Obtener columnas específicas

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

Obtener una fila

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

Obtener una columna

$titles = Db::table('roles')->pluck('title');

Usar el valor del campo id como índice

$roles = Db::table('roles')->pluck('title', 'id');

foreach ($roles as $id => $title) {
    echo $title;
}

Obtener un solo valor (campo)

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

Eliminar duplicados

$email = Db::table('user')->select('nickname')->distinct()->get();

Resultados en bloques

Si necesitas procesar miles de registros de base de datos, leer todos esos datos a la vez puede ser muy lento y propenso a causar problemas de memoria. En este caso, puedes considerar usar el método chunkById. Este método obtiene una pequeña parte del conjunto de resultados a la vez y la pasa a una función de closure para su procesamiento. Por ejemplo, podemos cortar todos los datos de la tabla users en pequeños bloques de 100 registros cada uno:

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

Puedes terminar de seguir obteniendo resultados en bloques devolviendo false en la closure.

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

    return false;
});

Nota: No elimines datos dentro de la función de devolución de llamada, ya que puede resultar en que algunos registros no estén incluidos en el conjunto de resultados

Agregación

El constructor de consultas también proporciona varios métodos de agregación, como count, max, min, avg, sum, etc.

$users = Db::table('users')->count();
$price = Db::table('orders')->max('price');
$price = Db::table('orders')->where('finalized', 1)->avg('price');

Verificar si los registros existen

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

Expresión nativa

Prototipo

selectRaw($expression, $bindings = [])

A veces podrías necesitar utilizar expresiones nativas en tus consultas. Puedes usar selectRaw() para crear una expresión nativa:

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

Además, se proporcionan métodos de expresiones nativas whereRaw(), orWhereRaw(), havingRaw(), orHavingRaw(), orderByRaw(), groupByRaw().

Db::raw($value) también se usa para crear una expresión nativa, pero no tiene la funcionalidad de parámetros vinculados, por lo que debe usarse con precaución para evitar problemas de inyección SQL.

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

Sentencias Join

// join
$users = Db::table('users')
            ->join('contacts', 'users.id', '=', 'contacts.user_id')
            ->join('orders', 'users.id', '=', 'orders.user_id')
            ->select('users.*', 'contacts.phone', 'orders.price')
            ->get();

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

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

// crossJoin    
$users = Db::table('sizes')
            ->crossJoin('colors')
            ->get();

Sentencias Union

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

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

Sentencias Where

Prototipo

where($column, $operator = null, $value = null)

El primer parámetro es el nombre de la columna, el segundo parámetro es cualquier operador admitido por el sistema de base de datos, y el tercero es el valor a comparar en esa columna.

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

// Cuando el operador es igual, se puede omitir, por lo que esta expresión es equivalente a la anterior
$users = Db::table('users')->where('votes', 100)->get();

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

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

El método orWhere recibe los mismos parámetros que el método where:

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

Puedes pasar una closure al método orWhere como primer parámetro:

// SQL: select * from users where votes > 100 or (name = 'Abigail' and votes > 50)
$users = Db::table('users')
            ->where('votes', '>', 100)
            ->orWhere(function($query) {
                $query->where('name', 'Abigail')
                      ->where('votes', '>', 50);
            })
            ->get();

whereBetween / orWhereBetween verifica si el valor del campo está entre dos valores dados:

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

whereNotBetween / orWhereNotBetween verifica si el valor del campo está fuera de dos valores dados:

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

whereIn / whereNotIn / orWhereIn / orWhereNotIn verifica si el valor del campo debe existir en un array especificado:

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

whereNull / whereNotNull / orWhereNull / orWhereNotNull verifica si el campo especificado debe ser NULL:

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

whereNotNull verifica si el campo especificado no debe ser NULL:

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

whereDate / whereMonth / whereDay / whereYear / whereTime se utilizan para comparar el valor del campo con una fecha dada:

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

whereColumn / orWhereColumn se utilizan para comparar si los valores de dos campos son iguales:

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

// También puedes pasar un operador de comparación
$users = Db::table('users')
                ->whereColumn('updated_at', '>', 'created_at')
                ->get();

// El método whereColumn también puede aceptar un array
$users = Db::table('users')
                ->whereColumn([
                    ['first_name', '=', 'last_name'],
                    ['updated_at', '>', 'created_at'],
                ])->get();

Agrupación de parámetros

// select * from users where name = 'John' and (votes > 100 or title = 'Admin')
$users = Db::table('users')
           ->where('name', '=', 'John')
           ->where(function ($query) {
               $query->where('votes', '>', 100)
                     ->orWhere('title', '=', 'Admin');
           })
           ->get();

whereExists

// select * from users where exists ( select 1 from orders where orders.user_id = users.id )
$users = Db::table('users')
           ->whereExists(function ($query) {
               $query->select(Db::raw(1))
                     ->from('orders')
                     ->whereRaw('orders.user_id = users.id');
           })
           ->get();

orderBy

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

Orden aleatorio

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

La orden aleatoria puede afectar gravemente el rendimiento del servidor y no se recomienda su uso.

groupBy / having

$users = Db::table('users')
                ->groupBy('account_id')
                ->having('account_id', '>', 100)
                ->get();
// Puedes pasar múltiples parámetros al método groupBy
$users = Db::table('users')
                ->groupBy('first_name', 'status')
                ->having('account_id', '>', 100)
                ->get();

offset / limit

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

Inserción

Insertar una fila

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

Insertar múltiples filas

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

ID autoincrementable

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

Nota: Cuando se utiliza PostgreSQL, el método insertGetId considerará por defecto id como el nombre de la columna autoincremental. Si deseas obtener un ID de otra "secuencia", puedes pasar el nombre del campo como el segundo parámetro al método insertGetId.

Actualización

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

Actualizar o insertar

A veces podrías querer actualizar un registro existente en la base de datos, o crear uno si no existe un registro coincidente:

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

El método updateOrInsert intentará primero buscar un registro coincidente en la base de datos utilizando las claves y valores del primer parámetro. Si el registro existe, se actualizarán los valores del segundo parámetro. Si no se encuentra registro, se insertará uno nuevo, cuyos datos son la combinación de ambos arrays.

Incrementar y decrementar

Ambos métodos reciben al menos un parámetro: la columna a modificar. El segundo parámetro es opcional y se usa para controlar cuánto se incrementa o decrementa la columna:

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

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

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

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

También puedes especificar los campos que deseas actualizar durante la operación:

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

Eliminar

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

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

Si necesitas vaciar una tabla, puedes usar el método truncate, que eliminará todas las filas y restablecerá el ID autoincrementable a cero:

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

Transacciones

Consulta transacciones de base de datos

Bloqueo pesimista

El constructor de consultas también incluye algunas funciones que pueden ayudarte a implementar "bloqueos pesimistas" en tu sintaxis de select. Para implementar un "bloqueo compartido" en tu consulta, puedes usar el método sharedLock. Un bloqueo compartido evita que las columnas de datos seleccionadas sean modificadas hasta que la transacción se haya confirmado:

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

Alternativamente, puedes usar el método lockForUpdate. Usar un bloqueo de "actualización" evita que las filas sean modificadas o seleccionadas por otros bloqueos compartidos:

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

Depuración

Puedes usar dd o dump para imprimir los resultados de las consultas o las sentencias SQL. Usar el método dd detendrá la ejecución de la solicitud después de mostrar la información de depuración. El método dump también mostrará la información de depuración, pero no detendrá la ejecución de la solicitud:

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

Nota
La depuración requiere instalar symfony/var-dumper, el comando es composer require symfony/var-dumper