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.

4 Answers 4

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']);
}
5
  • 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, 2012 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, 2012 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 Nov 20, 2012 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. Apr 22, 2015 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.
    – Blissful
    Nov 27, 2015 at 11:33
4

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
0
3
function hook_views_pre_execute(&$view) {
  if ($view->name == 'XYZ') {
    $query = (string)$view->build_info['query'];
    echo $query;
  }
}
-1

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

    $viewr = views_get_view('your_view_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.