How to Override a Drupal Views SQL Query

The queries that it builds are most often very elegant, but there are certain cases where you need to use a query that is just a bit too complicated for the Views query generator to build on its own. I ran into this recently where I could not accomplish my task without a join to a sub-query.

If you find this to be the case, you can utilize the views_pre_execute hook to override the query being supplied by Views.

function hook_views_pre_execute(&$view) 
{
  if($view->name == 'your_views_name') 
  {
    $view->build_info['query'] = "SELECT * FROM node";
  }
}

Once you have implemented this in your custom module, if you edit the view on your site, any changes you make that would normally change the query (sort criteria, filters etc.) will have no affect. However, you can utilize the options in 'Basic Settings', 'Page Settings' to edit the properties of how/where the view is going to display. When you use the 'Preview' button to test your view with various displays, your custom SQL query will be displayed so you can verify that it is being used.

You could just create a custom module with custom sql in lieu of using a view. However, if you have already built a view and styled your page/block based on the views generated elements you might not be looking forward to this. In this case you could use the above technique to override the query without disrupting the rest of the work you have done relying on the view.