This is a D7 version of the issue #368395: Handling date ranges in Views filter with the 'between' operator.

For "between" queries, either the from or the to date should match the given date range.

I'm not sure if I'm breaking something horribly, so can someone give this a look and see if it makes sense?

from date_views_filter_handler_simple.inc:

function op_between($field) {
    // Add the delta field to the view so we can later find the value that matched our query.
    list($table_name, $field_name) = explode('.', $field);
    if (!empty($this->options['add_delta']) && (substr($field_name, -6) == '_value' || substr($field_name, -7) == '_value2')) {
      $this->query->add_field($table_name, 'delta');
    }

    $min_value = $this->get_filter_value('min', $this->value['min']);
    $max_value = $this->get_filter_value('max', $this->value['max']);
    $field_from = $this->date_handler->sql_field($field, $this->offset);
    $field_from = $this->date_handler->sql_format($this->format, $field_from);
    $placeholder_min = $this->placeholder();
    $placeholder_max = $this->placeholder();
    $group = !empty($this->options['date_group']) ? $this->options['date_group'] : $this->options['group'];

    if (isset($this->definition['additional fields'][1]) && substr($this->definition['additional fields'][1], -7) == '_value2') {
      $field_to = $this->date_handler->sql_field($field . '2', $this->offset);
      $field_to = $this->date_handler->sql_format($this->format, $field_to);

      if ($this->operator == 'between') {
        $this->query->add_where_expression($group, "$field_to >= $placeholder_min AND $field_from <= $placeholder_max", array($placeholder_min => $min_value, $placeholder_max => $max_value));
      }
      else {
        $this->query->add_where_expression($group, "$field_to <= $placeholder_min OR $field_from >= $placeholder_max", array($placeholder_min => $min_value, $placeholder_max => $max_value));
      }
    }
    else {
      if ($this->operator == 'between') {
        $this->query->add_where_expression($group, "$field_from >= $placeholder_min AND $field_from <= $placeholder_max", array($placeholder_min => $min_value, $placeholder_max => $max_value));
      }
      else {
        $this->query->add_where_expression($group, "$field_from <= $placeholder_min OR $field_from >= $placeholder_max", array($placeholder_min => $min_value, $placeholder_max => $max_value));
      }
    }
  }
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

KarlShea’s picture

FileSize
2.63 KB

Oops, too much in the patch.

KarlShea’s picture

Issue summary: View changes

linked to issue

KarlShea’s picture

Category: feature » bug

Ok, I'm bumping this and changing the category because I consider this a bug.

KarenS’s picture

Status: Needs review » Postponed (maintainer needs more info)

I need an example of where you think you are not getting the right response. The code is different than what was in D6, so the D6 solution doesn't apply. In D7 we are getting the range of the date (the time between the start and end date) and getting the desired date range, and then finding the intersection between them.

If you have a concrete example of a D7 view that isn't producing this, I need details on how to reproduce it.

KarenS’s picture

Actually I got that wrong, that is what the contextual filter does. For the filter you are choosing either the start or end date as the date you want to match.

Again, if you think this is work incorrectly, don't jump immediately to a patch, start by explaining how to reproduce whatever you are seeing.

KarlShea’s picture

Status: Postponed (maintainer needs more info) » Needs review

Well I guess this would be adding support for matching both the start and end dates.

The use case we were having problems with was a list of events with an exposed filter. We wanted to see all of the events that were going on within a date range, and just selecting the start date or the end date wouldn't allow that list to work correctly.

For example, if there was an event that ran from Oct 1 to Dec 31, and the user selected a date range of Nov 1 to Dec 1, that event should show up in the list, but without this patch, it wasn't appearing, since neither Oct 1 or Dec 31 was "between" Nov 1 and Dec 1.

KarenS’s picture

Category: bug » feature
Status: Needs review » Needs work

This is a feature request that would change the behavior of the filter in a way that will break for people who use it as designed. The filter deliberately is set up to be Start date between or not between or greater than or less than. If you change the meaning of that it will break for everyone who uses it to mean exactly what it says.

What you want is a completely different filter that does not use Start date and End date but something that represents the range between the start date and end date. I really have no good idea how to create such a thing, but it would definitely have to be a completely different filter with a different name.

You can get what you want by setting your view up differently and use two filters, one for start date less than or equal to the start of the period you want and one for end date greater than or equal to the end date of the period you care about.

KarlShea’s picture

Unfortunately if those filters are exposed, it wouldn't quite work that well. Can there be a "range" operator just like "less than" or "greater than"?

KarlShea’s picture

Ah I misread what you were getting at. Yes, that does sound like it would work. I'll give it a try and come back and post what I did and close the issue if it's working.

KarlShea’s picture

Status: Needs review » Closed (works as designed)

That is the solution:

Date - end date (value2): Label Start date, operator >=
Date - start date (value): Label End date, operator <=

which creates the query "node_to >= user_from && node_from <= user_to"

