Auto delete / archive old events

[andybroomfield]

Currently events that are in the past are left on the site, which can be confusing when they appear in search. We would like to (as an option)

• auto archive events once they are past a certian amount of time from when the event was.
• This could take the form of a cron task, or set a scheduled transition to the archive state.
• It would take into account the recurring date so only the last event gets archived

[davidupjohn]

Agree this is needed. I've got 1771 unpublished events and only 265 live ones.

[andybroomfield]

From tech drop in (13/07/2023) we think that this could be it's own sub module and not handled by scheduled transition. We would need a configuration admin page to set how many days past before the event is archived or deleted. We need to use rrule to calculate the end date, and this isn't generally accessible so we'd need a table to store it and then run the query on a cron task.

[andybroomfield]

All events with the date_recur field get added to the table date_recur__node__localgov_event_date so it's possible to query that to get the events and then run a Drupal entity query to load and remove them. Its going to be a bit tricky as there will still be occurrences in the future that are potentially not generated in the table and the table contains all occurrences, so simply quering for all the past ones will still bring in events that have a future one. The date is also being stored as VARCHAR so that might complicate things.

So my thinking is:

1. Do an sql query for all future events on that table (with the time set to a configurable past date like 1 week ago). This query seems to work.

   SELECT DISTINCT entity_id FROM date_recur__node__localgov_event_date WHERE localgov_event_date_end_value >= '2023-08-05'

2. Then use the result as a NOT IN clause on either a query against either the recurring dates table or use in an entity type manager query on the localgov_event content type (could also combine with above for sub queries depending on how good someones SQL is).
3. Pass the resulting nodes to a function to delete or unpublished as desired, most likely run this as a cron task.

[andybroomfield]

Newest query while I've been looking at it

SELECT `entity_id`, `delta`, `localgov_event_date_end_value` FROM `date_recur__node__localgov_event_date` WHERE `localgov_event_date_end_value` <= (NOW() - INTERVAL $max_days DAY) AND `entity_id` > $entity_high_water OR (`entity_id` = $entity_high_water  AND $delta_high_water  > 49) ORDER BY `entity_id` LIMIT $limit_per_cron

eg.

SELECT `entity_id`, `delta`, `localgov_event_date_end_value` FROM `date_recur__node__localgov_event_date` WHERE `localgov_event_date_end_value` <= (NOW() - INTERVAL 30 DAY) AND `entity_id` > 1817676 OR (`entity_id` = 1817676 AND `delta` > 49) ORDER BY `entity_id` LIMIT 50

Then as above, load the resulting entities to check if they have a delta further into the future so the can be ignored or if not archive or delete as required.

Main question is if an sql query is right approach or if there is a Drupal entitiy type way of doing this, since ultimatly it still requires loading the underlying entities.

[vanbbd]

Hi @willguv, @andybroomfield. I can pick this ticket up and work on it.

[andybroomfield]

@vanbbd Yes please!

[vanbbd]

Please assign it to me on backlog board

[willguv]

Hi @vanbbd feel free to assign yourself to work we've agreed on Slack - thanks for chipping in!

(Thanks Andy for assigning)

[willguv]

Hi @vanbbd, how are you getting on with this? Thanks

[vanbbd]

hi @andybroomfield, i have built some UI for the module and start work. I have a couple of questions, and would like to join your online meet up to raise these questions.

[andybroomfield]

Hi @vanbbd Sorry I missed your comment. Do you have progress to show? If you have code you can make a Draft PR and we can take a look at it.

[vanbbd]

hi @andybroomfield , i am about to finished this module. I will share the work shortly, i just need to do some more code to deal with events that having moderation

[willguv]

Hi @vanbbd @andybroomfield is there an update on this please? Many thanks!

[andybroomfield]

@willguv i think we should unassign and allow someone else to work on it.

[willguv]

@joachim-n @rupertj is this something you can roll into your work please?

[joachim-n]

Doesn't this just require views of events to filter by the current date? Or is there a site-wide search I'm not aware of which shows these too?

[willguv]

I think it's about unpublished events being left in the back end

[andybroomfield]

So the original issue is that with lots of events they can cause some issues with the events views. Maybe less so with the move to search api, but there technically present and its a task for someone to delete them. I would be good to have a way of finding events that have ended (+ a period like a month) that can then be either unpublished or deleted.