See what a views query looks like

By berliner, 6 October, 2013

During development I often need to know what a fully build views query looks like. There are several ways to accomplish this:

Unfortunately none of those worked for me, so I build my own:

function mymodule_views_pre_render(&$view) {
  $query = $view->build_info['query'];
  
  $sql = (string) $query;
  $args = array_reverse($query->getArguments());
  $sql = str_replace('{', '', $sql);
  $sql = str_replace('}', '', $sql);
  
  if ($args !== NULL && is_array($args) && count($args)) {
    $sql = vsprintf($sql, $args);
  }
  
  if (count($args)) {
    foreach ($args as $key => $arg) {
      if (is_string($key) && $key[0] == ':') {
        switch (gettype($arg)) {
          case 'string':
            $sql = str_replace($key, '"' . $arg . '"', $sql);
            break;
          case 'integer':
            $sql = str_replace($key, $arg, $sql);
            break;
          case 'double':
            $sql = str_replace($key, $arg, $sql);
            break;
          case 'boolean':
            $sql = str_replace($key, $arg ? '1' : 0, $sql);
            break;
        
        }
      
      }
    }
  }
  d($sql);
}

The d($sql) is a custom function of my sandbox debug module. All it does is print the given string into a log file. (I know, there is the devel module, but sometimes I just prefer my own solutions ...)

The final output is a complete SQL query string with placeholders replaced by the actual filter values and the curly braces removed. Note that this only works in setups where no database prefix is used.

The output can be directly copy-pasted into my database gui or used as-is on the command line.

This should work with all SelectQuery objects, so I have included that into my debug module so that now I can simply use the following to see what views is doing:

function mymodule_views_pre_render(&$view) {
  dquery($view->build_info['query']);
}

See what a views query looks like

Tags