Events that have a new draft or review revision no longer show even if still published

[andybroomfield]

When creating a new revision of an event, or an event has a scheduled transition to review, it falls off the event listing.

Steps to reproduce

1. Add moderated content / workflow to the localgov_event content type if not already in place.
2. Create an event with recurring dates in the future
3. Publish the event
4. Create a new draft by selecteing draft or review in the moderation state
5. Go the the /events page

Note that the event, even though still having a published revision, is no longer on the events view.

I did quite a lot of digging on this one, I found that the DB table date_recur__node__localgov_event_date contains both an entity_id and revision_id. However the revision_id is always the latest revision, not the default revision.

Looking at the events views SQL query, there is a join to revision

LEFT JOIN {date_recur__node__localgov_event_date} "date_recur__node__localgov_event_date_node_field_data" ON node_field_data.vid = date_recur__node__localgov_event_date_node_field_data.revision_id

Views query below, which includes BHCC fields.

SELECT "date_recur__node__localgov_event_date_node_field_data"."localgov_event_date_value" AS "date_recur__node__localgov_event_date_node_field_data_localg", "date_recur__node__localgov_event_date_node_field_data"."localgov_event_date_end_value" AS "date_recur__node__localgov_event_date_node_field_data_localg_1", "node__bhcc_event_no_date"."bhcc_event_no_date_value" AS "node__bhcc_event_no_date_bhcc_event_no_date_value", "node_field_data"."nid" AS "nid", "geo_entity_field_data_node__localgov_event_location"."id" AS "geo_entity_field_data_node__localgov_event_location_id", 'localgov_events_listing:page_all_events' AS "view_name"
FROM
{node_field_data} "node_field_data"
LEFT JOIN {date_recur__node__localgov_event_date} "date_recur__node__localgov_event_date_node_field_data" ON node_field_data.vid = date_recur__node__localgov_event_date_node_field_data.revision_id
LEFT JOIN {node__localgov_event_location} "node__localgov_event_location" ON node_field_data.nid = node__localgov_event_location.entity_id AND node__localgov_event_location.deleted = '0'
LEFT JOIN {geo_entity_field_data} "geo_entity_field_data_node__localgov_event_location" ON node__localgov_event_location.localgov_event_location_target_id = geo_entity_field_data_node__localgov_event_location.id
LEFT JOIN {geo_entity__location} "geo_entity_field_data_node__localgov_event_location__geo_entity__location" ON geo_entity_field_data_node__localgov_event_location.id = geo_entity_field_data_node__localgov_event_location__geo_entity__location.entity_id AND geo_entity_field_data_node__localgov_event_location__geo_entity__location.deleted = '0' AND (geo_entity_field_data_node__localgov_event_location__geo_entity__location.langcode = geo_entity_field_data_node__localgov_event_location.langcode OR geo_entity_field_data_node__localgov_event_location__geo_entity__location.bundle = 'address')
LEFT JOIN {node__bhcc_event_no_date} "node__bhcc_event_no_date" ON node_field_data.nid = node__bhcc_event_no_date.entity_id AND node__bhcc_event_no_date.deleted = '0'
WHERE (("node_field_data"."status" = '1') AND ("node_field_data"."type" IN ('localgov_event')) AND ((DATE_FORMAT((date_recur__node__localgov_event_date_node_field_data.localgov_event_date_value + INTERVAL 3600 SECOND), '%Y-%m-%d\T%H:%i:%s') >= DATE_FORMAT(('2024-04-11T23:00:00' + INTERVAL 3600 SECOND), '%Y-%m-%d\T%H:%i:%s')))) OR (("node_field_data"."status" = '1') AND ("node_field_data"."type" IN ('localgov_event')) AND ((DATE_FORMAT((date_recur__node__localgov_event_date_node_field_data.localgov_event_date_value + INTERVAL 3600 SECOND), '%Y-%m-%d\T%H:%i:%s') < DATE_FORMAT(('2024-04-11T23:00:00' + INTERVAL 3600 SECOND), '%Y-%m-%d\T%H:%i:%s'))) AND ((DATE_FORMAT((date_recur__node__localgov_event_date_node_field_data.localgov_event_date_end_value + INTERVAL 3600 SECOND), '%Y-%m-%d\T%H:%i:%s') >= DATE_FORMAT(('2024-04-11T23:00:00' + INTERVAL 3600 SECOND), '%Y-%m-%d\T%H:%i:%s'))))
ORDER BY "node__bhcc_event_no_date_bhcc_event_no_date_value" ASC, "date_recur__node__localgov_event_date_node_field_data_localg" ASC
LIMIT 10 OFFSET 0

[andybroomfield]

Discovery on this https://git.drupalcode.org/project/date_recur/-/blob/3.4.x/src/DateRecurOccurrences.php?ref_type=heads#L69-77

    $isInsert = $event->isInsert();
    if (!$isInsert) {
      // Delete all existing values for entity and field combination.
      /** @var string|int $entityId */
      $entityId = $list->getEntity()->id();
      $this->database->delete($tableName)
        ->condition('entity_id', (string) $entityId)
        ->execute();
    }

and https://git.drupalcode.org/project/date_recur/-/blob/3.4.x/src/DateRecurOccurrences.php?ref_type=heads#L111-114

    if ($entity->getEntityType()->isRevisionable() && $entity instanceof RevisionableInterface) {
      $fields[] = 'revision_id';
      $baseRow['revision_id'] = $entity->getRevisionId();
    }

So what is happening is that every time an event is saved, the date occurances table for that node gets deleted, and new calculated dates added, but only for the current saved revision, which might not be a published one, and that is breaking the query in views.

However it is a service / event subscriber, so maybe there is a way to override this without patching. https://git.drupalcode.org/project/date_recur/-/blob/3.4.x/date_recur.services.yml?ref_type=heads#L6-10

  date_recur.occurrences:
    class: Drupal\date_recur\DateRecurOccurrences
    arguments: ['@database', '@entity_field.manager', '@typed_data_manager', '@entity_type.manager']
    tags:
    - { name: 'event_subscriber' }

[ekes]

Sounds like a bug upstream to me. Wouldn't you want the published version (if there is) to be the one that you calculate the views values for? At least by default.

[andybroomfield]

Very old patch here https://www.drupal.org/project/date_recur/issues/3010184#comment-13156851 Would be worth trying to see if this applies.

[andybroomfield]

BHCC has applied this patch, might be worth including here in case it comes up for others.