Uso del database (basato sul componente database di Laravel)

Ottenere tutte le righe

<?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]);
    }
}

Ottenere colonne specifiche

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

Ottenere una riga

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

Ottenere una colonna

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

Utilizzare il valore del campo id come indice

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

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

Ottenere un singolo valore (campo)

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

Rimuovere i duplicati

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

Risultati per blocchi

Se hai bisogno di elaborare migliaia di record del database, leggere tutti questi dati in una sola volta può richiedere tempo e può causare un superamento della memoria. In questo caso, puoi considerare di utilizzare il metodo chunkById. Questo metodo ottiene un piccolo blocco del set di risultati alla volta e lo passa a una funzione di chiusura per l'elaborazione. Ad esempio, possiamo suddividere tutti i dati della tabella users in piccoli blocchi di 100 record da elaborare:

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

Puoi interrompere il processo di ottenimento dei risultati a blocchi restituendo false nella funzione di chiusura.

Db::table('users')->orderBy('id')->chunkById(100, function ($users) {
    // Elabora i record...

    return false;
});

Nota: Non eliminare dati all'interno del callback, poiché ciò potrebbe causare l'esclusione di alcuni record dal set di risultati.

Aggregazioni

Il costruttore di query fornisce anche vari metodi di aggregazione, come count, max, min, avg, sum, ecc.

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

Verificare se i record esistono

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

Espressioni raw

Prototipo

selectRaw($expression, $bindings = [])

A volte potresti aver bisogno di utilizzare espressioni raw nelle query. Puoi usare selectRaw() per creare un'espressione raw:

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

Allo stesso modo, sono forniti metodi di espressioni raw come whereRaw(), orWhereRaw(), havingRaw(), orHavingRaw(), orderByRaw(), groupByRaw().

Db::raw($value) è anche usato per creare un'espressione raw, ma non supporta il binding dei parametri e deve essere usato con cautela per evitare problemi di iniezione SQL.

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

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

Union

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

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

Where

Prototipo

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

Il primo parametro è il nome della colonna, il secondo è un operatore supportato da qualsiasi sistema di database e il terzo è il valore con cui confrontare la colonna.

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

// Quando l'operatore è l'uguale, può essere omesso, quindi questa espressione ha lo stesso effetto della precedente
$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();

Puoi anche passare un array di condizioni alla funzione where:

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

Il metodo orWhere accetta gli stessi parametri del metodo where:

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

Puoi passare una chiusura al metodo orWhere come primo parametro:

// 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 se il valore del campo è compreso tra due valori dati:

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

whereNotBetween / orWhereNotBetween verifica se il valore del campo è al di fuori di due valori dati:

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

whereIn / whereNotIn / orWhereIn / orWhereNotIn verifica se il valore del campo deve esistere in un array specificato:

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

whereNull / whereNotNull / orWhereNull / orWhereNotNull verifica se il campo specificato deve essere NULL:

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

whereNotNull verifica se il campo specificato non deve essere NULL:

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

whereDate / whereMonth / whereDay / whereYear / whereTime vengono utilizzati per confrontare il valore del campo con una data fornita:

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

whereColumn / orWhereColumn vengono utilizzati per confrontare se i valori di due campi sono uguali:

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

// Puoi anche passare un operatore di confronto
$users = Db::table('users')
                ->whereColumn('updated_at', '>', 'created_at')
                ->get();

// whereColumn può anche ricevere un array
$users = Db::table('users')
                ->whereColumn([
                    ['first_name', '=', 'last_name'],
                    ['updated_at', '>', 'created_at'],
                ])->get();

Raggruppamento parametri

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

Ordinamento casuale

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

L'ordinamento casuale può avere un grande impatto sulle prestazioni del server e non è consigliato.

groupBy / having

$users = Db::table('users')
                ->groupBy('account_id')
                ->having('account_id', '>', 100)
                ->get();
// Puoi passare più parametri al metodo 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();

Inserimento

Inserire una singola riga

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

Inserire più righe

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

ID auto-increment

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

Nota: Quando si utilizza PostgreSQL, il metodo insertGetId utilizzerà per default id come nome del campo auto-incremento. Se desideri ottenere ID da un altro "sequenza", puoi passare il nome del campo come secondo parametro al metodo insertGetId.

Aggiornamento

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

Aggiornare o inserire

A volte potresti voler aggiornare un record esistente nel database o crearne uno se non esiste un record corrispondente:

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

Il metodo updateOrInsert cercherà prima di trovare un record del database corrispondente utilizzando le chiavi e i valori del primo parametro. Se il record esiste, verrà aggiornato con i valori del secondo parametro. Se non viene trovato alcun record, verrà inserito un nuovo record, i dati del nuovo record saranno la combinazione dei due array.

Incremento & decremento

Entrambi questi metodi accettano almeno un parametro: la colonna da modificare. Il secondo parametro è facoltativo e serve a controllare quanto incrementare o decrementare la colonna:

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

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

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

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

Puoi anche specificare i campi da aggiornare durante l'operazione:

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

Eliminare

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

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

Se hai bisogno di svuotare la tabella, puoi utilizzare il metodo truncate, che eliminerà tutte le righe e ripristinerà l'ID auto-increment a zero:

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

Transazioni

Vedi transazioni del database

Lock pessimista

Il costruttore di query include anche alcune funzioni che possono aiutarti a implementare il "lock pessimista" sulla sintassi select. Se desideri implementare un "lock condiviso" in una query, puoi usare il metodo sharedLock. Il lock condiviso impedisce la modifica delle colonne di dati selezionati fino a quando la transazione non viene completata:

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

In alternativa, puoi utilizzare il metodo lockForUpdate. Usando il lock "update", si evita che le righe siano modificate o selezionate da altre lock condivise:

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

Debug

Puoi utilizzare i metodi dd o dump per visualizzare i risultati delle query o le istruzioni SQL. Utilizzando il metodo dd, vengono visualizzate le informazioni di debug e l'esecuzione della richiesta si interrompe. Il metodo dump mostra anch'esso le informazioni di debug, ma non interrompe l'esecuzione della richiesta:

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

Nota
Il debug richiede l'installazione di symfony/var-dumper, il comando è composer require symfony/var-dumper