Make a SQL query on multiple tables with Drupal 8

Drupal 8 provides an API, with EntityQuery, which significantly simplifies writing SQL queries to retrieve and list a set of contents. Thus it is very easy to retrieve a list of contents according to complex criteria and conditions, without needing to know precisely the tables and their syntax for each field associated with an entity.

For example, in a few lines we can recover all content that meets several conditions (here for example the contents referencing a set of instruments and corresponding to certain skills).

$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 reaches certain limits if for example you need to retrieve information stored on another entity, and therefore another table. For example, if for obvious performance reasons you have created a custom entity to store all notes received by a content, and you now want to retrieve the list of contents sorted by rating, EntityQuery will not allow us to get to our purposes.

In these cases, we use the good old db_select() or its equivalent on Drupal 8 \Drupal::database()->select(). We will then be able to build our SQL query, perform all necessary joins, and at the same time use expressions to delegate to the database calculations that might be more complex elsewhere.

So to pick up the example above, our request would become

$query = $this->database->select('node_field_data', 'n');
$query->leftjoin('node__' . Const::NODE_INSTRUMENT, 'i', 'i.entity_id = n.nid');  // Joining with the NODE_INSTRUMENT field table
$query->leftjoin('node__' . Const::NODE_LEVEL_SKILLS, 'l', 'l.entity_id = n.nid'); // Joining with the NODE_LEVEL_SKILLS field table
$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'); // For the moment we only sort by date of publication
$query->range(0, Const::HOME_MAX_ITEM_PER_LIST);
$result = $query->execute()->fetchAllKeyed(0, 0);

Query less intuitive to write than the version with EntityQuery. But all the interest now lies in the ability to add a new join on a personal entity that holds all the ratings assigned to each content.

Addition that takes place in a few lines. We add a join to the table of our entity (e_score) storing the notes, then adding an SQL expression we can automatically calculate the average of the notes and use this result to sort the results of our query.

$query = $this->database->select('node_field_data', 'n');
$query->leftjoin('e_score', 's', 's.node_id = n.nid');  // We add a join on the e_score table of our entity.
$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'); // We calculate the average of each note on the table of the entity.
$query->groupBy('n.nid'); // And this average is calculated by grouping all the results by noted content.
$query->orderBy('score', 'DESC');  // We can now sort the results by the average of their scores.
$query->range(0, Const::HOME_MAX_ITEM_PER_LIST);
$result = $query->execute()->fetchAllKeyed(0, 0);

Thus we can now sort the results of the query according to the average of the scores obtained by each content, using the MySQL avg() function. We can use in the addition of the expression, here, any type of calculation (count(), sum(), and many other calculation or selection, etc) that the database is able to realize natively. Be careful, however, because then our query becomes linked to the database depending on the functions it offers and is no longer agnostic. But this is a minimal risk or at least that can be easily mastered by any Drupal Developer 8 or whatever.

Note that we can use the OR / AND condition groups on this type of query, and can chain them together, as on EntityQuery-based queries. For example, we add a group of OR conditions that contain a number of AND condition groups. Specifically all content that has the same level of competence as the level of the user on this skill. 

$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'); // We add a new join.
$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(); // We create a group of OR conditions.
$user_global_skills = $this->getUserGlobalSkills($user, FALSE); // We recover some properties of the user.
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); // We add this group of AND conditions to the main group of OR conditions.
  }
}
$query->condition($condition_or); // And we finally add the OR condition group to the query.
$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);

In the end, even if they can be a little less readable, the requests based on a select() are equally powerful. Especially since the groups of conditions can also be used on it, to be able easily and legibly to chain complex conditions. And be able to return to it months later without too much...bitterness.