How to Override Drupal Views Query?

Question

How can I replace a Views 3 query with a custom SQL query in code?

Answer 1

From what I understand, you can use hook_views_query_alter to modify the query. I would assume you could also use it to replace the query. Here are a couple hook_views_query_alter examples, to get you started:

• Using Hook Views Query Alter
• Alter a View query to add a WHERE clause with OR group operator.

Answer 2

This might no longer be pertinent to you, but there's what seems to be a very useful discussion on Drupal.org at Implementing custom SQL query for Views / Filters that looks like it's answering my similar question.

In particular, the initial poster has suggested attaching to hook views_views_pre_execute, which someone else mentioned can be put into a custom module like:

function mymodulename_views_pre_execute(&$view) {
   if($view->name=="groups_list2") {
      // ...
      $view->build_info['query'] = "SELECT node.nid AS nid ".
         "FROM node WHERE node.type='%s'"; // wrapped for legibility
   }
}

And another poster mentioned mySQL Views and Table Wizard (direct link), though they did mention it's worth bearing in mind this article about mySQL Views performance

It's definitely worth reading the whole thread on Drupal.org, though, as I found it really useful; I hope someone else does too.

Update: Indeed, this is precisely what we're now doing — overriding views_views_pre_execute in a custom module to inject new SQL before it gets to the database. I had opened a similar (but more specific) question on Drupal.SE at Slow query with large dataset in Drupal views — better to process in SQL or PHP?, which you might find useful.