Prologo

Primi Passi

Architettura

Le Basi

Approfondimenti

Sicurezza

Database

Eloquent ORM

Testing

Package

Query Builder

Introduzione

Il query builder di Laravel offre un’interfaccia comoda e fluente per creare ed eseguire query al database. Può essere utilizzato per la maggior parte delle operazioni sul database nella tua applicazione e funziona perfettamente con tutti i sistemi di database supportati da Laravel.

Il query builder di Laravel utilizza il binding dei parametri PDO per proteggere la tua applicazione dagli attacchi di SQL injection. Non è necessario pulire o sanitizzare le stringhe passate al query builder come binding delle query.

PDO non supporta il binding dei nomi delle colonne. Pertanto, non dovresti mai permettere che l’input dell’utente determini i nomi delle colonne usate nelle tue query, comprese le colonne "order by".

Eseguire Query sul Database

Ottenere tutte le righe da una tabella

Puoi usare il metodo table fornito dalla facciata DB per iniziare una query. Il metodo table restituisce un’istanza di query builder fluente per la tabella specificata, permettendoti di aggiungere ulteriori vincoli alla query e infine recuperare i risultati della query usando il metodo get:

<?php

namespace App\Http\Controllers;

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

class UserController extends Controller
{
    /**
     * Mostra una lista di tutti gli utenti dell'applicazione.
     */
    public function index(): View
    {
        $users = DB::table('users')->get();

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

Il metodo get restituisce un’istanza di Illuminate\Support\Collection contenente i risultati della query, dove ogni risultato è un’istanza dell’oggetto PHP stdClass. Puoi accedere al valore di ogni colonna accedendo alla colonna come proprietà dell’oggetto:

use Illuminate\Support\Facades\DB;

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

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

Le collezioni di Laravel offrono una varietà di metodi estremamente potenti per mappare e ridurre i dati. Per maggiori informazioni sulle collezioni di Laravel, consulta la documentazione sulle collezioni.

Recuperare una Singola Riga / Colonna da una Tabella

Se hai bisogno di recuperare una sola riga da una tabella del database, puoi usare il metodo first della facade DB. Questo metodo restituirà un singolo oggetto stdClass:

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

return $user->email;

Se desideri recuperare una singola riga da una tabella del database, ma lanciare un’eccezione Illuminate\Database\RecordNotFoundException se non viene trovata nessuna riga corrispondente, puoi usare il metodo firstOrFail. Se l’eccezione RecordNotFoundException non viene catturata, una risposta HTTP 404 viene automaticamente inviata al client:

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

Se non hai bisogno di un’intera riga, puoi estrarre un singolo valore da un record usando il metodo value. Questo metodo restituirà direttamente il valore della colonna:

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

Per recuperare una singola riga tramite il valore della sua colonna id, usa il metodo find:

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

Recuperare una Lista di Valori di Colonna

Se desideri ottenere un’istanza di Illuminate\Support\Collection contenente i valori di una singola colonna, puoi utilizzare il metodo pluck. In questo esempio, recupereremo una collezione di titoli utente:

use Illuminate\Support\Facades\DB;

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

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

Puoi specificare la colonna che la collezione risultante deve usare come chiavi passando un secondo argomento al metodo pluck:

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

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

Suddivisione dei Risultati

Se devi gestire migliaia di record nel database, considera di usare il metodo chunk fornito dalla facade DB. Questo metodo recupera un piccolo blocco di risultati alla volta e passa ogni blocco a una closure per l’elaborazione. Ad esempio, recuperiamo l’intera tabella users a blocchi di 100 record alla volta:

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

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

Puoi interrompere l’elaborazione dei blocchi successivi restituendo false dalla closure:

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

    return false;
});

Se stai aggiornando record nel database mentre suddividi i risultati, i risultati dei blocchi potrebbero cambiare in modi imprevisti. Se prevedi di aggiornare i record recuperati durante la suddivisione, è sempre meglio usare il metodo chunkById. Questo metodo pagina automaticamente i risultati basandosi sulla chiave primaria del record:

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

Poiché i metodi chunkById e lazyById aggiungono le proprie condizioni "where" alla query eseguita, dovresti tipicamente raggruppare logicamente le tue condizioni all’interno di una closure:

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

