Whilst converting a D7 site to D9 I came across some SQL in the node.tpl.php template. This clearly shouldn't be in the template and the poor developer had added a comment saying as much, but was clearly under pressure and maybe not as experienced as they needed to be.

So, with D9 using TWIG I needed to move the SQL to another place.... and the THEME_preprocess_page() function is probably the best place.

Just to get around the background functionality and to focus on the SQL, let's say my theme is called myD9theme, the following code will be in the myD9theme.theme file and the content type I'm focusing on is called 'video'.

function myD9theme_preprocess_page(&$variables) {

  if(isset($variables['node'])){
    $nodeType = $variables['node']->bundle();
    if($nodeType == 'video'){
      // This is where we will put our SQL
    }
  }

}

Our 'video' content type has a field called 'YT ID' with a machine name of 'field_yt_id'. We get this value using:

$yt_id = $variables['node']->field_yt_id->getString();

The value of $yt_id is something like: x6zzyz

The SQL I need to run is:

SELECT field_video_id_value 
FROM `node_revision__field_playlist_id` 
LEFT OUTER JOIN `node_revision__field_video_id` ON node_revision__field_playlist_id.entity_id = node_revision__field_video_id.entity_id
WHERE `field_playlist_id_value` = 'x6zzyz'
LIMIT 1

The above SQL starts with the table 'node_revision__field_playlist_id' and then joins the 'node_revision__field_video_id' table, has a WHERE clause, a LIMIT set to 1 record and the result is a single field 'field_video_id_value'.

The Drupal code version of the above, with a row count check and a variable set (that can be used in the TWIG template) is:

$database = \Drupal::database();
$query = $database->select('node_revision__field_playlist_id', 'nrfpi');
$query->leftJoin('node_revision__field_video_id', 'nrfvi', 'nrfpi.entity_id = nrfvi.entity_id');
$query->condition('nrfpi.field_playlist_id_value', $yt_id, '=');
$query->fields('nrfvi', ['field_video_id_value']);
$query->range(0, 1);

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

$result = $query->execute();

if($num_rows == 1){
  foreach ($result as $record) {
    $variables['firstVideo'] = $record->field_video_id_value;
  }    
}
else {
  $variables['firstVideo'] = false; 
}