Get the actual query executed by a view

Question

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.

Answer 1

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

Answer 2

No need for patches or hooks. Just use the following code.

// 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('  ', '  ', nl2br($string));

Its output is the following.

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

Answer 3

function hook_views_pre_execute(&$view) {
  if ($view->name == 'XYZ') {
    $query = (string)$view->build_info['query'];
    echo $query;
  }
}

Answer 4

//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

Answer 5

Get query without running view->execute. Wrapped in a function with the _fast suffix.

function get_view_query_string_fast($view_name, $view_display, $pager = TRUE) {
  // Get view and set display.
  $view = views_get_view($view_name);
  $view->set_display($view_display);

  // Disable Pager.
  if (!$pager) {
    $pager = $view->display_handler->get_option('pager');
    $pager['type'] = 'none';
    $view->display_handler->set_option('pager', $pager);
  }

  // Get query object.
  $view->pre_execute();
  $view->build();
  $query = $view->build_info['query'];
  $query->addMetaData('view', $view);
  $query->preExecute();

  // Get the query string, ready to run.
  $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);
    }
  }
  return $query_string;
}

$sql_a = get_view_query_string_fast('frontpage', 'page', TRUE);

SELECT node.sticky AS node_sticky, node.created AS node_created, node.nid AS nid, 'frontpage:page' AS view_name
FROM 
node node
WHERE (( (node.promote <> 0) AND (node.status = 1) ))
ORDER BY node_sticky DESC, node_created DESC
LIMIT 10 OFFSET 0

$sql_b = get_view_query_string_fast('frontpage', 'page', FALSE);

SELECT node.sticky AS node_sticky, node.created AS node_created, node.nid AS nid, 'frontpage:page' AS view_name
FROM 
node node
WHERE (( (node.promote <> 0) AND (node.status = 1) ))
ORDER BY node_sticky DESC, node_created DESC