1

I have two tables, events and event_logs.

The events table stores the theoretical data on a recurring event whereas the event_log stores specific information on a particular datetime.

Event

+-----+---------------------------+----------+-------------------------------------------------------------------------------------------+
| id  | start_at                  | place_id | recurring_schedule                                                                        |
+-----+---------------------------+----------+-------------------------------------------------------------------------------------------+
| 358 | 2015-01-23 20:00:00 +0000 | 412      | {"validations":{"day":[2]},"rule_type":"IceCube::WeeklyRule","interval":1,"week_start":0} |
| 359 | 2016-01-22 19:30:00 +1100 | 414      | {"validations":{"day":[1]},"rule_type":"IceCube::WeeklyRule","interval":1,"week_start":0} |
| 360 | 2016-02-01 19:00:00 +1100 | 415      | {"validations":{"day":[4]},"rule_type":"IceCube::WeeklyRule","interval":1,"week_start":0} |
| 361 | 2016-02-01 20:00:00 +0000 | 416      | {"validations":{"day":[4]},"rule_type":"IceCube::WeeklyRule","interval":1,"week_start":0} |
| 362 | 2014-02-13 20:00:00 +0000 | 417      | {"validations":{"day":[2]},"rule_type":"IceCube::WeeklyRule","interval":1,"week_start":0} |
+-----+---------------------------+----------+-------------------------------------------------------------------------------------------+
  • start_at (date time, used for the DOW and start time, actual date is ignored)
  • recurring_schedule (weekly, bi-weekly, monthly), this is stored as a ruleset in a text column using IceCube but it could just have easily been stored as a column with 3 choices.

Event_log

+-------+----------+----------+---------------------------+------------+
| id    | place_id | event_id | start_at                  | state      |
+-------+----------+----------+---------------------------+------------+
| 20600 | 58       | 35       | 2017-01-24 19:30:00 +0000 | completed  |
| 13236 | 105      | 94       | 2015-09-07 20:00:00 +0100 | pending    |
| 21388 | 311      | 269      | 2017-04-09 18:30:00 +0100 | failed     |
| 25265 | 23       | 45       | 2017-10-22 20:00:00 +0100 | incomplete |
| 25538 | 283      | 242      | 2017-11-07 19:30:00 +1100 | failed     |
+-------+----------+----------+---------------------------+------------+

The problem is reconciling recurring data with actual occurrences, data on the EventLog doesn’t exist, but for speed and necessity I would like to create some sort of aggregated view, eg scheduled_events (which looks and acts like the event_log) but which combines theoretical future events and their start times & dates with actually occurring EventLogs (when they exist). But I’m unsure how to do this if someone has some suggestions.

Thanks,

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.