How to display the query executed by the Drupal view

I want to display the query that is executed in the drupal view. Currently in the view editor it shows the query however I have a need to use that query in my code to download an excel version of the view.

Is there a way to get the executed query the same way it's shown in the "editor" window of the views menu? I want this at the time the the view is shown.

What I plan to do here is to capture the query in the footer, and have that query posted to a process which will send back an XLS resultset. So i'd like the exact query the view is using开发者_StackOverflow中文版 to display the results.

Or you can use hook_views_pre_execute along with devel's dpq function:

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

Set the Footer's Input Format to PHP, and paste this snippet into the Footer text:

<pre><?php
  $v = views_get_current_view();
  $query = db_prefix_tables(vsprintf($v->build_info['query'], $v->build_info['query_args']));
  $replacements = module_invoke_all('views_query_substitutions', $v);
  $query = str_replace(array_keys($replacements), $replacements, $query);
  echo $query;
?></pre>

For a syntax-highlighted query (using geshifilter.module), use the following snippet:

<pre><?php
  require_once drupal_get_path('module', 'geshifilter') .'/geshifilter.pages.inc';
  $v = views_get_current_view();
  $query = db_prefix_tables(vsprintf($v->build_info['query'], $v->build_info['query_args']));
  $replacements = module_invoke_all('views_query_substitutions', $v);
  $query = str_replace(array_keys($replacements), $replacements, $query);
  echo geshifilter_process($query, 'sql');
?></pre>

(Stemmed from @Owen's answer and discussion with @Mech-Software in the comments.)

The query exists in the view object. Depending on where you want to use it, you may want to add the variable in a views preprocess function, or the location you're calling the view (if calling it programatically).

If you're just using the default template for it though, you can have access to it there:

// ex. somewhere in your views-view--VIEW_NAME.tpl.php
<?php print db_prefix_tables($view->build_info['query']); ?>

Be careful if your process takes arbitrary SQL though, may be better to call it with the view name, and have it programatically pick up results as required. Or, have a secondary display on your view which returns the result in a XLS result set directly.

The devel module can log queries for you.

http://drupal.org/project/views_bonus will help to export from Views.

as a side note, for the excel export, Have you tried Views Data Export Module?

How to output view queries to the screen.

This works for me in Drupal 7.

go to:

views/plugins/views_plugin_query_default.inc

find this function:

/**
 * Generate a query and a countquery from all of the information supplied
 * to the object.
 *
 * @param $get_count
 *   Provide a countquery if this is true, otherwise provide a normal query.
 */
function query($get_count = FALSE) {

just before the very end of the function, use dpq($query);

  // Add all query substitutions as metadata.
  $query->addMetaData('views_substitutions', module_invoke_all('views_query_substitutions', $this));
dpq($query);
    return $query;
  }