Quando aggiorni o elimini record all’interno della callback del chunk, eventuali modifiche alla chiave primaria o alle chiavi esterne potrebbero influenzare la query del chunk. Questo potrebbe potenzialmente far sì che alcuni record non vengano inclusi nei risultati suddivisi.

Streaming dei Risultati in Modo Lazy

Il metodo lazy funziona in modo simile a il metodo chunk poiché esegue la query a blocchi. Tuttavia, invece di passare ogni blocco a una callback, il metodo lazy() restituisce una LazyCollection, che ti permette di interagire con i risultati come un unico flusso:

use Illuminate\Support\Facades\DB;

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

Ancora una volta, se prevedi di aggiornare i record recuperati durante l’iterazione, è meglio usare i metodi lazyById o lazyByIdDesc. Questi metodi paginano automaticamente i risultati basandosi sulla chiave primaria del record:

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

Quando aggiorni o elimini record durante l’iterazione, eventuali modifiche alla chiave primaria o alle chiavi esterne potrebbero influenzare la query a blocchi. Questo potrebbe far sì che alcuni record non vengano inclusi nei risultati.

Aggregati

Il query builder offre diversi metodi per ottenere valori aggregati come count, max, min, avg e sum. Puoi chiamare uno di questi metodi dopo aver costruito la tua query:

use Illuminate\Support\Facades\DB;

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

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

Naturalmente, puoi combinare questi metodi con altre clausole per perfezionare come viene calcolato il valore aggregato:

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

Verificare se esistono record

Invece di usare il metodo count per determinare se esistono record che soddisfano i vincoli della tua query, puoi utilizzare i metodi exists e doesntExist:

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

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

Istruzioni Select

Specificare una Clausola Select

Non sempre vuoi selezionare tutte le colonne da una tabella del database. Utilizzando il metodo select, puoi specificare una clausola "select" personalizzata per la query:

use Illuminate\Support\Facades\DB;

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

Il metodo distinct ti permette di forzare la query a restituire risultati distinti:

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

Se hai già un’istanza di query builder e desideri aggiungere una colonna alla clausola select esistente, puoi usare il metodo addSelect:

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

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

Espressioni Raw

A volte potresti dover inserire una stringa arbitraria in una query. Per creare un’espressione di stringa raw, puoi utilizzare il metodo raw fornito dal facade DB:

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

Le istruzioni raw verranno inserite nella query come stringhe, quindi devi prestare estrema attenzione per evitare di creare vulnerabilità di SQL injection.

Metodi Raw

Invece di utilizzare il metodo DB::raw, puoi anche usare i seguenti metodi per inserire un’espressione raw in diverse parti della tua query. Ricorda che Laravel non può garantire che una query che utilizza espressioni raw sia protetta da vulnerabilità di SQL injection.

selectRaw

Puoi usare il metodo selectRaw al posto di addSelect(DB::raw(/* ... */)). Questo metodo accetta un array opzionale di bindings come secondo argomento:

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

whereRaw / orWhereRaw

I metodi whereRaw e orWhereRaw possono essere usati per inserire una clausola "where" raw nella tua query. Questi metodi accettano un array opzionale di bindings come secondo argomento:

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

havingRaw / orHavingRaw

I metodi havingRaw e orHavingRaw possono essere utilizzati per fornire una stringa raw come valore della clausola "having". Questi metodi accettano un array opzionale di bindings come secondo argomento:

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

orderByRaw

Il metodo orderByRaw può essere usato per fornire una stringa raw come valore della clausola "order by":

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

groupByRaw

Il metodo groupByRaw può essere utilizzato per fornire una stringa raw come valore della clausola group by:

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

Joins

Clausola Inner Join

Il query builder può anche essere utilizzato per aggiungere clausole join alle tue query. Per effettuare un semplice "inner join", puoi usare il metodo join su un’istanza di query builder. Il primo argomento del metodo join è il nome della tabella a cui vuoi unirti, mentre gli argomenti successivi definiscono le condizioni sulle colonne per il join. Puoi persino unire più tabelle in un’unica query:

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

Clausola Left Join / Right Join

Se vuoi eseguire una "left join" o "right join" invece di una "inner join", usa i metodi leftJoin o rightJoin. Questi metodi hanno la stessa firma del metodo 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();

