Repeating/Recurring Calendar Events

[ADobkin]

I have a table that use dates which need to repeat on a set frequency, such as monthly, yearly, etc. What I have so far is a list of the start dates, which the user enters, along with the frequency. In particular, I would like to use this table along with the Calendar action. So, for example, a weekly event that was scheduled on January 1 should also display on January 8, January 15, January 22, and January 29. The user should only have to enter the date once, and each event should only be stored in the database once.

I have seen various ways of accomplishing something like this with PHP and MySQL, but I'm not sure how to apply it to Xataface. One method I tried was to create a separate calendar table with a list of all known dates over a 10-20 year period. That part was fairly easy. Then, I joined that table with my events table based on the frequency to produce a list of valid dates for the repeating set. This works fine with a raw MySQL query, but not when I use it as the __sql__ declaration in my fields.ini file. It looks like Xataface is retrieving the correct amount of rows, but then it only outputs the last date of each set.

Does anyone have experience with this type of situation?

Thanks,
Alan

[shannah]

The __sql__ directive of the fields.ini file is only meant for adding columns to a set - not rows. It must be the case that the result of the __sql__ query returns the exact same rows as the select * from table query - except that each row may contain more rows.

To accomplish what you want to try, it would probably be best to create a view and treat that view as your events table.

Repeating events are a tricky thing - there doesn't seem to be a single way to do it that is best for every use case. I'm currently developing a calendar module based on http://arshaw.com/fullcalendar/ (separate from the calendar action) that incorporates repeating events. The direction I chose to go was to have a single record for each event and just store the "repeat id" so that events that are part of the same sequence can be identified. This adds overhead when trying to update all of the events together - but it adds flexibility in that events can be modified individually as well as in a group.

Your solution of using a separate table to house the set of possible dates and joining that with the events table seems like quite a slick way to do things also.