Get SQL From View to put in db_query()

Question

I am using views and have a query to return some data. I want to take the SQL from the view and use it in my code.

The SQL the view is creating is:

SELECT node.title AS node_title
FROM 
{node} node
LEFT JOIN {field_data_field_order_reference} field_data_field_order_reference ON node.nid = field_data_field_order_reference.entity_id AND (field_data_field_order_reference.entity_type = 'node' AND field_data_field_order_reference.deleted = '0')
INNER JOIN {node} node_field_data_field_order_reference ON field_data_field_order_reference.field_order_reference_nid = node_field_data_field_order_reference.nid
INNER JOIN {field_data_field_service_status} field_data_field_service_status ON node.nid = field_data_field_service_status.entity_id AND (field_data_field_service_status.entity_type = 'node' AND field_data_field_service_status.deleted = '0')
WHERE (( (node_field_data_field_order_reference.nid = '110' ) )AND(( (node.status = '1') AND (field_data_field_service_status.field_service_status_value = '1') )))
LIMIT 1 OFFSET 0

When I try and run this code in mysql I get back some errors. Such as:

It seems not to like the {}.

Is there an easy way to transform this View SQl into SQL that I can run using db_query()?

Answer 1

Assuming the table and field names are valid, you can run that SQL straight through db_query() and it will work fine.

It won't work if you just put the raw query into an SQL client, as Drupal uses brackets around table names to substitute in any necessary prefix. MySQL doesn't do that, so if you want to run the same query against the server proper, you need to remove the brackets from around the table names.

Answer 2

If you like to get and be able to execute the query for a view you could use the hook_views_post_execute() hook.

The function actually converting each placeholder to its appropriate value are taken from kiamlaluno answer on this page: Print the query which is built using db_select()

function _get_query_string(SelectQueryInterface $query) {
  $string = (string) $query;
  $arguments = $query->arguments();

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

function YOUR_MODULE_views_post_execute(&$view) {
  print str_replace(array("{","}"),
                    array("",""),
                    _get_query_string($view->build_info['query']));

}

If you would like to display the query for a specific view you could check the name of the view inside the hook_views_post_execute() hook:

if($view->name == 'MY_VIEW_NAME') {
}