Clausola Cross Join

Puoi utilizzare il metodo crossJoin per eseguire una "cross join". Le cross join creano un prodotto cartesiano tra la prima tabella e quella unita:

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

Clausole di Join Avanzate

Puoi anche specificare clausole di join più avanzate. Per iniziare, passa una closure come secondo argomento al metodo join. La closure riceverà un’istanza di Illuminate\Database\Query\JoinClause che ti permette di definire le condizioni sulla clausola di "join":

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

Se vuoi usare una clausola "where" nei tuoi join, puoi utilizzare i metodi where e orWhere forniti dall’istanza JoinClause. Invece di confrontare due colonne, questi metodi confronteranno la colonna con un valore:

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

Subquery Join

Puoi usare i metodi joinSub, leftJoinSub e rightJoinSub per unire una query a una subquery. Ogni metodo accetta tre argomenti: la subquery, l’alias della tabella e una closure che definisce le colonne correlate. In questo esempio, recupereremo un insieme di utenti dove ogni record utente include il timestamp created_at del post del blog pubblicato più recentemente dall’utente:

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

Le lateral joins sono attualmente supportate da PostgreSQL, MySQL >= 8.0.14 e SQL Server.

Puoi utilizzare i metodi joinLateral e leftJoinLateral per eseguire una "lateral join" con una sottoquery. Ognuno di questi metodi accetta due argomenti: la sottoquery e l’alias della tabella. Le condizioni di join devono essere specificate all’interno della clausola where della sottoquery. Le lateral joins vengono valutate per ogni riga e possono fare riferimento a colonne al di fuori della sottoquery.

In questo esempio, recupereremo una collezione di utenti insieme ai tre post del blog più recenti di ciascun utente. Ogni utente può generare fino a tre righe nel set di risultati: una per ciascuno dei suoi post più recenti. La condizione di join è specificata con una clausola whereColumn all’interno della sottoquery, facendo riferimento alla riga utente corrente:

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

Unioni

Il query builder fornisce anche un metodo comodo per "unire" due o più query insieme. Ad esempio, puoi creare una query iniziale e utilizzare il metodo union per unirla con altre query:

use Illuminate\Support\Facades\DB;

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

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

Oltre al metodo union, il query builder mette a disposizione il metodo unionAll. Le query combinate usando il metodo unionAll non rimuovono i risultati duplicati. Il metodo unionAll ha la stessa firma del metodo union.

Clausole Where di base

Clausole Where

Puoi usare il metodo where del query builder per aggiungere clausole "where" alla query. La chiamata più semplice al metodo where richiede tre argomenti. Il primo argomento è il nome della colonna. Il secondo argomento è un operatore, che può essere uno qualsiasi degli operatori supportati dal database. Il terzo argomento è il valore con cui confrontare il valore della colonna.

Ad esempio, la seguente query recupera gli utenti dove il valore della colonna votes è uguale a 100 e il valore della colonna age è maggiore di 35:

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

Per comodità, se vuoi verificare che una colonna sia = a un dato valore, puoi passare il valore come secondo argomento al metodo where. Laravel assumerà che tu voglia usare l’operatore =:

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

Come detto prima, puoi usare qualsiasi operatore supportato dal tuo sistema di database:

    $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. Ogni elemento dell’array dovrebbe essere un array contenente i tre argomenti tipicamente passati al metodo where:

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

PDO non supporta il binding dei nomi delle colonne. Pertanto, non dovresti mai permettere che l’input dell’utente determini i nomi delle colonne referenziate dalle tue query, comprese le colonne "order by".

MySQL e MariaDB convertono automaticamente le stringhe in interi nei confronti tra stringhe e numeri. In questo processo, le stringhe non numeriche vengono convertite in 0, il che può portare a risultati inaspettati. Ad esempio, se la tua tabella ha una colonna secret con un valore di aaa e esegui User::where('secret', 0), quella riga verrà restituita. Per evitare ciò, assicurati che tutti i valori siano convertiti ai loro tipi appropriati prima di usarli nelle query.

Clausole Or Where

Quando si concatenano chiamate al metodo where del query builder, le clausole "where" verranno unite usando l’operatore and. Tuttavia, puoi usare il metodo orWhere per unire una clausola alla query usando l’operatore or. Il metodo orWhere accetta gli stessi argomenti del metodo where:

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

