23

I need to find out the SQL query that is executed by a certain query. Views module can show the SQL when configuring the view but apparently the query is not the actual query that gets run in all cases.
I am aware that the Devel module can show the database queries, but there is no way with devel to show the actual queries except clicking the associated 'A' link with every query and there are hundreds of them.

How can I find out the actual query that the view executes? The view is displayed as a block.

40

You have to use hook_views_pre_execute and with Devel installed use dpq to view the SQL String:

function hook_views_pre_execute(&$view) {
  dpq($view->build_info['query']);
}
  • Thanks. It reads in the hook api page that "The query is now fully built, but it has not yet been run through db_rewrite_sql." Does this mean that there is a chance that some other hook might rewrite the sql before its run? Then I would not get the actual query in all situations. – jjei Apr 24 '12 at 9:13
  • 2
    pre_render is probably the best, but I'm not sure the query will be very much different in most cases. – Countzero Apr 24 '12 at 9:41
  • I actually think there's a bug in Views in some cases, I've just posted an issue drupal.org/node/1845772 – Sean Bannister Nov 20 '12 at 13:54
  • 1
    $view->build_info['query'] appears to be the same query Views displays if you enable SQL Query output in Views settings. – Johnathan Elmore Apr 22 '15 at 14:50
  • This is useless. Example SELECT users.uid AS uid, users.created AS users_created, users.language AS users_language, users.mail AS users_mail, users.name AS users_name, 'user' AS field_data_field_first_name_user_entity_type, 'user' AS field_data_field_last_name_user_entity_type, 'user' AS field_data_field_date_of_birth_user_entity_type FROM {users} users WHERE (( (users.status <> '0') AND (users.created > 1441641600) )) ORDER BY users_created DESC LIMIT 20 OFFSET 0 I have "field_data_field_first_name_user_entity_type" which is user field and in doesnt give proper results without alterations. – Marko Blazekovic Nov 27 '15 at 11:33
4
function hook_views_pre_execute(&$view) {
  if ($view->name == 'XYZ') {
    $query = (string)$view->build_info['query'];
    echo $query;
  }
}
2

No need for patches or hooks.

// Run the view.
$view = views_get_view('frontpage');
$view->set_display('page');
$view->pre_execute();
$view->execute();

/* Magic Below Here */
// Get query from the view.
$query = $view->query->query();

// Format SelectQueryInterface into a string.
$string = (string) $query;

// Replace arguments.
$arguments = $query->arguments();
if (!empty($arguments) && is_array($arguments)) {
  foreach ($arguments as $placeholder => &$value) {
    if (is_string($value)) {
      $value = "'$value'";
    }
  }
  $string = strtr($string, $arguments);
}

// Format the query string for more readable output.
$string = str_replace(array(' {', "\n{"), ' ', $string);
$string = str_replace(array('} ', "}\n"), ' AS ', $string);
$string = str_replace(', ', ",\n  ", $string);
$string = str_replace(' AND ', "\n  AND ", $string);
$string = str_replace(' ON ', "\n  ON ", $string);
$string = str_replace('SELECT ', "SELECT\n  ", $string);
$string = str_replace('ORDER BY ', "ORDER BY\n  ", $string);

// echo $string;
echo str_replace('  ', '&nbsp;&nbsp;', nl2br($string));

Gives this as output

SELECT
  node.sticky AS node_sticky,
  node.created AS node_created,
  node.nid AS nid,
  'frontpage:page' AS view_name
FROM  node AS node
WHERE (( (node.promote <> 0)
  AND (node.status = 1) ))
ORDER BY
  node_sticky DESC,
  node_created DESC
LIMIT 10 OFFSET 0
  • I'm sorry, but my output is different:SELECT node.nid AS nid, 'node' AS field_data_field_name_node_entity_type, 'node' AS field_data_field_surname_node_entity_type, ecc... – Leo Oct 15 '18 at 10:46
  • can you help me? – Leo Oct 15 '18 at 10:47
  • 1
    @Leo I need more info as to what view your trying to run this on. The output is for the frontpage view out of the box unaltered; sounds like you have changed the frontpage view's configuration so of course the SQL will be different. – mikeytown2 Oct 15 '18 at 18:10
  • Thank you for answering me, maybe I will create a question and insert the link below, in order not to spam this answer – Leo Oct 16 '18 at 8:33
  • the link of the question, I hope I was clear drupal.stackexchange.com/questions/270994/… – Leo Oct 16 '18 at 9:07
1

Please try this patch:

--- a/sites/all/modules/views/plugins/views_plugin_query_default.inc
+++ b/sites/all/modules/views/plugins/views_plugin_query_default.inc
@@ -1393,6 +1393,19 @@ class views_plugin_query_default extends     views_plugin_query {
           $query->range($offset, $limit);
         }

+        $query_string = (string)$query;
+        $query_string = str_replace('{', '', $query_string);
+        $query_string = str_replace('}', '', $query_string);
+        $query_params = $query->getArguments();
+        foreach($query_params as $placeholder => $value) {
+          if(!is_numeric($value)) {
+            $query_string = str_replace($placeholder, "'$value'",    $query_string);
+          }
+          else {
+            $query_string = str_replace($placeholder, $value, $query_string);
+          }
+        }
+        drupal_set_message($query_string);
         $result = $query->execute();

         $view->result = array();
0

//Get The Results by View Name and Contextual Filter nid here

    $viewr = views_get_view('top_block_on_product_display');
    $args[] = $myarg;
    $display_id = 'default';
    $viewr->set_arguments($args);
    $viewr->set_display($display_id);
    $viewr->execute();
    $view_content = $viewr->result;

See the doc for more reference: https://api.drupal.org/api/views/views.module/function/views_get_view_result/7.x-3.x

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.