Edit: bolded parts, because the labels are supposed to be the "opposite" of the dates they are filtering by.

Alan D.’s picture

Status: Needs work » Active

Sorry to reopen, but I think that there is an issue with ranges.

If start is less than x and end is greater than x, the range does not pick up any of the dates that cover the entire range. IE: We need

  (
    (
      DATE_FORMAT(FROM_UNIXTIME(field_data_field_event_date.field_event_date_value), '%Y-%m-%d') >= '2011-12-13' 
      AND
      DATE_FORMAT(FROM_UNIXTIME(field_data_field_event_date.field_event_date_value), '%Y-%m-%d') <= '2012-12-13'
    )
    OR
    (
      DATE_FORMAT(FROM_UNIXTIME(field_data_field_event_date2.field_event_date_value2), '%Y-%m-%d') >= '2011-12-13' 
      AND
      DATE_FORMAT(FROM_UNIXTIME(field_data_field_event_date2.field_event_date_value2), '%Y-%m-%d') <= '2012-12-13'
    )
    OR
    (
      DATE_FORMAT(FROM_UNIXTIME(field_data_field_event_date1.field_event_date_value1), '%Y-%m-%d') <= '2011-12-13' 
      AND
      DATE_FORMAT(FROM_UNIXTIME(field_data_field_event_date2.field_event_date_value2), '%Y-%m-%d') >= '2012-12-13'
    )
  )
Alan D.’s picture

Nope. Adding another OR condition to see if the start date is before the start date and finish date is after the end date worked.

I done this manually using a views query alter. I've tried to make this as dynamic as possible: The first is a timestamp field and the next is a date only field.

function htevents_views_query_alter(&$view, &$query) {
  static $placeholder = 1;
  switch ($view->name . ':' . $view->current_display) {
    case 'events:pane_search_list':
      if (!empty($query->where['date'])) {
        if (preg_match('/FROM\_UNIXTIME\(([^\.]*)\.([^\)]*)\)/i', $query->where['date']['conditions'][0]['field'], $matches)) {
          if (preg_match('/(\:[a-z0-9\_]*) [^\:]*(\:[a-z0-9\_]*)$/', $query->where['date']['conditions'][0]['field'], $m2)) {
            $table = $matches[1];
            $field = $matches[2];
            $ph1 = ':htevents_alter' . ($placeholder++);
            $ph2 = ':htevents_alter' . ($placeholder++);

            $query->where['date']['conditions'][] = array(
              'field' => "DATE_FORMAT(FROM_UNIXTIME({$table}.{$field}), '%Y-%m-%d') <= {$ph1} AND DATE_FORMAT(FROM_UNIXTIME({$table}2.{$field}2), '%Y-%m-%d') >= {$ph2}",
              'value' => array(
                $ph1 => $query->where['date']['conditions'][0]['value'][$m2[1]],
                $ph2 => $query->where['date']['conditions'][0]['value'][$m2[2]],
              ),
              'operator' => 'formula',
            );
          }
        }
        if (preg_match('/DATE_FORMAT\(([^\.]*)\.([^\,]*)\,/i', $query->where['date']['conditions'][2]['field'], $matches)) {
          if (preg_match('/(\:[a-z0-9\_]*) [^\:]*(\:[a-z0-9\_]*)$/', $query->where['date']['conditions'][2]['field'], $m2)) {
            $table = $matches[1];
            $field = $matches[2];
            $ph1 = ':htevents_alter' . ($placeholder++);
            $ph2 = ':htevents_alter' . ($placeholder++);
            $query->where['date']['conditions'][] = array(
              'field' => "DATE_FORMAT({$table}.{$field}, '%Y-%m-%d') <= {$ph1} AND DATE_FORMAT({$table}2.{$field}2, '%Y-%m-%d') >= {$ph2}",
              'value' => array(
                $ph1 => $query->where['date']['conditions'][2]['value'][$m2[1]],
                $ph2 => $query->where['date']['conditions'][2]['value'][$m2[2]],
              ),
              'operator' => 'formula',
            );
          }
        }
      }
      break;
  }
}

As an aside, having only one date from the date range means that the query does not kick in, although I would have expected this to work.

Eg: Having a starting range would eliminate all fields that have a finishing date that fall before the starting range, and vice versa, having a finishing date would eliminate all fields that have a starting date greater than fall after the finishing range filter date.

KarlShea’s picture

Status: Active » Needs review
FileSize
2.48 KB

Are you sure that's true?

I've uploaded a picture of what I think the four possibilities are.

The shaded grey area is the date range given by a starting and ending date.

It seems to me that all four "events" that fall over that range will get picked up by these two statements:

event end date >= user start date
AND
event start date <= user end date

That even covers case #4, which is an event that is completely outside the selected range. Its start date is still less than the range end date, and the end date is greater than the range start date.

Alan D.’s picture

Status: Closed (works as designed) » Fixed

So true! Thanks for the refresher of my now stale logic skills!

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

Anonymous’s picture

Issue summary: View changes

PHP formatting