Se hai bisogno di raggruppare una condizione "or" tra parentesi, puoi passare una closure come primo argomento al metodo orWhere:

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

L’esempio sopra produrrà il seguente SQL:

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

Dovresti sempre raggruppare le chiamate orWhere per evitare comportamenti inaspettati quando vengono applicati degli scope globali.

Clausole Where Not

I metodi whereNot e orWhereNot possono essere utilizzati per negare un gruppo di vincoli di query. Ad esempio, la seguente query esclude i prodotti che sono in saldo o che hanno un prezzo inferiore a dieci:

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

Clausole Where Any / All / None

A volte potresti dover applicare gli stessi criteri di query a più colonne. Ad esempio, potresti voler recuperare tutti i record in cui una qualsiasi colonna in una lista specificata è LIKE a un certo valore. Puoi fare questo usando il metodo whereAny:

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

La query sopra genererà il seguente SQL:

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

Allo stesso modo, il metodo whereAll può essere utilizzato per recuperare i record in cui tutte le colonne specificate soddisfano un determinato criterio:

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

La query sopra genererà il seguente SQL:

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

Il metodo whereNone può essere usato per recuperare i record in cui nessuna delle colonne specificate soddisfa un determinato criterio:

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

La query sopra genererà il seguente SQL:

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

Clausole Where JSON

Laravel supporta anche l’interrogazione di colonne di tipo JSON su database che supportano questo tipo di colonne. Attualmente, ciò include MariaDB 10.3+, MySQL 8.0+, PostgreSQL 12.0+, SQL Server 2017+ e SQLite 3.39.0+. Per interrogare una colonna JSON, utilizza l’operatore ->:

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

Puoi usare whereJsonContains per interrogare array JSON:

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

Se la tua applicazione usa i database MariaDB, MySQL o PostgreSQL, puoi passare un array di valori al metodo whereJsonContains:

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

Puoi usare il metodo whereJsonLength per interrogare array JSON in base alla loro lunghezza:

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

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

Clausole Where Aggiuntive

whereLike / orWhereLike / whereNotLike / orWhereNotLike

Il metodo whereLike permette di aggiungere clausole "LIKE" alla tua query per il confronto di pattern. Questi metodi offrono un modo indipendente dal database per eseguire query di confronto stringhe, con la possibilità di attivare o disattivare la sensibilità al maiuscolo/minuscolo. Per impostazione predefinita, il confronto delle stringhe non distingue tra maiuscole e minuscole:

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

Puoi abilitare una ricerca sensibile al maiuscolo/minuscolo tramite l’argomento caseSensitive:

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

Il metodo orWhereLike permette di aggiungere una clausola "or" con una condizione LIKE:

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

Il metodo whereNotLike permette di aggiungere clausole "NOT LIKE" alla tua query:

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

Allo stesso modo, puoi usare orWhereNotLike per aggiungere una clausola "or" con una condizione NOT LIKE:

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

L’opzione di ricerca sensibile al maiuscolo/minuscolo whereLike attualmente non è supportata in SQL Server.

whereIn / whereNotIn / orWhereIn / orWhereNotIn

Il metodo whereIn verifica che il valore di una determinata colonna sia contenuto nell’array fornito:

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

Il metodo whereNotIn verifica che il valore della colonna specificata non sia contenuto nell’array fornito:

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

Puoi anche fornire un oggetto query come secondo argomento del metodo whereIn:

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

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

L’esempio sopra genererà la seguente SQL:

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

Se stai aggiungendo un grande array di binding interi alla tua query, i metodi whereIntegerInRaw o whereIntegerNotInRaw possono essere utilizzati per ridurre notevolmente l’uso della memoria.

whereBetween / orWhereBetween

Il metodo whereBetween verifica che il valore di una colonna sia compreso tra due valori:

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

whereNotBetween / orWhereNotBetween

Il metodo whereNotBetween verifica che il valore di una colonna sia al di fuori di due valori:

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

whereBetweenColumns / whereNotBetweenColumns / orWhereBetweenColumns / orWhereNotBetweenColumns

