Query Builder - Knowledge of Laravel

Cos’è il Query Builder

Il Query Builder di Laravel offre un’interfaccia fluida per costruire ed eseguire query sul database. Parte da DB::table() e concatena i metodi.

use Illuminate\Support\Facades\DB;

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

Internamente usa il parameter binding di PDO, quindi la protezione da SQL injection è automatica.

Il Query Builder funziona su tutti i database supportati (MySQL, MariaDB, PostgreSQL, SQLite, SQL Server). Il codice non cambia cambiando database.

Quando usare il Query Builder o Eloquent

Situazione	Consigliato
Servono modelli e relazioni	Eloquent
Aggregazioni e report complessi	Query Builder
Prestazioni su grandi volumi	Query Builder
Operazioni semplici su tabelle esistenti	Query Builder
Migration e seeder	Query Builder

Recupero dei dati

Tutti i record

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

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

get() restituisce Illuminate\Support\Collection. Ogni record è uno stdClass.

Un solo record

$user = DB::table('users')->where('name', 'Mario Rossi')->first();
$user = DB::table('users')->where('name', 'Mario Rossi')->firstOrFail();
$email = DB::table('users')->where('name', 'Mario Rossi')->value('email');
$user = DB::table('users')->find(3);

Elenchi di colonne

$emails = DB::table('users')->pluck('email');
$emailByName = DB::table('users')->pluck('email', 'name');

Elaborazione a blocchi

use Illuminate\Support\Collection;

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

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

Se aggiorni/elimini record mentre esegui chunk, usa chunkById() per evitare disallineamenti.

Streaming (LazyCollection)

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

Aggregazioni

$count   = DB::table('users')->count();
$maxAge  = DB::table('users')->max('age');
$minAge  = DB::table('users')->min('age');
$avgAge  = DB::table('users')->avg('age');
$total   = DB::table('orders')->sum('amount');

$avgPremium = DB::table('orders')
    ->where('plan', 'premium')
    ->avg('amount');

Verifica esistenza

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

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

SELECT

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

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

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

WHERE

Condizioni base

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

$users = DB::table('users')
    ->where('status', 'active')
    ->where('age', '>', 20)
    ->get();

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

Raggruppamento

use Illuminate\Database\Query\Builder;

$users = DB::table('users')
    ->where('active', true)
    ->where(function (Builder $query) {
        $query->where('role', 'admin')
              ->orWhere('role', 'moderator');
    })
    ->get();
// WHERE active = 1 AND (role = 'admin' OR role = 'moderator')

whereIn / whereBetween / whereNull

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

$users = DB::table('users')->whereBetween('age', [20, 40])->get();

$users = DB::table('users')->whereNull('deleted_at')->get();
$users = DB::table('users')->whereNotNull('email_verified_at')->get();

whereLike

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

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

whereAny / whereAll

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

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

whereNullSafeEquals

whereNullSafeEquals (e orWhereNullSafeEquals) confronta considerando due NULL come uguali. L’operatore = normale rende NULL = NULL come false; con whereNullSafeEquals invece i NULL sono uguali. Corrisponde a <=> di MySQL e IS NOT DISTINCT FROM di PostgreSQL.

$lastLoginIp = $request->input('last_login_ip');

$users = DB::table('users')
    ->whereNullSafeEquals('last_login_ip', $lastLoginIp)
    ->get();

Il classico where('column', null) diventa WHERE column IS NULL; whereNullSafeEquals('column', $value) funziona in modo coerente anche quando il valore associato è o non è null. Molto utile con input utente potenzialmente nullo.

JOIN

$users = DB::table('users')
    ->join('orders', 'users.id', '=', 'orders.user_id')
    ->select('users.name', 'orders.amount')
    ->get();

$users = DB::table('users')
    ->leftJoin('orders', 'users.id', '=', 'orders.user_id')
    ->get();

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

JOIN con sottoquery

$latestOrders = DB::table('orders')
    ->select('user_id', DB::raw('MAX(created_at) as last_order_at'))
    ->groupBy('user_id');

$users = DB::table('users')
    ->joinSub($latestOrders, 'latest_orders', function ($join) {
        $join->on('users.id', '=', 'latest_orders.user_id');
    })
    ->get();

Ordinamento, raggruppamento, limit

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

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

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

$orders = DB::table('orders')
    ->select('status', DB::raw('COUNT(*) as count'))
    ->groupBy('status')
    ->get();

$orders = DB::table('orders')
    ->select('user_id', DB::raw('SUM(amount) as total'))
    ->groupBy('user_id')
    ->having('total', '>', 10000)
    ->get();

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

Sottoquery

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

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

$users = DB::table('users')
    ->select('name')
    ->selectSub(function ($query) {
        $query->from('orders')
              ->selectRaw('COUNT(*)')
              ->whereColumn('orders.user_id', 'users.id');
    }, 'order_count')
    ->get();

Ricerca per similarità vettoriale

Per le colonne che memorizzano vettori di embedding, whereVectorSimilarTo consente di filtrare i record vicini a un vettore specifico. Le colonne vettoriali si definiscono nelle migrazioni con il tipo vector (vedi colonne vettoriali nelle migrazioni).

use Illuminate\Support\Facades\DB;

$embedding = [0.12, -0.34, 0.56];

$documents = DB::table('documents')
    ->whereVectorSimilarTo('embedding', $embedding, minSimilarity: 0.4)
    ->limit(10)
    ->get();

