2

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.

  • "I have a View and it's based on a query", what does that mean? Have you seen hook_views_query_alter? – Letharion Dec 13 '12 at 15:46
  • @Letharion - all Views are based on a SQL query. This can be displayed by setting 'Display Query' to on in the Views settings. It's a useful way of working out what is happening behind the scenes. – jalal Dec 13 '12 at 15:59
2

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 (

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.