FR: Improve date compatiblity with granularity

Hi,

I had to get a exposed filter working with only the years showing. This module exposes the entire time stamp as the id for the field. It also doesn't do a distinct on the dates. This is only part of the solution to making dates work. I also wrote a seperate views_query_alter to actually select the right rows.

Jaime.

Quick update to the patch to be more generic and select dates.

Just saw your comment now david! Thanks so much. I'll update here if I do any more changes.

Any patches for the latest release 7.x-1.3?

Was this particular patch committed? If so, do I need to do anything else to get this module working for post dates? Like jaimekristene I need an exposed filter with only the years. When I use views selective filters on the post date, it is returning duplicates.

<select>
<option value="All">Any</option>
<option value="1397759794">2014</option>
<option value="1429306922">2015</option>
<option value="1429298641">2015</option>
</select>

Any thoughts? How can we strip the timestamp and just use the year or month?

So as I research this further, it appears that views selective filters only works when it is returning a timestamp in the url. If it returns something like &created_selective=April+2015, it won't have any results. I'm at a loss :(

What you also need to do is override the op_simple() function within views_handler_filter_selective to something like this:

/**
   * Override op_simple.
   */
  function op_simple() {
    if (empty($this->value)) {
      return;
    }
    $this->ensure_my_table();
    
    // The content date has special filtering needs
    if ($this->real_field == 'field_content_date_value') {
      $timestamp = strtotime($this->value[0]);

      // First day of the month.
      $min = date('Y-m-01 00:00:00', $timestamp);
      
      // Last day of the month.
      $max = date('Y-m-t 23:59:59', $timestamp);

      $this->query->add_where_expression($this->options['group'], "$this->table_alias.$this->real_field BETWEEN '$min' AND '$max'");
    }
    else {
      // We use array_values() because the checkboxes keep keys and that can cause
      // array addition problems.
      $this->query->add_where($this->options['group'], "$this->table_alias.$this->real_field", array_values($this->value), $this->operator);
    }
  }

This is an ugly workaround, but hey, it works.

What needs to be done for this issue imo is the following:
- Create a new filter for dates only
- Use the structure that already exists for the date filter within views
- Rewrite the options accordingly

I came upon this and had a few tweaks, but got it mostly working:

As noted a few places above, the date filtering in the patch mostly works, with a few changes:
- you'll need to change field_content_date to the id of your field (in my case below it was field_date)
- The "$year..." line needs to have strtotime included: $year = date("Y",strtotime($key));

That should get the filter selective-ness working, from there you'll still need a views query alter. Here's what I had to do:

function hook_views_query_alter(&$view, &$query) {
  if ($view->name == 'my_view') {
    // Traverse through the 'where' part of the query.
    foreach ($query->where as &$condition_group) {
      foreach ($condition_group['conditions'] as &$condition) {
        // Rewrite "selective" date field to properly alter the query.
        if ($condition['field'] == 'field_data_field_date.field_date_value') {
          $condition = [
            'field' => 'DATE_FORMAT(field_data_field_date.field_date_value, \'%Y\') = :field_data_field_date_field_date_value',
            'value' => [
              ':field_data_field_date_field_date_value' => $condition['value'][0]
            ],
            'operator' => 'formula',
          ];
        }
      }
    }
  }
}

Hope that helps someone in the future.