Saturday, January 7, 2017

Debugging sql select queries

When you build sql select queries using Drupal database API often you need to debug them. I mean to copy sql string, paste into sql editor, run it and see a result. If you cast query object to string you simply get a sql query with placeholders instead of their values. To get a string with replaced placeholders by values you can use this function:
<?php

/**
 * Helper function returns sql string for db_select object.
 *
 * @param \SelectQueryInterface $query
 *   Select query object.
 *
 * @return string
 *   SQL string with replaced parameters.
 */
function module_get_select_query_string(SelectQueryInterface $query) {
  $string = (string) $query;
  $db = Database::getConnection();
  $string = str_replace(['{', '}'], [
    '`' . $db->tablePrefix(),
    '`',
  ], $string);
  $arguments = $query->arguments();

  if (!empty($arguments) && is_array($arguments)) {
    foreach ($arguments as $placeholder => &$value) {
      if (is_string($value)) {
        $value = "'$value'";
      }
    }

    $string = strtr($string, $arguments);
  }

  return $string;
}
You can also use devel's dpq() function but I prefer have a small helper module with needed functions only. Usage example:
<?php

$query = db_select('node', 'n')
  ->condition('status', 1)
  ->fields('n', ['nid']);

print module_get_select_query_string($query);

Key notes:

No comments:

Post a Comment