Travail avec la base de données dans Drupal 7 - leçon 4 - Requêtes dynamiques (SELECT)

Nous sommes probablement arrivés à la partie la plus intéressante de l'API Database de Drupal : les requêtes dynamiques. Ces requêtes sont dites dynamiques car Drupal génère dynamiquement la chaîne de la requête. Toutes les requêtes d'insertion, mise à jour, suppression ou fusion peuvent être dynamiques. Les requêtes de sélection peuvent être dynamiques ou statiques. Toutefois, il est recommandé d'utiliser des requêtes dynamiques même pour la sélection.

Toutes les requêtes dynamiques sont créées comme des objets requête, exécutés quand nécessaire. Comme pour les requêtes statiques, la majorité des requêtes utilise un wrapper procédural. Tous les paramètres de la requête peuvent être ajoutés à l'objet requête.

Requêtes de sélection avec db_select()

Les requêtes de sélection dynamiques commencent par la fonction db_select(), par exemple :

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

Dans cet exemple, "node" est la table ciblée, ce qui correspond au FROM en SQL. Notez que le nom de table n’est pas entouré d’accolades, le constructeur gère cela automatiquement. Le deuxième paramètre est l’alias de la table. Si aucun alias n’est donné, le nom de la table est utilisé. Le tableau $options est optionnel, identique à celui des requêtes statiques.

Les requêtes dynamiques peuvent être simples ou complexes. Nous allons voir les principes de base, mais on pourrait y passer beaucoup de temps.

Exemple supplémentaire de db_select()

Voici un exemple simple de sélection dans la table des utilisateurs. Plus tard, nous verrons les jointures et requêtes plus complexes.

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

Ce code est équivalent à :

$result = db_query("SELECT uid, name, status, created, access FROM {users} u WHERE uid > 0 LIMIT 50 OFFSET 0");

C’est une forme simplifiée utilisée dans la page d’administration des utilisateurs, bonne base pour étudier.

Jointures (joins)

Pour joindre une autre table, utilisez les méthodes join(), innerJoin(), leftJoin() ou rightJoin(), par exemple :

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

Ici, un INNER JOIN est effectué avec la table "user" aliasée "u", avec la condition ON "n.uid = u.uid AND u.uid = :uid", où :uid vaut 5. Notez l’usage des placeholders pour la sécurité. Ne jamais injecter directement des variables dans la requête pour éviter les injections SQL.

Les méthodes innerJoin(), leftJoin(), rightJoin() correspondent aux types SQL respectifs.

La méthode join() retourne l’alias de la table jointe. Si cet alias existe déjà (rare), Drupal en génèrera un nouveau.

Exemple d’utilisation d’un objet SelectQuery comme table source :

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

Champs (Fields, addField)

Pour ajouter un champ à la requête, utilisez addField() :

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

Ce code ajoute le champ "title" de la table aliasée "n", avec l’alias "my_title". Si aucun alias n’est donné, un alias est généré automatiquement. En général, c’est le nom du champ. Si ce nom existe déjà, l’alias inclut le nom de la table, puis un compteur si besoin.

Si vous écrivez un hook_query_alter() sans connaître l’alias exact, utilisez les alias standards.

Pour plusieurs champs, appelez plusieurs fois addField(), dans n’importe quel ordre. L’ordre importe rarement sauf logique métier spécifique.

Pour ajouter plusieurs champs à la fois, utilisez fields() :

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

Ceci équivaut à plusieurs appels à addField(). Attention, fields() ne permet pas de définir des alias personnalisés. Il génère automatiquement les alias.

Appeler fields() sans champ équivaut à "SELECT *" :

<?php
$query->fields('n');
?>

Ce sera "n.*". Attention aux collisions si plusieurs tables ont des champs identiques, préférez spécifier les champs.

DISTINCT

Pour éviter les doublons dans le résultat, utilisez distinct() :

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

Mais DISTINCT peut alourdir la requête, à utiliser avec précaution.

Expressions (addExpression)

Les requêtes supportent les expressions SQL, par exemple compter, soustraire, manipuler les champs :

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

Le deuxième paramètre est l’alias. S’il existe déjà, un nouvel alias sera généré. Le troisième paramètre est un tableau des valeurs à substituer dans l’expression.

Attention, certaines expressions peuvent ne pas fonctionner avec GROUP BY.

ORDER BY (orderBy)

Pour trier les résultats, utilisez orderBy() :

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

Trie par "title" décroissant. Le deuxième paramètre peut être "ASC" ou "DESC". Par défaut, c’est "ASC". Le nom du champ doit être celui utilisé comme alias dans addField() ou addExpression(). Pour trier sur plusieurs champs, appelez plusieurs fois orderBy().

Ordre aléatoire (orderRandom)

Pour un ordre aléatoire (dont la syntaxe varie selon DB), utilisez orderRandom() :

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

Vous pouvez combiner avec orderBy() :

<?php
$query->orderBy('term')->orderRandom()->execute();
?>

GROUP BY (groupBy)

Pour grouper les résultats, utilisez groupBy() :

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

Le nom du champ doit être un alias existant. Vous pouvez appeler plusieurs fois groupBy() pour plusieurs champs.

Limites et plages (range)

Pour limiter le nombre d’enregistrements, utilisez range() :

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

Affiche 10 enregistrements à partir du 6e (l’index commence à 0). Appeler range() plusieurs fois écrase les valeurs précédentes.

Tri via TableSort (extend)

Pour rendre le tri dynamique avec un en-tête de tableau, utilisez l’extension TableSort :

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

Conditions (condition)

Les conditions sont un sujet complexe, abordé plus tard. Pour rappel, WHERE s’utilise via condition() ou where(), HAVING via havingCondition() ou having().

Exécution (execute)

Pour exécuter la requête :

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

execute() retourne un ensemble de résultats similaire à db_query(), que vous pouvez parcourir :

<?php
$result = $query->execute();
foreach ($result as $record) {
  // Traitement de chaque $record
}
?>

• fetchField()
• fetchAllKeyed()
• fetchCol()

Ces méthodes nécessitent des alias corrects mais ne sont pas toujours garantis par le constructeur.

Compte de lignes (countQuery)

Pour compter les lignes, utilisez :

<?php
$count_query = $query->countQuery();
?>

$count_query est un nouveau SelectQuery sans tri, qui retourne le nombre total d’enregistrements. Vous pouvez chaîner :

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

Debug

Pour afficher la requête SQL générée, utilisez :

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