22 QueryBuilder di Laravel

Il QueryBuilder di laravel è un’interfaccia che ci permette di eseguire delle query SQL concatenando i metodi, in modo da utilizzare le query grezze in modo più fluido e facilitato con laravel.

In questa lezione andremo a sostituire le query create precedentemente e lanciate dai metodi della facade DB utilizzando lo strumento del QueryBuilder.

SELECT

Andiamo nel metodo index() e incominciamo analizzando il codice

public function index(Request $request)
{
$where = '';
$sql = 'select * from albums';
$sql = 'select * from albums WHERE 1=1';
$where = [];
if($request >has('id')) {
$sql .= ' where id =' . (int)$request >get('id');
$where['id'] = $request >get('id');
$sql .= " AND id=:id";
}
if($request >has('album_name')) {
$where = " AND album_name=".$request >get('album_name')." ";
$where['album_name'] = $request >get('album_name');
$sql .= " AND album_name=:album_name";
}
$sql = $sql . $where;
//dd($sql);
return DB::select($sql);
return DB::select($sql, $where);
}

iniziamo con la prima parte dove si creava la query SQL, traduciamolo con le QueryBuilder, utilizzando sempre la facade DB e il metodo table(), alla quale passiamo la tabella da interrogare

DB::table('albums')

in questo modo abbiamo già accesso al QueryBuilder e alla tabella albums. Proseguiamo concatenando i metodi

$querBuilder= DB::table('albums')->get()

Con il metodo get() abbiamo già una collezioni di dati, restituiamo la vista e abbiamo già tutti i nostri albums con 2 rige di codice.

return view('albums.albums', ['albums' => $albums]);

ordinare i dati

adesso dobbiamo ordinare i dati secondo l’id discendente.

Abbiamo detto che il metodo get() restituisce una collation, quindi prima di esso dobbiamo chiamare gli altri metodi, nel nostro caso orderBy() che vuole il campo da ordinare e il tipo di ordinamento

$querBuilder = DB::table('albums')->orderBy('id','DESC')->get()

filtrare i dati in base all’id

adesso dobbiamo applicare il filtro per i parametri passati via GET usiamo come prima il metodo has() per la condizione, il metodo where() per specificare il campo sul quale effettuare il confronto e infine il metodo input() per reperire il valore di quel campo

if ($request->has('id')) {
 $queryBuilder->where('id', '=', $request->input('id'));
}

quando stiamo confrontando una colonna con un valore esatto, possiamo omettere l’ uguale per cui diventa

$queryBuilder->where('id', $request->input('id'));

infine restituiamo il get() per avere la collection

$albums = $queryBuilder->get();

filtrare i dati in base al nome

Seguendo lo stesso esempio per l’id, riporto il codice completo per il filtraggio in base all’ album_name

if ($request->has('album_name')) {
 $queryBuilder->where('album_name', 'like','%'.
 $request->input('album_name').'%');
}
$albums = $queryBuilder->get();

la differenza sta oltre che nel nome del campo anche nel confronto, non volendo filtrare in base ad una stringa esatta , utilizzerò l’operatore like, inoltre concateno una percentuale prima e dopo il confronto in modo da permettere la ricerca di una stringa che può stare dentro ad una frase.

DELETE

Per trasformare il metodo delete da query grezze a querybuilder, selezioniamo sempre la tabella con DB::table() e applichiamo il metodo where sul campo id che sarà confrontato con l’id passato come argomento. Infine chiamiamo il metodo delete()

public function delete($id) {
 $res = DB::table('albums')->where('id', $id)->delete();
 return $res
}

Come si evince grazie alle querybuilder di laravel si puà lavorare con il database in modo molto semplice e senza difficoltose query SQL.

Un piccolo appunto da fare sul delete è che una volta cancellato il record, la pagina non verrà aggiornata automaticamente fino a che non viene ricaricata. Per risolvere bisogna nel album.blade, selezionare l’elemento padre nel DOM della variabile li aggiungendo un ulteriore parentNode

var li = ele.target.parentNode.parentNode;

UPDATE

andiamo a lavorare sul metodo store(), iniziamo selezionando l’id della tabella

$res = DB::table('albums')->where('id', $id);

Applichiamo ora il metodo update() che riceverà un array associativo dei valori da aggiornare

$res = DB::table('albums')->where('id', $id)->update(
 ['album_name' => request()->input('name'),
  'description' => request()->input('description')
 ]
);

Da notare che nell’array le chiavi corrispondono ai campi, mentre i valori sono quelli inseriti nel form catturati con request()->input()

INSERT

Molto simile all’update, abbiamo in più il campo user_id e ovviamente invochiamo il metodo insert()

$res = DB::table('albums')->where('id', $id)->insert(
 ['album_name' => request()->input('name'),
  'description' => request()->input('description'),
  'user:id' => 1
 ]
);

qualora volessimo inserire più records contemporaneamente, basterà passare al metodo insert un array di array

[ 
 [ 
 ] ,
 [
 ]
]

JOIN

Ovviamente il queryBuilder può essere utilizzato per operazioni più complesse.

Per esempio facciamo una query che restituisca gli utenti che non hanno un album, iniziamo a farla con normale SQL

SELECT u.id, email, name FROM users as u
LEFT JOIN albums as a ON u.id = a.user_id
WHERE album_name is NULL

abbreviamo nel comando users con l’alias u e albums con a, con LEFT JOIN che restituisce tutti gli utenti, mentre la INNER JOIN direbbe restituisci tutti gli utenti che hanno un album, ma noi vogliamo il contrario.

Vediamo la facilità sintattica del querybuilder

$usersnoalbum = DB::table('users as u')
 ->leftJoin('albums as a', 'u.id','a.user_id')

Con il metodo leftjoin() si mette il nome della tabella con cui fare il confronto, albums, il secondo e terzo parametro sono la condizione e che quindi l’id dell’utente corrisponda allo uiser_id della tabella albums. In realtà il terzo parametro dovrebbe essere l’operatore di confronto ‘=’ e quindi il confronto va poi sul quarto parametro, ma essendo l’id univoco si può omettere, quindi sarebbe:

->leftJoin('albums as a', 'u.id', '=', 'a.user_id')

selezioniamo i campi che vogliamo restituire con select()

$usersnoalbum = DB::table('users as u')

->leftJoin('albums as a', 'u.id','a.user_id')

->select('u.id','email','name','album_name')->

ora filtriamo con la whereNull() per restituire gli utenti che non hanno un album

->whereNull('album_name')

abbiamo tantissimi tipi di where che ci offre il querybuilder, molto intuitivi e descrittivi per soddisfare ogni esigenza. Qual’ora volessimo utilizzare la sintassi SQL basta utilizzare la keyword Raw dopo il nome del metodo

whereRaw('album_name is null')

Quindi in tutte quelle situazioni dove si voglia effettuare operazioni particolari o vogliamo inserire del puro SQL, il querybuilder lo consente con una soluzione mista grazie a Raw, infatti potevamo fare la selezione anche senza la JOIN con un esempio di questo tipo

->select('u.id','email','name')->

whereRaw( ' EXISTS (SELECT user_id from albums where user_id= u.id)')

ovviamente viene meno la comodità offerta dal querybuilder, il quale può ricoprire praticamente tutte le necessità. Ovviamente chi ha una buona conoscenza del linguaggio SQL sarà facilitato nell’uso intuitivo del querybuilder la documentazione di laravel mette a disposizione la sezione dedicata da consultare.