Lavorare con il database in Drupal 7 – Lezione 4 – Query dinamiche (SELECT)

Probabilmente siamo arrivati alla parte più interessante del Database API di Drupal: le query dinamiche. Si chiamano così perché Drupal costruisce dinamicamente la stringa della query. Tutte le query di inserimento, aggiornamento, eliminazione o unione possono essere dinamiche. Le query di selezione possono essere sia dinamiche che statiche, ma è consigliato usare sempre le query dinamiche anche per le SELECT.

Tutte le query dinamiche vengono create come oggetti query, ai quali possiamo aggiungere condizioni e parametri quando necessario. Come nelle query statiche, la maggior parte dei casi utilizza un procedural wrapper. Tutti i parametri successivi possono essere aggiunti direttamente all’oggetto query.

Query di selezione db_select()

Le query di selezione dinamiche iniziano con la funzione db_select(), ad esempio:

<?php
$query = db_select('node', 'n', $options);
?>

In questo esempio 'node' è la tabella a cui ci riferiamo (l’equivalente della clausola FROM in SQL). Non è necessario racchiudere il nome della tabella tra parentesi graffe, poiché il costruttore di query lo farà automaticamente. Il secondo parametro è l’alias della tabella. Se non viene fornito, Drupal userà il nome della tabella stessa. Il parametro $options è facoltativo e ha la stessa struttura di quello usato nelle query statiche.

Le query di selezione dinamiche possono essere molto semplici o estremamente complesse. Vedremo i principi fondamentali per comprenderne il funzionamento, ma le loro possibilità sono molto ampie.

Esempio di db_select()

Di seguito un esempio semplice di query di selezione sulla tabella degli utenti. Più avanti vedremo come utilizzare i join per costruire query più complesse.

<?php
$query = db_select('users', 'u');

$query
  ->condition('u.uid', 0, '<>')
  ->fields('u', array('uid', 'name', 'status', 'created', 'access'))
  ->range(0, 50);

$result = $query->execute();
?>

Questo esempio equivale alla seguente query SQL:

SELECT uid, name, status, created, access FROM {users} u WHERE uid <> 0 LIMIT 50 OFFSET 0;

Join (INNER, LEFT, RIGHT)

Per unire altre tabelle alla query si utilizzano i metodi join(), innerJoin(), leftJoin() o rightJoin(), come nell’esempio seguente:

<?php
$table_alias = $query->join('user', 'u', 'n.uid = u.uid AND u.uid = :uid', array(':uid' => 5));
?>

Questo aggiunge un INNER JOIN (tipo predefinito) con la tabella “user”, alias “u”. La condizione ON è “n.uid = u.uid AND u.uid = :uid”, dove il segnaposto :uid viene sostituito con 5. I segnaposti proteggono la query da SQL injection, quindi non bisogna mai concatenare valori direttamente nella stringa SQL.

Il valore restituito dal metodo join() è l’alias effettivo della tabella, che Drupal rinominerà automaticamente se necessario per evitare conflitti.

È anche possibile eseguire join con sottoquery:

<?php
$myselect = db_select('mytable')
  ->fields('mytable')
  ->condition('myfield', 'myvalue');
$alias = $query->join($myselect, 'myalias', 'n.nid = myalias.nid');
?>

Campi (fields, addField)

Per aggiungere un campo alla query si usa addField():

<?php
$title_field = $query->addField('n', 'title', 'my_title');
?>

Questo aggiunge il campo “title” dalla tabella con alias “n” e lo rinomina “my_title”. Se l’alias non è definito, Drupal ne genera uno automaticamente (es. “title” o “n_title”).

Per selezionare più campi si possono chiamare più volte addField() o usare fields():

<?php
$query->fields('n', array('nid', 'title', 'created', 'uid'));
?>

Questo equivale a chiamare addField() quattro volte. Se si chiama fields() senza elencare campi, il risultato sarà equivalente a SELECT n.*.

DISTINCT

Per rimuovere duplicati dai risultati, si usa distinct():

<?php
$query->distinct();
?>

Attenzione: DISTINCT può ridurre le prestazioni, quindi usarlo solo se necessario.

Espressioni (addExpression)

Il costruttore di query consente anche di aggiungere espressioni SQL, ad esempio funzioni di aggregazione o calcoli:

<?php
$count_alias = $query->addExpression('COUNT(uid)', 'uid_count');
$count_alias = $query->addExpression('created - :offset', 'uid_count', array(':offset' => 3600));
?>

ORDER BY

Per ordinare i risultati, usa orderBy():

<?php
$query->orderBy('title', 'DESC');
?>

Il secondo parametro può essere “ASC” o “DESC” (ASC è il valore predefinito). È possibile aggiungere più ordinamenti chiamando più volte orderBy().

Ordine casuale (orderRandom)

Per ottenere risultati in ordine casuale, usa:

<?php
$query->orderRandom();
?>

Può essere combinato con orderBy().

GROUP BY

Per raggruppare i risultati per campo:

<?php
$query->groupBy('uid');
?>

Limitazioni (range)

Per limitare il numero di record restituiti:

<?php
$query->range(5, 10);
?>

Restituisce 10 record a partire dal sesto (l’indice parte da 0).

Estensioni per ordinamento tabellare (extend)

Drupal fornisce l’estensione TableSort per aggiungere funzionalità di ordinamento alle tabelle:

<?php
$query = $query
  ->extend('TableSort')
  ->orderByHeader($header);
?>

Condizioni (condition)

Le condizioni si aggiungono con condition() o where() per la clausola WHERE, e havingCondition() o having() per HAVING. Gli operatori funzionano in modo simile tra loro.

Esecuzione (execute)

Per eseguire la query si chiama execute():

<?php
$result = $query->execute();
?>

Il risultato può essere gestito con un ciclo:

<?php
foreach ($result as $record) {
  // Elaborazione di ogni record
}
?>

Conteggio risultati

Per ottenere il numero totale di record, si può usare countQuery():

<?php
$num_rows = $query->countQuery()->execute()->fetchField();
?>

Debug

Per visualizzare la query SQL generata, usa:

<?php
print_r($query->__toString());
?>