Il metodo whereBetweenColumns verifica che il valore di una colonna sia compreso tra i due valori di due colonne nella stessa riga della tabella:

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

Il metodo whereNotBetweenColumns verifica che il valore di una colonna sia al di fuori dei due valori di due colonne nella stessa riga della tabella:

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

whereNull / whereNotNull / orWhereNull / orWhereNotNull

Il metodo whereNull verifica che il valore della colonna specificata sia NULL:

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

Il metodo whereNotNull verifica che il valore della colonna non sia NULL:

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

whereDate / whereMonth / whereDay / whereYear / whereTime

Il metodo whereDate può essere utilizzato per confrontare il valore di una colonna con una data:

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

Il metodo whereMonth può essere utilizzato per confrontare il valore di una colonna con un mese specifico:

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

Il metodo whereDay può essere utilizzato per confrontare il valore di una colonna con un giorno specifico del mese:

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

Il metodo whereYear può essere utilizzato per confrontare il valore di una colonna con un anno specifico:

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

Il metodo whereTime può essere utilizzato per confrontare il valore di una colonna con un orario specifico:

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

whereColumn / orWhereColumn

Il metodo whereColumn può essere utilizzato per verificare che due colonne siano uguali:

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

Puoi anche passare un operatore di confronto al metodo whereColumn:

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

Puoi anche passare un array di confronti di colonne al metodo whereColumn. Queste condizioni saranno unite utilizzando l’operatore and:

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

Raggruppamento Logico

A volte potresti aver bisogno di raggruppare diverse clausole "where" all’interno di parentesi per ottenere il raggruppamento logico desiderato nella tua query. Infatti, dovresti generalmente sempre raggruppare le chiamate al metodo orWhere tra parentesi per evitare comportamenti imprevisti della query. Per fare ciò, puoi passare una closure al metodo where:

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

Come puoi vedere, passare una closure al metodo where istruisce il query builder a iniziare un gruppo di vincoli. La closure riceverà un’istanza del query builder che puoi usare per impostare i vincoli che dovrebbero essere contenuti all’interno del gruppo di parentesi. L’esempio sopra produrrà il seguente SQL:

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

Dovresti sempre raggruppare le chiamate orWhere per evitare comportamenti imprevisti quando vengono applicati scope globali.

Clausole Where Avanzate

Clausole Where Exists

Il metodo whereExists ti permette di scrivere clausole SQL "where exists". Il metodo whereExists accetta una closure che riceverà un’istanza di query builder, permettendoti di definire la query che sarà inserita all’interno della clausola "exists":

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

In alternativa, puoi fornire un oggetto query al metodo whereExists invece di una closure:

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

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

Entrambi gli esempi sopra produrranno il seguente SQL:

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

Clausole Where con Sottoquery

A volte potrebbe essere necessario creare una clausola "where" che confronta i risultati di una sottoquery con un valore specifico. Puoi fare questo passando una closure e un valore al metodo where. Ad esempio, la seguente query recupera tutti gli utenti che hanno una "membership" recente di un tipo specifico:

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

Oppure, potrebbe essere necessario creare una clausola "where" che confronta una colonna con i risultati di una sottoquery. Puoi fare questo passando una colonna, un operatore e una closure al metodo where. Ad esempio, la seguente query recupera tutti i record di reddito dove l’importo è inferiore alla media:

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

Clausole Where Full Text

Le clausole where a testo completo sono attualmente supportate da MariaDB, MySQL e PostgreSQL.

I metodi whereFullText e orWhereFullText possono essere utilizzati per aggiungere clausole "where" a testo completo a una query per colonne che possiedono full text indexes. Questi metodi verranno trasformati nel SQL appropriato per il sistema di database sottostante da Laravel. Ad esempio, verrà generata una clausola MATCH AGAINST per le applicazioni che utilizzano MariaDB o MySQL:

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

Ordinamento, Raggruppamento, Limite e Offset

Ordinamento

Il metodo orderBy

Il metodo orderBy ti permette di ordinare i risultati della query in base a una colonna specifica. Il primo argomento del metodo orderBy deve essere la colonna per cui vuoi ordinare, mentre il secondo argomento definisce la direzione dell’ordinamento e può essere asc o desc:

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

Per ordinare per più colonne, puoi semplicemente chiamare orderBy tutte le volte necessarie:

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

