How do I use hook_views_query_alter() to re-write a query?
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 (&amp;$view, &amp;$query) {
  $display_id = $view-&gt;current_display;
  $uid = $view-&gt;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