Werken met de database in Drupal 7 – les 4 – Dynamische query’s (SELECT)

We zijn nu aangekomen bij het meest interessante deel van de Database API van Drupal: dynamische query’s. Ze worden “dynamisch” genoemd omdat Drupal de querystring automatisch uitbreidt tijdens de uitvoering. Alle query’s voor invoegen (INSERT), bijwerken (UPDATE), verwijderen (DELETE) of samenvoegen (MERGE) kunnen dynamisch zijn. Ook SELECT-query’s kunnen dynamisch of statisch zijn, maar het is aan te raden om ook voor SELECT-query’s dynamische query’s te gebruiken.

Alle dynamische query’s worden aangemaakt als een queryobject, dat je verder kunt uitbreiden wanneer nodig. Net als bij statische query’s wordt meestal gebruikgemaakt van een procedural wrapper. Alle parameters kunnen vervolgens via methoden aan het queryobject worden toegevoegd.

db_select() – SELECT-query’s

Dynamische SELECT-query’s beginnen met de functie db_select(), bijvoorbeeld:

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

In dit voorbeeld is "node" de tabel waarop de query wordt uitgevoerd (hetzelfde als het gedeelte na FROM in SQL). Merk op dat je de tabelnaam hier niet tussen accolades hoeft te zetten – de querybuilder doet dat automatisch. De tweede parameter is het alias (bijvoorbeeld n), en als die niet wordt opgegeven, gebruikt Drupal standaard de tabelnaam. De parameter $options is optioneel en werkt hetzelfde als bij statische query’s.

Dynamische SELECT-query’s kunnen eenvoudig of zeer complex zijn. We bekijken hier de basisprincipes, maar je kunt ze zeer uitgebreid toepassen.

Een eenvoudig voorbeeld van db_select()

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

Dit voorbeeld is gelijk aan de volgende SQL-query:

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

Joins (INNER, LEFT, RIGHT)

Om een query met een andere tabel te combineren gebruik je join(), innerJoin(), leftJoin() of rightJoin(). Bijvoorbeeld:

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

Hier voegen we een INNER JOIN toe met de tabel user (alias u), met de ON-voorwaarde n.uid = u.uid AND u.uid = :uid. De placeholder :uid heeft de waarde 5. Gebruik altijd placeholders in plaats van variabelen direct in de query, om SQL-injecties te voorkomen.

Velden toevoegen (addField, fields)

Gebruik addField() om een veld aan de query toe te voegen:

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

Dit voegt het veld title toe van de tabel n met als alias my_title. Als je geen alias opgeeft, genereert Drupal er automatisch één (bijvoorbeeld title of n_title).

Wil je meerdere velden tegelijk selecteren, gebruik dan de kortere fields()-methode:

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

Dit is gelijk aan vier opeenvolgende aanroepen van addField(). Roep je fields() zonder parameters aan, dan is dat hetzelfde als SELECT *.

DISTINCT

Om dubbele rijen uit het resultaat te verwijderen, gebruik je de methode distinct():

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

Let op: DISTINCT kan prestatieproblemen veroorzaken, dus gebruik het alleen als het echt nodig is.

Expressies toevoegen (addExpression)

Je kunt ook SQL-expressies aan een query toevoegen, bijvoorbeeld:

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

De eerste regel voegt COUNT(uid) AS uid_count toe. De derde parameter bevat optionele placeholders voor waarden in de expressie.

ORDER BY en willekeurige volgorde

Om resultaten te sorteren gebruik je orderBy():

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

Voor willekeurige volgorde gebruik je orderRandom():

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

Je kunt beide methoden combineren als dat nodig is.

GROUP BY

Om resultaten te groeperen gebruik je groupBy():

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

LIMIT en OFFSET (range)

Gebruik range() om het aantal resultaten te beperken:

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

Dit levert 10 records op, beginnend bij de zesde (index 5).

Extenders gebruiken (TableSort)

Je kunt query-extenders gebruiken, zoals TableSort, om sorteerbare tabellen te genereren:

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

WHERE en HAVING voorwaarden

Voorwaarden worden ingesteld met condition() (WHERE) of havingCondition() (HAVING). Beide methoden werken op vergelijkbare wijze. Ze worden later in detail uitgelegd.

Query uitvoeren (execute)

De query wordt uitgevoerd met execute():

<?php
$result = $query->execute();
foreach ($result as $record) {
  // Verwerk elk $record.
}
?>

Aantal resultaten tellen

Om het aantal resultaten van een query te tellen, gebruik je countQuery():

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

Debuggen

Om de gegenereerde SQL-query te bekijken, gebruik je:

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