I need to incorporate appointment calendaring into my app,
including recurring events (like “every other Thursday” or
“annually on July 17th”).
Before I get into this, I want to make sure I’m not
reinventing the wheel cuz while the one-shot appointments are
straightforward, synthesizing appointments from recurrent
events is more work.
Anyone doing something like this want to share some insights?
Sure, I just implemented something like this. I was completely at sea as
to how to do it efficiently until I hit upon this solution - create a
table of dates, or a stored procedure which enumerates dates given a
range, against which you can join. I used the former since I needed to
flag our holidays as special dates anyway. I ended up with four tables:
events - event metadata
library_dates - date enumeration
occurrences - specific dates on which events occur
recurrences - patterns by which events recur
I then created a view titled “events_library_dates” which uses the data
in these tables to generate what looks to rails like a normal HABTM join
table for events and dates. There are one minor gotcha I’ve run across
with this approach so far - when I destroy an event, AR tries to destroy
the rows in the “join table”, which of course it cannot, but I tagged
the HABTM relation with a noop sql statement in the :delete_sql option
and everything’s hunky dory again.
(It would be better to tag the relation itself as read-only, but AR
doesn’t seem to support that; I’ve filed an enhancement request for this
at http://dev.rubyonrails.org/ticket/8045 just for grins, I’m sure the
ticket sit there gathering dust unless and until I’m able to scratch my
own itch.)
Anyway, that’s the gist. For the record, my recurrences table has three
columns:
monthday
weekday
monthweek
so it only handles cases like “the 23rd of every month”, “every
wednesday”, “3rd wednesday of the month”, and “last wednesday of the
month”. Expanding it to accomodate annually recurring events or
whathaveyou should be relatively straightforward.
The events_library_dates view looks like:
CREATE VIEW events_library_dates AS
SELECT
ld.id AS library_date_id,
e.id AS event_id
– select all dates
FROM library_dates ld
– multiply by all events
LEFT OUTER JOIN events e ON 1=1
– decorate with recurrences
LEFT OUTER JOIN recurrences r ON r.event_id = e.id AND
((r.monthday IS NOT NULL AND DATEPART(dd, ld.start_date) = r.monthday)
OR
(r.monthday IS NULL AND r.weekday IS NOT NULL AND DATEPART(dw,
ld.start_date) = r.weekday AND
(r.monthweek IS NULL OR
(r.monthweek > 1 AND DATEPART(dd, ld.start_date) BETWEEN
7*(r.monthweek-1) + 1 AND 7*(r.monthweek-1) + 7) OR
(r.monthweek = -1 AND DATEPART(dd, ld.start_date) BETWEEN
DATEPART(dd,
DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,0,ld.start_date)+1,0)))-6 AND
DATEPART(dd,
DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,0,ld.start_date)+1,0))))
)
))
– decorate with occurrences
LEFT OUTER JOIN occurrences o ON o.event_id = e.id AND o.library_date_id
= ld.id
– remove holidays
WHERE ld.holiday = 0 AND
– and dates before the start date, if any
(e.start_date IS NULL OR (e.start_date IS NOT NULL AND ld.start_date >=
e.start_date)) AND
– and dates after the end date, if any
(e.end_date IS NULL OR (e.end_date is NOT NULL AND ld.start_date <=
e.end_date)) AND
– and dates with no events
(r.id IS NOT NULL OR o.id IS NOT NULL)
the date functions are sqlserver-specific, but analogues should exist
for whatever database you’re using.
The view logic is a bit hairy, but it performs well and is neatly
encapsulated. I’m curious if other folks think this is a decent approach
or if I’m overlooking something.