I metodi latest e oldest

I metodi latest e oldest ti permettono di ordinare facilmente i risultati per data. Per impostazione predefinita, il risultato verrà ordinato in base alla colonna created_at della tabella. Oppure, puoi specificare il nome della colonna su cui desideri ordinare:

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

Ordinamento Casuale

Il metodo inRandomOrder può essere utilizzato per ordinare i risultati della query in modo casuale. Ad esempio, puoi usare questo metodo per ottenere un utente casuale:

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

Rimuovere Ordinamenti Esistenti

Il metodo reorder elimina tutte le clausole "order by" precedentemente applicate alla query:

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

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

Puoi specificare una colonna e una direzione quando utilizzi il metodo reorder per rimuovere tutte le clausole "order by" esistenti e applicare un nuovo ordinamento alla query:

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

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

Grouping

I metodi groupBy e having

Come puoi aspettarti, i metodi groupBy e having possono essere usati per raggruppare i risultati delle query. La firma del metodo having è simile a quella del metodo where:

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

Puoi usare il metodo havingBetween per filtrare i risultati entro un intervallo specifico:

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

Puoi passare più argomenti al metodo groupBy per raggruppare per più colonne:

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

Per creare condizioni having più avanzate, consulta il metodo havingRaw.

Limite e Offset

I metodi skip e take

Puoi usare i metodi skip e take per limitare il numero di risultati restituiti dalla query o per saltare un determinato numero di risultati nella query:

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

In alternativa, puoi usare i metodi limit e offset. Questi metodi sono funzionalmente equivalenti ai metodi take e skip, rispettivamente:

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

Clausole Condizionali

A volte potresti voler applicare certe clausole a una query in base a un’altra condizione. Ad esempio, potresti voler utilizzare una clausola where solo se un determinato valore di input è presente nella richiesta HTTP in arrivo. Puoi ottenere questo usando il metodo when:

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

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

Il metodo when esegue la closure solo quando il primo argomento è true. Se il primo argomento è false, la closure non verrà eseguita. Quindi, nell’esempio sopra, la closure passata al metodo when verrà invocata solo se il campo role è presente nella richiesta in arrivo e risulta true.

Puoi passare un’altra closure come terzo argomento al metodo when. Questa closure verrà eseguita solo se il primo argomento è false. Per illustrare come può essere utilizzata questa funzionalità, la useremo per configurare l’ordinamento predefinito di una query:

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

Istruzioni di Inserimento

Il query builder fornisce anche un metodo insert che può essere usato per inserire record nella tabella del database. Il metodo insert accetta un array di nomi di colonne e valori:

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

Puoi inserire più record contemporaneamente passando un array di array. Ogni array rappresenta un record che deve essere inserito nella tabella:

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

Il metodo insertOrIgnore ignorerà gli errori durante l’inserimento dei record nel database. Quando si utilizza questo metodo, bisogna essere consapevoli che gli errori di duplicazione dei record verranno ignorati e altri tipi di errori potrebbero essere ignorati a seconda del motore del database. Ad esempio, insertOrIgnore salta la modalità rigorosa di MySQL:

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

Il metodo insertUsing inserirà nuovi record nella tabella utilizzando una sottoquery per determinare i dati da inserire:

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

ID ad incremento automatico

Se la tabella ha un id ad incremento automatico, usa il metodo insertGetId per inserire un record e poi ottenere l’ID:

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

Quando usi PostgreSQL, il metodo insertGetId si aspetta che la colonna ad incremento automatico si chiami id. Se vuoi ottenere l’ID da una "sequence" diversa, puoi passare il nome della colonna come secondo parametro al metodo insertGetId.

Upsert

Il metodo upsert inserisce i record che non esistono e aggiorna quelli già presenti con i nuovi valori che puoi specificare. Il primo argomento del metodo consiste nei valori da inserire o aggiornare, mentre il secondo argomento elenca la/e colonna/e che identificano univocamente i record nella tabella associata. Il terzo e ultimo argomento del metodo è un array di colonne che devono essere aggiornate se un record corrispondente esiste già nel database:

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

Nell’esempio sopra, Laravel tenterà di inserire due record. Se un record esiste già con gli stessi valori nelle colonne departure e destination, Laravel aggiornerà la colonna price di quel record.

