How to filter Views records by day-of-week, including multiples

Question

I have a need to filter many records by the day of the week derived from a date-only field in those records (i.e. not today's dow).

I would like a solution that works with views filters, as the wider context is that the view itself is being manipulated in code to supply which days should be included -- note plural. Basically, a paragraph is configured with List(text) fields for the weekdays and during preprocess the view is loaded and the field values 'poked' into the view's value instead of the fixed original value. Several other fields are updated likewise.

If I was doing this in plain SQL I might use DATE_FORMAT(fieldname, "%a") IN ("Wed", "Fri") or something similar. I have looked at various existing modules - views_dates and views_date_format_sql in depth - and have noted D7-only views_dow. However I can't quite see a way forward using those (even as a base), partly because I'm not very familiar with views internals.

Answer 1

I have managed to create a usable filter, though there is still a portability issue. This is the filter class I ended up with:

/**
 * Filter to handle dates stored as a timestamp.
 *
 * @ingroup views_filter_handlers
 *
 * @ViewsFilter("views_dates_day_of_week")
 */
class DayOfWeek extends InOperator {

  /**
   * {@inheritdoc}
   */
  protected function opSimple() {
    if (empty($this->value)) {
      return;
    }
    $this->ensureMyTable();

    /** @var \Drupal\views\Plugin\views\query\Sql $query */
    $query = $this->query;

    $placeholder = $this->placeholder().'[]';
    $fieldexpr = $this->getDateField();
    // For PostgreSQL use EXTRACT (DOW FROM field), result is numeric.
    // For SQLite use strftime("%w", field), result is numeric.
    // BUT : How to tell if these are needed!!
    $lhs = "DATE_FORMAT({$fieldexpr}, '%w')";
    $snippet = $lhs . ' IN (' . $placeholder . ')';
    // Keys are 0..6, to match %w format.
    $possibles = array_keys($this->value);

    $query->addWhereExpression(
      $this->options['group'],
      $snippet,
      [$placeholder => $possibles]
    );
  }

  /**
   * Return an array of day names, keyed by the value of the %w date format.
   *
   * If the 'options callback' is defined, use it to return values, otherwise
   * use the predefined (translatable) versions.
   *
   * Cache the return value in $this->valueOptions for speed.
   *
   * @return array|null
   *   The array of names, keyed by integer 0..6 representing Sunday - Saturday.
   */
  public function getValueOptions() {
    if (isset($this->valueOptions)) {
      return $this->valueOptions;
    }

    if (isset($this->definition['options callback']) && is_callable($this->definition['options callback'])) {
      if (isset($this->definition['options arguments']) && is_array($this->definition['options arguments'])) {
        $this->valueOptions = call_user_func_array($this->definition['options callback'], $this->definition['options arguments']);
      }
      else {
        $this->valueOptions = call_user_func($this->definition['options callback']);
      }
    }
    else {
      // Keys are 0..6 and starting Sunday, to match %w format.
      $this->valueOptions = [
        '0' => $this->t('Sunday'),
        '1' => $this->t('Monday'),
        '2' => $this->t('Tuesday'),
        '3' => $this->t('Wednesday'),
        '4' => $this->t('Thursday'),
        '5' => $this->t('Friday'),
        '6' => $this->t('Saturday'),
      ];
    }

    return $this->valueOptions;
  }
}

Note that the implementation of opSimple has hard-coded use of MySQL. I was unable to find out which DB type this operation is using and therefore adapt to the SQL used by that DB. The builtin 'dateSQL' member of $this is protected and marked views-internal, preventing it being used to identify the db type. I also used the array placeholder feature of Drupal (the ".'[]'" on the placeholder variable) to avoid having to separately quote all the IN values. I also cannot see a way to avoid this operation requiring a sequential search of the db table :(

I switched to using numbers (2) rather than abbrev-names (Tue) because that was more portable between db types and possibly faster. The rest of the code I have inherited/adapted from the drupal.org/project/views_dates:

Add to views_dates.filter.schema.yml:

views.filter.views_dates_day_of_week:
  type: views.filter.in_operator
  label: 'DoW'
  mapping:
    type:
      type: string
      label: 'Type'

Add an entry in views_dates_views_data_alter(), which is :

$data[$table_name][$base_field_id . '_dow'] = [
  'title' => t( '@label as day name', ['@label' => $base_field_label]),
  'help' => t( '@label as day name 0 - 6, representing Sunday (0) to Saturday (6).', ['@label' => $base_field_label]),
  'argument' => [
    'field' => $base_field_id,
    'id' => 'date_dow',
  ],
  'filter' => [
    'field' => $base_field_id,
    'id' => 'views_dates_day_of_week',
  ],
];

Note that, in views_dates.module, entity type 'node' is skipped from adding these new filters - you may wish to change this!