Modifying a Views 3 underlying query

Question

I have a View and it's based on a query and I'd like to modify the query that it is based on. What is the best way to do this?

More specifically, the query generated by Views is like this:

SELECT blah, blah, field-repeating-date, field-startdate, blah blah
FROM etc. etc.
WHERE this, that and the other
ORDER BY field-repeating-date ASC, field-startdate ASC

and I'd like the query to be:

SELECT blah, blah, IFNULL(field-repeating-date, field-startdate) AS thedate, blah blah
FROM etc. etc.
WHERE this, that and the other
ORDER BY thedate

in order to be able to mix the two date fields and have them sort properly. The modified query does what I want when entered through phpMyAdmin.

I'm using Drupal 7 with Views 3.

Edit: I did find a solution, but I can't post an answer for another 3 hours because I do not have a reputation here.

Answer 1

A little googling and I found one solution, I'm not sure if it is the correct way to go.

I created a small module with one function, installed and enabled the module and it seems to work exactly as I need.

The mymodule.module file contains the following code:

function mymodule_views_query_alter(&$view, &$query) {
  // this is the name of our View and Display
  if ($view->name == 'upcoming' && $view->current_display == 'block') {
    // override the ordering
    // this is because the two different date fields are one or the other
    // but not both fields, so we coalesce.
    $query->orderby = array(
      array(
        'field' => 'COALESCE( field_data_field_repeatdate_field_repeatdate_value, field_data_field_startdate_field_startdate_value)',
        'direction' => 'ASC',
      )
    );
  }
}

This gets called in when needed and uses COALLESCE to on the sort order (

Answer 2

You can use hook_views_query_alter

This hook should be placed in MODULENAME.views.inc and it will be auto-loaded. MODULENAME.views.inc must be in the directory specified by the 'path' key returned by MODULENAME_views_api(), or the same directory as the .module file, if 'path' is unspecified.

Parameters

$view: The view object about to be processed.

$query: An object describing the query.

Read Views hooks