Faire une requête SQL sur plusieurs tables avec Drupal 8

Drupal 8 fournit une API, avec EntityQuery, qui simplifie de manière significative l'écriture de requêtes SQL pour récupérer et lister un ensemble de contenus. Ainsi il est très facile de récupérer une liste de contenus selon des critères et des conditions complexes, sans avoir besoin de connaître précisément les tables et leur syntaxe pour chaque champ associé à une entité.

Par exemple, en quelques lignes nous pouvons récupérer l'ensemble des contenus répondant à plusieurs conditions (ici par exemple les contenus référençant un ensemble d'instruments et correspondant à certaines compétences).

$query = $this->nodeStorage->getQuery()
  ->condition('status', 1)
  ->condition('type','CONTENT_TYPE_NAME')
  ->range(0, 20)
  ->sort('created', 'DESC');
if ($instrument_ids) {
  $query->condition(Const::NODE_INSTRUMENT, $instrument_ids, 'IN');
}
if ($skill_ids) {
  $query->condition(Const::NODE_LEVEL_SKILLS, $skill_ids, 'IN');
}
$result = $query->execute();

EntityQuery arrive à certaines limites si par exemple vous avez besoin de récupérer des informations stockées sur une autre entité, et donc une autre table. Par exemple, si pour des raisons de performances évidentes vous avez créé une entité personnalisée pour stocker toutes les notes reçues par un contenu, et que vous souhaitez désormais récupérer la liste des contenus triés par note, EntityQuery ne va pas nous permettre d'arriver à nos fins.

Dans ces cas de figure, il nous recourir au bon vieux db_select() ou son équivalent sur Drupal 8 \Drupal::database()->select(). Nous allons alors pouvoir construire notre requête SQL, effectuer toutes les jointures nécessaires, et dans le même temps utiliser des expressions pour déléguer à la base de données des calculs qui pourraient être plus complexes fait par ailleurs.

Ainsi pour reprendre l'exemple ci-dessus, notre requête deviendrait

$query = $this->database->select('node_field_data', 'n');
$query->leftjoin('node__' . Const::NODE_INSTRUMENT, 'i', 'i.entity_id = n.nid');  // Jointure avec la table du champ NODE_INSTRUMENT
$query->leftjoin('node__' . Const::NODE_LEVEL_SKILLS, 'l', 'l.entity_id = n.nid'); // Jointure avec la table du champ NODE_LEVEL_SKILLS
$query->fields('n', ['nid']);
$query->condition('n.status', 1);
$query->condition('n.type', 'CONTENT_TYPE_NAME');
if ($instrument_ids) {
 $query->condition('i.' . Const::NODE_INSTRUMENT . '_target_id', $instrument_ids, 'IN');
}
if ($skill_ids) {
  $query->condition('l.' . Const::NODE_LEVEL_SKILLS . '_target_id', $skill_ids, 'IN');
}
$query->orderBy('created', 'DESC'); // Pour l'instant nous ne trions que par date de publication
$query->range(0, Const::HOME_MAX_ITEM_PER_LIST);
$result = $query->execute()->fetchAllKeyed(0, 0);

Requête moins intuitive à écrire que la version avec EntityQuery. Mais tout l'intérêt maintenant réside en la possibilité d'ajouter une nouvelle jointure sur une entité personnelle qui détient toutes les notes attribuées à chaque contenu.

Ajout qui s'effectue en quelques lignes. Nous ajoutons une jointure sur la table de notre entité (e_score) stockant les notes, puis en ajoutant une expression SQL nous pouvons calculer automatiquement la moyenne des notes et utiliser ce résultat pour trier les résultas de notre requête.

