A function to print out a full SelectInterface query, substituting placeholders and values

printQuery.php

<?php

function printQuery(\Drupal\Core\Database\Query\SelectInterface $query, $exit = TRUE) {
  $database = \Drupal::service('database');
  $queryString = $query->__toString();
  $queryArgs = $query->arguments();
  $queryString = preg_replace('~\{([^\}]+)\}~', '$1', $queryString);

  // Sort the args so replacements don't mess eachother up.
  krsort($queryArgs);

  foreach ($queryArgs as $key => $data) {
    $is_bracket_placeholder = substr($key, -2) === '[]';
    $is_array_data = is_array($data);
    if ($is_bracket_placeholder && !$is_array_data) {
      throw new \InvalidArgumentException('Placeholders with a trailing [] can only be expanded with an array of values.');
    }
    elseif (!$is_bracket_placeholder) {
      if ($is_array_data) {
        throw new \InvalidArgumentException('Placeholders must have a trailing [] if they are to be expanded with an array of values.');
      }
      // Scalar placeholder - does not need to be expanded.
      continue;
    }
    // Handle expansion of arrays.
    $key_name = str_replace('[]', '__', $key);
    $new_keys = [];
    // We require placeholders to have trailing brackets if the developer
    // intends them to be expanded to an array to make the intent explicit.
    foreach (array_values($data) as $i => $value) {
      // This assumes that there are no other placeholders that use the same
      // name.  For example, if the array placeholder is defined as :example[]
      // and there is already an :example_2 placeholder, this will generate
      // a duplicate key.  We do not account for that as the calling code
      // is already broken if that happens.
      $new_keys[$key_name . $i] = $value;
    }

    // Update the query with the new placeholders.
    $queryString = str_replace($key, implode(', ', array_keys($new_keys)), $queryString);

    // Update the args array with the new placeholders.
    unset($queryArgs[$key]);
    $queryArgs += $new_keys;
  }

  foreach ($queryArgs as $placeholder => $value) {
    $value = $database->quote($value);
    $queryString = str_replace($placeholder, $value, $queryString);
  }

  echo "<pre>";
  print_r($queryString);
  echo "</pre>";

  if ($exit) {
    exit();
  }
};