Tutti i database tranne SQL Server richiedono che le colonne nel secondo argomento del metodo upsert abbiano un indice "primary" o "unique". Inoltre, i driver di database MariaDB e MySQL ignorano il secondo argomento del metodo upsert e utilizzano sempre gli indici "primary" e "unique" della tabella per rilevare i record esistenti.

Istruzioni di aggiornamento

Oltre a inserire record nel database, il query builder può anche aggiornare record esistenti usando il metodo update. Il metodo update, come il metodo insert, accetta un array di coppie colonna-valore che indicano le colonne da aggiornare. Il metodo update restituisce il numero di record interessati. Puoi limitare la query di update usando clausole where:

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

Aggiorna o Inserisci

A volte potresti voler aggiornare un record esistente nel database o crearne uno nuovo se non esiste alcun record corrispondente. In questo scenario, può essere utilizzato il metodo updateOrInsert. Il metodo updateOrInsert accetta due argomenti: un array di condizioni per trovare il record e un array di coppie colonna-valore che indicano le colonne da aggiornare.

Il metodo updateOrInsert tenterà di individuare un record corrispondente nel database utilizzando le coppie colonna-valore del primo argomento. Se il record esiste, verrà aggiornato con i valori del secondo argomento. Se il record non viene trovato, verrà inserito un nuovo record con gli attributi uniti di entrambi gli argomenti:

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

Puoi fornire una closure al metodo updateOrInsert per personalizzare gli attributi che vengono aggiornati o inseriti nel database in base all’esistenza di un record corrispondente:

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

Aggiornamento delle colonne JSON

Per aggiornare una colonna JSON, usa la sintassi -> per modificare la chiave corretta nell’oggetto JSON. Questa operazione è supportata da MariaDB 10.3+, MySQL 5.7+ e PostgreSQL 9.5+:

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

Incremento e Decremento

Il query builder fornisce metodi comodi per incrementare o decrementare il valore di una determinata colonna. Entrambi i metodi accettano almeno un argomento: la colonna da modificare. È possibile fornire un secondo argomento per specificare l’ammontare di incremento o decremento:

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

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

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

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

Se necessario, puoi anche specificare colonne aggiuntive da aggiornare durante l’operazione di incremento o decremento:

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

Inoltre, puoi incrementare o decrementare più colonne contemporaneamente usando i metodi incrementEach e decrementEach:

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

Eliminare Record

Il metodo delete del query builder può essere usato per eliminare record dalla tabella. Il metodo delete restituisce il numero di righe interessate. Puoi limitare le istruzioni delete aggiungendo clausole "where" prima di chiamare il metodo delete:

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

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

Se desideri troncare un’intera tabella, rimuovendo tutti i record e resettando l’ID auto-incrementante a zero, puoi usare il metodo truncate:

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

Truncate delle Tabelle e PostgreSQL

Quando si tronca un database PostgreSQL, verrà applicato il comportamento CASCADE. Ciò significa che anche tutti i record correlati tramite chiavi esterne in altre tabelle saranno eliminati.

Pessimistic Locking

Il query builder include alcune funzioni che ti aiutano a implementare il "pessimistic locking" durante l’esecuzione delle tue istruzioni select. Per eseguire un’istruzione con un "shared lock", puoi chiamare il metodo sharedLock. Un shared lock impedisce la modifica delle righe selezionate fino a quando la tua transazione non viene confermata:

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

In alternativa, puoi usare il metodo lockForUpdate. Un lock "for update" impedisce la modifica dei record selezionati o la loro selezione con un altro shared lock:

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

Debugging

Puoi utilizzare i metodi dd e dump durante la costruzione di una query per visualizzare i binding e l’SQL correnti. Il metodo dd mostrerà le informazioni di debug e interromperà l’esecuzione della richiesta. Il metodo dump mostrerà le informazioni di debug ma permetterà alla richiesta di continuare:

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

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

I metodi dumpRawSql e ddRawSql possono essere utilizzati su una query per visualizzare l’SQL con tutti i binding dei parametri correttamente sostituiti:

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

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

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *

Laravel-Italia.it è il sito della Community Italiana di Laravel.
2024 — Laravel-Italia