How do I use hook_views_query_alter() to re-write a query?

Question

I have a very simple view that lists all the nodes, which shows the following query:

SELECT node.nid AS nid, node.title AS node_title, node.uid AS node_uid, node.created AS node_created, 'node' AS field_data_field_site_node_entity_type, 'node' AS field_data_field_status_node_entity_type
FROM 
{node} node
WHERE (( (node.status = '1') AND (node.type IN  ('ticket')) ))
ORDER BY node_created DESC
LIMIT 50 OFFSET 0

I am trying to overwrite it with a custom database query to list only nodes of the user referenced in the URL and all his invitees. This is the code I am using.

function my_custom_module_views_pre_view(&$view, &$display_id, &$args) {
  if ($view->name == "support" && $display_id == "page" ) {
    // Let's start collecting uids and put current page uid as the first member of array.
    $uids[] = arg(1);

    // Now let's collect all invitees of the page uid author and push into the same array.
   $query = db_select('invite', 'i')
     ->condition('i.uid', arg(1), '=')
     ->condition('i.status', '3', '=')
     ->fields('i', array('invitee'));
   $result = $query->execute();
   foreach ($result as $record) {
     $uids[] = $record->invitee;
   }

   // Now let's pull all the nodes of the inviter (page uid) and his invitees.
  $query = db_select('node', 'n')
     ->condition('n.type', 'ticket', '=')
     ->condition('n.status', '1', '=')
     ->condition('n.uid', $uids, 'IN')
     ->fields('n', array('nid', 'title', 'created'));
   $result = $query->execute();
   foreach ($result as $record) {
     dpm($record->nid.' | '.$record->title.' | '.$record->created);
   }

  }
}

dpm() shows all the correct nodes, but reading hook_views_query_alter() I still can't figure out how to make my view use this query.

How can I achieve this?

Answer 1

While exploring a site-building alternative (see comments in OP), here is a direct answer to the question:

Implementing the hook_views_query_alter should do what you want:

function my_custom_module_views_query_alter (&$view, &$query) {
  $display_id = $view->current_display;
  $uid = $view->args(0); // for a view that is loaded from a URL like /myview_url/UID

  // Your code from the OP follows, modified accordingly
  // You have a query object available as an argument by reference
  // You can add joins and filters to it
  ...
}

You would need to implement the hook_views_api in addition to placing the above code snippet inside the my_custom_module.views.inc file, a minimal implementation would be:

function my_custom_module_views_api() { return array( 'api' => '3', ); }

Cheers!

P.S.: Have a look at this one, if you haven't already => http://www.kdweb.co.uk/blog/drupal-module-views-hook-sequence