How to display the query executed by the Drupal view

Question

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 to display the results.

score 7 · Answer 1 · answered Apr 23 '14 at 13:45

7

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']);
}

answered Apr 23 '14 at 13:45

Felix Eve

3,426
3
36
48

smokris · Answer 2 · 2011-01-05T19:29:26.530

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.)

score 3 · Accepted Answer · answered Feb 26 '10 at 00:02

3

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.

answered Feb 26 '10 at 00:02

Owen

76,727
20
113
113

This looks more or less what I want but I'm trying to put this into the "footer" vs the view.tps.php. I tried adding globals $view but it returned nothing. This looks promising if I can get it working. – Mech Software Feb 26 '10 at 15:38
Woot, I'm almost there, I got it working EXCEPT for the substituion of the form data. What's missing? $current_view = views_get_current_view(); print db_prefix_tables($current_view->build_info['query']); However, the query comes back with [sql clipped] WHERE (node.status <> 0) AND (node.type IN ('%s')) AND (casetracker_case.case_status_id = '%s') ORDER BY node_comment_statistics_last_updated DESC How do I get the %s substitution included? – Mech Software Feb 26 '10 at 15:42
Got it working by using the query_args and running that through sprintf. Thanks a ton, you saved me a lot of work. – Mech Software Feb 26 '10 at 19:29

score 2 · Answer 4 · answered Feb 25 '10 at 22:55

2

The devel module can log queries for you.

answered Feb 25 '10 at 22:55

googletorp

32,389
15
62
81

score 0 · Answer 5 · answered Mar 19 '14 at 09:49

0

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

answered Mar 19 '14 at 09:49

arcsum

156
8

score 0 · Answer 6 · answered Feb 25 '10 at 23:04

0

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

answered Feb 25 '10 at 23:04

Nikit

5,138
16
29

mcaleaa · Answer 7 · 2013-06-25T10:58:32.277

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;
  }

How to display the query executed by the Drupal view

7 Answers7