whereVectorSimilarTo ordina i risultati dal più simile al meno simile. Se non serve indicare una similarità minima ma si vuole solo esplicitare l’ordinamento per distanza, si può usare orderByVectorDistance. Per includere la distanza tra le colonne restituite, selectVectorDistance permette di assegnarle un alias.

$documents = DB::table('documents')
    ->select('id', 'title')
    ->selectVectorDistance('embedding', $embedding, as: 'distance')
    ->orderByVectorDistance('embedding', $embedding)
    ->limit(10)
    ->get();

Questi metodi vengono tradotti negli operatori vettoriali forniti dal database. Verifica che il database in uso supporti il tipo vector e le relative operazioni e, con PostgreSQL, assicurati di aver abilitato l’estensione pgvector.

Espressioni Raw

Le espressioni Raw vengono inserite come SQL. Passare input utente direttamente causa SQL injection. Usa sempre binding.

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

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

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

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

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

INSERT / UPDATE / DELETE

INSERT

DB::table('users')->insert([
    'email' => '[email protected]',
    'name'  => 'Mario Rossi',
]);

DB::table('users')->insert([
    ['email' => '[email protected]', 'name' => 'Mario Rossi'],
    ['email' => '[email protected]', 'name' => 'Giulia Bianchi'],
]);

$id = DB::table('users')->insertGetId([
    'email' => '[email protected]',
    'name'  => 'Luca Verdi',
]);

UPSERT

DB::table('users')->upsert(
    [
        ['email' => '[email protected]', 'name' => 'Mario Rossi', 'votes' => 5],
        ['email' => '[email protected]', 'name' => 'Giulia Bianchi', 'votes' => 10],
    ],
    uniqueBy: ['email'],
    update: ['name', 'votes']
);

UPDATE

$affected = DB::table('users')
    ->where('id', 1)
    ->update(['name' => 'Mario R.', 'updated_at' => now()]);

DB::table('users')->where('id', 1)->increment('votes');
DB::table('users')->where('id', 1)->increment('votes', 5);
DB::table('users')->where('id', 1)->decrement('votes');
DB::table('users')->where('id', 1)->decrement('balance', 100);

DELETE

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

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

Query condizionali (when)

$status = request('status');
$sortBy = request('sort', 'name');

$users = DB::table('users')
    ->when($status, function ($query, $status) {
        $query->where('status', $status);
    })
    ->when($sortBy === 'email', function ($query) {
        $query->orderBy('email');
    }, function ($query) {
        $query->orderBy('name');
    })
    ->get();

Debug

$sql = DB::table('users')->where('active', true)->toSql();
$bindings = DB::table('users')->where('active', true)->getBindings();

DB::table('users')->where('active', true)->dump();
DB::table('users')->where('active', true)->dd();

dd() è comodo in debug ma non usarlo in produzione. toSql() e getBindings() sono più sicuri.

Riepilogo

Metodi più usati

Metodo	Descrizione
`get()`	Tutti i record (Collection)
`first()`	Primo record
`find($id)`	Per ID
`value($column)`	Valore di una colonna
`pluck($column)`	Elenco valori di una colonna
`count()`	Conteggio
`sum($col)`	Somma
`avg($col)`	Media
`max($col)` / `min($col)`	Massimo/minimo
`exists()`	Verifica esistenza
`insert([...])`	Insert
`update([...])`	Update
`delete()`	Delete
`chunk($n, fn)`	A blocchi
`when($cond, fn)`	Condizionale
`toSql()`	Verifica SQL

Query Builder vs Eloquent

Il Query Builder è più a basso livello e restituisce stdClass. Se non ti servono relazioni ed eventi dei modelli è più semplice e veloce.

// Eloquent
$users = User::where('active', true)->get();
echo $users[0]->name; // Istanza User

// Query Builder
$users = DB::table('users')->where('active', true)->get();
echo $users[0]->name; // stdClass

​Cos’è il Query Builder

​Quando usare il Query Builder o Eloquent

​Recupero dei dati

​Tutti i record

​Un solo record

​Elenchi di colonne

​Elaborazione a blocchi

​Streaming (LazyCollection)

​Aggregazioni

​Verifica esistenza

​SELECT

​WHERE

​Condizioni base

​Raggruppamento

​whereIn / whereBetween / whereNull

​whereLike

​whereAny / whereAll

​whereNullSafeEquals

​JOIN

​JOIN con sottoquery

​Ordinamento, raggruppamento, limit

​Sottoquery

​Ricerca per similarità vettoriale

​Espressioni Raw

​INSERT / UPDATE / DELETE

​INSERT

​UPSERT

​UPDATE

​DELETE

​Query condizionali (when)

​Debug

​Riepilogo

Cos’è il Query Builder

Quando usare il Query Builder o Eloquent

Recupero dei dati

Tutti i record

Un solo record

Elenchi di colonne

Elaborazione a blocchi

Streaming (LazyCollection)

Aggregazioni

Verifica esistenza

SELECT

WHERE

Condizioni base

Raggruppamento

whereIn / whereBetween / whereNull

whereLike

whereAny / whereAll

whereNullSafeEquals

JOIN

JOIN con sottoquery

Ordinamento, raggruppamento, limit

Sottoquery

Ricerca per similarità vettoriale

Espressioni Raw

INSERT / UPDATE / DELETE

INSERT

UPSERT

UPDATE

DELETE

Query condizionali (when)

Debug

Riepilogo