$query = $this->database->select('node_field_data', 'n');
$query->leftjoin('e_score', 's', 's.node_id = n.nid');  // Nous ajoutons une jointure sur la table e_score de notre entité.
$query->leftjoin('node__' . Const::NODE_INSTRUMENT, 'i', 'i.entity_id = n.nid');
$query->leftjoin('node__' . Const::NODE_LEVEL_SKILLS, 'l', 'l.entity_id = n.nid');
$query->fields('n', ['nid']);
$query->condition('n.status', 1);
$query->condition('n.type', 'CONTENT_TYPE_NAME');
if ($instrument_ids) {
  $query->condition('i.' . Const::NODE_INSTRUMENT . '_target_id', $instrument_ids, 'IN');
}
if ($skill_ids) {
  $query->condition('l.' . Const::NODE_LEVEL_SKILLS . '_target_id', $skill_ids, 'IN');
}
$query->addExpression('AVG(s.score)', 'score'); // Nous calculons la moyenne de chaque note sur la table de l'entité.
$query->groupBy('n.nid'); // Et cette moyenne est calculée en regroupant tous les résultats par contenu noté.
$query->orderBy('score', 'DESC');  // Nous pouvons désormais trier les résultats en fonction de la moyenne de leurs notes.
$query->range(0, Const::HOME_MAX_ITEM_PER_LIST);
$result = $query->execute()->fetchAllKeyed(0, 0);

Ainsi nous pouvons désormais trier les résultats de la requête en fonction de la moyenne des notes obtenus par chaque contenu, et ce en utilisant la fonction MySQL AVG(). Nous pouvons utiliser dans l'ajout de l'expression, ici, tout type de calcul (count(), sum(), et bien d'autres calcul ou sélection, etc) que la base de données est capable de réaliser nativement. Attention toutefois, car alors notre requête devient ici liée à la base de données en fonction des fonctions qu'elle propose et donc n'est plus agnostique. Mais il s'agit ici d'un risque minime ou à tout le moins qui peut être maîtrisé facilement par tout Développeur Drupal 8 ou autre.

A noter que nous pouvons utiliser sur ce type de requête les groupes de conditions OR / AND, et pouvons les enchaîner, comme sur les requêtes basées sur EntityQuery. Par exemple nous rajoutons ici un groupe de conditions OR qui contiennent un certain nombre de groupes de conditions AND. Concrètement tous les contenus qui disposent du même niveau d'une compétence que le niveau de l'utilisateur sur cette compétence. 

$query = $this->database->select('node_field_data', 'n');
$query->leftjoin('wp_score', 's', 's.node_id = n.nid');
$query->join('node__' . Const::NODE_INSTRUMENT, 'i', 'i.entity_id = n.nid');
$query->join('node__' . Const::NODE_LEVEL_SKILLS, 'l', 'l.entity_id = n.nid');
$query->join('node__' . Const::NODE_SKILLS_MAIN, 'm', 'l.entity_id = n.nid'); // Nous ajoutons une nouvelle jointure.
$query->fields('n', ['nid']);
$query->condition('n.status', 1);
$query->condition('n.type', 'element');
$query->condition('i.' . Const::NODE_INSTRUMENT . '_target_id', [$instrument_id], 'IN');

$condition_or = $query->orConditionGroup(); // Nous créons un groupe de conditions OR.
$user_global_skills = $this->getUserGlobalSkills($user, FALSE); // Nous récupérons certaines propriétés de l'utilisateur.
foreach ($user_global_skills as $type_skill => $score_skill) {
  $skill_level_id = $this->getUserLevelSkillId((float) $score_skill);
  if ($skill_level_id) {
    $skill_type_id = isset(Const::GLOBAL_SKILLS[$type_skill]) ? Const::GLOBAL_SKILLS[$type_skill] : '';
    $condition_and = $query->andConditionGroup(); // Nous créons un groupe de conditions AND.
    $condition_and->condition('l.' . Const::NODE_LEVEL_SKILLS . '_target_id', [$skill_level_id], 'IN');
    $condition_and->condition('m.' . Const::NODE_SKILLS_MAIN . '_target_id', [$skill_type_id], 'IN');
    $condition_or->condition($condition_and); // Nous ajoutons ce groupe de conditions AND au groupe principal de conditions OR.
  }
}
$query->condition($condition_or); // Et nous ajoutons finalement le groupe de conditions OR à la requête.
$query->addExpression('AVG(s.score)', 'score');
$query->groupBy('n.nid');
$query->orderBy('score', 'DESC');
$query->range(0, Const::HOME_MAX_ITEM_PER_LIST);
$result = $query->execute()->fetchAllKeyed(0, 0);

Au final, même si elles peuvent être un peu moins lisibles, les requêtes basées sur un select() n'en sont pas moins tout autant puissantes. D'autant que les groupes de conditions peuvent être également utilisées dessus, pour pouvoir facilement et lisiblement enchainer des conditions complexes. Et pouvoir y revenir dessus des mois plus tard sans trop...d'amertume.