Custom filter handler in Drupal 7 and Views 3 for data stored as date and datetime

3 minutters læsning

I just needed to expose a table with legacy data for Drupal 7 with Views 3. However, some of the date in the legacy mysql database was stored as DATE and DATETIME. However, Views only supports timestamps for their datehandling, and I needed to use a filter to filter away older posts in the legacy database.

What I needed to do was build a view with “filter >= -10 day”. First I googled and came across this article: Creating a DATETIME View handler for Views. And that article was a good starting point.

I needed to write my own views filter handler. Views 3 in Drupal 7 relies on the autoload feature, so first I needed to declare my handler in the .info-file:

files[] = my_module_handler_filter_datetime.inc

I also needed to state in my_module.module which views api I am using:

core = "7.x"
name = "My Module"
description = "Example on how to add a filter handler with DATETIME"
package = "My modules"
php = "5.2.4"
project = "my_module"
version = "7.x-0.1"
files[] = "my_module_handler_filter_datetime.inc"
<?php
/**
 * Implementation of hook_views_api().
 */
function vih_course_long_legacy_views_api() {
    return array(
        'api' => 3,
        'path' => drupal_get_path('module', 'vih_course_long_legacy'),
    );
}
<?php
/**
 * Implementation of hook_views_data
 */
function my_module_views_data() {
  // Define the base group of this table. Fields that don't
  // have a group defined will go into this field by default.
  $data['my_table']['table']['group'] = t('My table');
  $data['my_table']['table']['base'] = array(
    'field' => 'date',
    'title' => t('My table'),
    'help' => t('My table help description'),
    'database' => 'my_database');
  $data['my_table']['id'] = array(
    'title' => t('Id'),
    'help' => t('Id for the course'),
    'field' => array('handler' => 'views_handler_field'),
    'argument' => array('handler' => 'views_handler_argument_numeric'),
    'filter' => array('handler' => 'views_handler_filter_numeric'),
    'sort' => array('handler' => 'views_handler_sort_numeric'));
  $data['my_table']['date_created'] = array(
    'title' => t('Date created'),
    'help' => t('Date for creation of post'),
    'field' => array('handler' => 'views_handler_field'),
    'argument' => array('handler' => 'views_handler_argument_date'),
    'filter' => array('handler' => 'my_module_handler_filter_datetime'),
    'sort' => array('handler' => 'views_handler_sort_date'));

  return $data;
}

<?php
/**
 * Custom filter handler for views, that handles DATETIME
 */
class my_module_handler_filter_datetime extends views_handler_filter_date {
  function op_between($field) {
    if ($this->operator == 'between') {
      $a = intval(strtotime($this->value['min'], 0));
      $b = intval(strtotime($this->value['max'], 0));
    }
    else {
      $a = intval(strtotime($this->value['max'], 0));
      $b = intval(strtotime($this->value['min'], 0));
    }

    if ($this->value['type'] == 'offset') {
      // changed from original
      $a = (integer)time() + (integer)sprintf('%+d', $a); // keep sign
      $b = (integer)time() + (integer)sprintf('%+d', $b); // keep sign
      // changed from original ends
    }
    // %s is safe here because strtotime scrubbed the input and we might
    // have a string if using offset.
    $this->query->add_where_expression($this->options['group'], "$field >= '".date("Y-m-d H:i:s", $a)."'");
    $this->query->add_where_expression($this->options['group'], "$field <= '".date("Y-m-d H:i:s", $b)."'");
  }

  function op_simple($field) {
    $value = intval(strtotime($this->value['value'], 0));
    if (!empty($this->value['type']) && $this->value['type'] == 'offset') {
      $this->query->add_where_expression($this->options['group'], "$field $this->operator DATE_ADD(NOW(), INTERVAL $value SECOND)");
    } else {
      $this->query->add_where_expression($this->options['group'], "$field $this->operator $value");
    }
  }
}

As you can see, I have a reference to my own filter here:

'filter' => array('handler' => 'my_module_handler_filter_datetime'),

Then I wrote the handler based on the previous mentioned article. There a some smaller changes, but everything should pretty much be self-explanatory. Have a look through the code.

<?php
/**
 * Custom filter handler for views, that handles DATETIME
 */
class my_module_handler_filter_datetime extends views_handler_filter_date {
  function op_between($field) {
    if ($this->operator == 'between') {
      $a = intval(strtotime($this->value['min'], 0));
      $b = intval(strtotime($this->value['max'], 0));
    }
    else {
      $a = intval(strtotime($this->value['max'], 0));
      $b = intval(strtotime($this->value['min'], 0));
    }

    if ($this->value['type'] == 'offset') {
      // changed from original
      $a = (integer)time() + (integer)sprintf('%+d', $a); // keep sign
      $b = (integer)time() + (integer)sprintf('%+d', $b); // keep sign
      // changed from original ends
    }
    // %s is safe here because strtotime scrubbed the input and we might
    // have a string if using offset.
    $this->query->add_where_expression($this->options['group'], "$field >= '".date("Y-m-d H:i:s", $a)."'");
    $this->query->add_where_expression($this->options['group'], "$field <= '".date("Y-m-d H:i:s", $b)."'");
  }

  function op_simple($field) {
    $value = intval(strtotime($this->value['value'], 0));
    if (!empty($this->value['type']) && $this->value['type'] == 'offset') {
      $this->query->add_where_expression($this->options['group'], "$field $this->operator DATE_ADD(NOW(), INTERVAL $value SECOND)");
    } else {
      $this->query->add_where_expression($this->options['group'], "$field $this->operator $value");
    }
  }
}

Caveat

IMHO this should either be built into Views, or it should be easier to achieve e.g. via the date module (and maybe it is). If you have a better solution, do not hesitate to comment.

Kommentarer

Du kan blive den første til at kommentere på indlægget.

Skriv en kommentar

Skriv meget gerne en kommentar og skriv pænt. Hvis du gerne vil have et svar, så husk at skrive din e-mail eller tjekke tilbage jævnligt. Obligatoriske felter er markeret med *.

Indlæser...
Din e-mail-adresse bliver ikke publiceret.

Denne side beskyttes af reCAPTCHA og derfor gælder Google Privacy Policy og Terms of Service.