Marking a DateField event as "repeating"
Jay Parlar
an efficient way to mark some event as repeating.
The trivial way to create the model would be something like:
REPEAT = (
('D', 'Daily'),
('W', 'Weekly'),
('M', 'Monthly'),
('Y', 'Yearly'),
)
class Event(models.Model):
title = models.CharField(maxlength=32)
date = models.DateField()
repeat = models.CharField(maxlength=1, choices=REPEAT)
When I render a calendar, I render one month at a time. Any
suggestions on an efficient way to query the db for all the events in
a given month?
Maybe it would make more sense to skip the database all together, and
store things as iCal files, using one of the Python iCal libraries to
parse through?
Jay P.
Rubic
> When I render a calendar, I render one month at a time. Any
> suggestions on an efficient way to query the db for all the events in
> a given month?
I don't know enough about your calendar application,
but since we're talking about RFC 2445[1], I would
probably segregate repeating VEVENTs from non-repeating
events. A non-repeating event is simple to add,
edit, and delete. A repeating event requires some
calculation and synchronization with your calendar.
When you present a calendar view, first look up
all active repeating events for a given date
range, calculating the actual days events
fall within that range. The union of these
events and your non-repeating events will
form the basis of your calendar view.
--
Jeff Bauer
Rubicon, Inc.
Malcolm Tredinnick
This shouldn't be too hard to do (it was a fun problem to think about
over lunch). I would create a method on the model that takes a date
(what you are really interested in is the month and year) and returns a
list of the dates in that month when this event occurs. It could even
return copies of itself (or a lighter-weight class) for each date that
it occurs on, with the date set appropriately.
Then you can select all your active events and concatenate together all
those lists, do a single sort on the date field in Python and you're
done.
You could be a little more efficient here by not selecting all events
with repeat = 'Y' and just selecting those in the months they are going
to appear (so one query for most repeat types and another query for 'Y'
types).
With judicious use of list comprehensions, creating all the events for
all dates in a given month should be close to one or two lines of code
(plus the extra model method). The performance should be pretty decent,
too.
Regards,
Malcolm
Jay Parlar
> This shouldn't be too hard to do (it was a fun problem to think about
> over lunch). I would create a method on the model that takes a date
> (what you are really interested in is the month and year) and returns a
> list of the dates in that month when this event occurs. It could even
> return copies of itself (or a lighter-weight class) for each date that
> it occurs on, with the date set appropriately.
>
> Then you can select all your active events and concatenate together all
> those lists, do a single sort on the date field in Python and you're
> done.
>
> You could be a little more efficient here by not selecting all events
> with repeat = 'Y' and just selecting those in the months they are going
> to appear (so one query for most repeat types and another query for 'Y'
> types).
>
> With judicious use of list comprehensions, creating all the events for
> all dates in a given month should be close to one or two lines of code
> (plus the extra model method). The performance should be pretty decent,
> too.
It is one of those problems that's just begging me to have a crack at
it in IPython, and see what I can come up with. I'm just occupied with
other things right now, so I was hoping someone else had already done
it :)
I'm not quite understanding part of your first paragraph though: "I
would create a method on the model that takes a date (what you are
really interested in is the month and year) and returns a list of the
dates in that month when this event occurs"
If the model is taking a date, then what is "this event"? Part of the
goal would be to determine *which* events occur in a given month. The
second step would be to figure out all the times those events do
occur.
Given all the events that do occur in a given month, the rest of your
steps make sense, but I'm still not sure how I'd find that initial
list of events.
Thanks,
Jay P.
Malcolm Tredinnick
[...]
> I'm not quite understanding part of your first paragraph though: "I
> would create a method on the model that takes a date (what you are
> really interested in is the month and year) and returns a list of the
> dates in that month when this event occurs"
>
> If the model is taking a date, then what is "this event"? Part of the
> goal would be to determine *which* events occur in a given month. The
> second step would be to figure out all the times those events do
> occur.
>
> Given all the events that do occur in a given month, the rest of your
> steps make sense, but I'm still not sure how I'd find that initial
> list of events.
Sorry, I skipped the first step in the reasoning because I thought it
was obvious. My bad.
Except for events that repeat only yearly, every single one of your
events is going to occur at least once in any given month. The only
exceptions will be things like events with monthly repetition on the
30th of the month and where you're considering the month of February.
But that case occurs infrequently enough that you can ignore it.
So, your initial list is *every event*. Then you have a method on the
Event model that converts that single event + a month and year (i.e. a
date) into a list of events on particular dates in that month. The
slight improvement here is to select every event except yearly ones and
then treat yearly recurrences as special. Which approach is best depends
on how common yearly events are.
Iterate over your set of events, calling the special method and have
that method return a list of (date, title) tuples, for example. Then
merge those lists (iterate and merge in a single list comprehension),
sort the list and you're ready to put them on a calendar.
Is that clearer?
Regards,
Malcolm
Jay Parlar
> was obvious. My bad.
>
> Except for events that repeat only yearly, every single one of your
> events is going to occur at least once in any given month. The only
> exceptions will be things like events with monthly repetition on the
> 30th of the month and where you're considering the month of February.
> But that case occurs infrequently enough that you can ignore it.
>
> So, your initial list is *every event*. Then you have a method on the
> Event model that converts that single event + a month and year (i.e. a
> date) into a list of events on particular dates in that month. The
> slight improvement here is to select every event except yearly ones and
> then treat yearly recurrences as special. Which approach is best depends
> on how common yearly events are.
>
> Iterate over your set of events, calling the special method and have
> that method return a list of (date, title) tuples, for example. Then
> merge those lists (iterate and merge in a single list comprehension),
> sort the list and you're ready to put them on a calendar.
>
> Is that clearer?
>
Ok, my bad, I left one option out of my original model, namely
"NoRepeat", for one-off events.
But talking this through with you has filled in the blanks. So I think
it stands as follows:
1) Select all events that are NoRepeat, and find the ones that are in
the desired month.
2) Then do everything you said on the set of events that are not
NoRepeat, plus my results from step 1.
Sound good?
Of course, this is a simplified model. If I want to allow all the
features of a regular calendar app, I'll need to support custom
repeats, like "Every second Thursday", or "Every 5 months on the
17th". iCal on OS X has a very nice interface for selecting these
custom repeats, I guess I'll try and copy that.
Jay P.
Malcolm Tredinnick
Yep.
>
> Of course, this is a simplified model. If I want to allow all the
> features of a regular calendar app, I'll need to support custom
> repeats, like "Every second Thursday", or "Every 5 months on the
> 17th". iCal on OS X has a very nice interface for selecting these
> custom repeats, I guess I'll try and copy that.
The same logic should mostly apply, too. If you're considering monthly
intervals, most of your events will occur at least once a month. The
ones that don't will be easy to treat separately if they are common
cases you want to factor out.
Cheers,
Malcolm
Gary Doades
The best way I have found so far is to use the rrule stuff from the
python-dateutil package.
Given a repeat interval such as DAILY, WEEKLY, MONTHLY etc. it can
generate all the dates that your event occurs on between two dates.
It will even generate for just "weekdays" or every other Monday etc.
etc. It also takes care of different sized months, leap years, etc.
All you then need to do is store the parameters to the rrule in a table
and most of the work is done.
To retrieve from the database all you need to do is find all the ones
that occur in your month of choice, feed all the rrules into an rruleset
and generate everything in one line by list'ing the rruleset.
Hope this helps,
Gary.
Merric Mercer
data in a table.
I now need to add another column to my table. However, the data in the
new column is a calculation based on two of the other fields
in that record.
I presume that this can be done using a template tag, but I can't work
out how it can be done. I can't see how I can pass the values of the
other fields into
a tag. Can anybody please advise the best way to do this.
MerMer
Jay Parlar
>
> I'm working on this very problem.
>
> The best way I have found so far is to use the rrule stuff from the
> python-dateutil package.
>
> Given a repeat interval such as DAILY, WEEKLY, MONTHLY etc. it can
> generate all the dates that your event occurs on between two dates.
>
> It will even generate for just "weekdays" or every other Monday etc.
> etc. It also takes care of different sized months, leap years, etc.
>
> All you then need to do is store the parameters to the rrule in a table
> and most of the work is done.
>
> To retrieve from the database all you need to do is find all the ones
> that occur in your month of choice, feed all the rrules into an rruleset
> and generate everything in one line by list'ing the rruleset.
>
So in your Event model (or whatever you're calling it), you're
essentially storing the keyword arguments to an rrule, depending on
what's necessary for a particular event?
Then whenever you need to render a calendar, you pull in all the
Events from the db, create rrules for each using the stored
parameters, and just work with the dateutil library to do the rest?
Jay P.
limodou
http://www.djangosnippets.org/snippets/9/
--
I like python!
UliPad <<The Python Editor>>: http://wiki.woodpecker.org.cn/moin/UliPad
My Blog: http://www.donews.net/limodou
Gary Doades
> So in your Event model (or whatever you're calling it), you're
> essentially storing the keyword arguments to an rrule, depending on
> what's necessary for a particular event?
>
> Then whenever you need to render a calendar, you pull in all the
> Events from the db, create rrules for each using the stored
> parameters, and just work with the dateutil library to do the rest?
>
> Jay P.
More or less, yes.
I have a Django model (from my original test code):
class ResourceSchedule(models.Model):
description = models.CharField(maxlength=64)
daymap = models.IntegerField()
monthmap = models.SmallIntegerField(default=4095)
period =
models.SmallIntegerField(default=0,choices=RESOURCE_SCHEDULE_PERIODS,validator_list=[validators.NumberIsInRange(0,6)])
frequency = models.SmallIntegerField(default=1)
time_from = models.TimeField(default='09:00:00')
duration = models.TimeField(default='00:30:00')
date_from = models.DateField()
date_to = models.DateField(null=True)
Which covers all the rule bits I will need.
The daymap and monthmap are used depending on what the period is (Weekly,
Monthly etc.) to store exactly wich days & months the event should repeat
in. I store these as a bitmap for minimizing data storage, but it is
easily searchable with SQL and the bitmaps can be simply expanded to lists
for display in forms (as Checkbox Lists for example). You could of course
store the days/months more explicitly in your table.
frequency is the interval part of the rule giving you every 'n' weeks for
example.
Its pretty easy to then find the ResourceSchedules that might be
candidates for having events in your datefrom/dateto period and shoving
them into and rrule/rruleset.
It might be overkill for some simple tasks but I need the flexibility.
This way I can store/generate just about any repeating pattern I care to
think of.
Regards,
Gary.
Jay Parlar
Mostly I'm cool with that, but one thing just isn't clicking yet: Your
date_from and date_to fields, I'm guessing these represent the from/to
dates of the *original* event, and aren't affected by repetitions? So
if that's the case, how do you initially pull your ResourceSchedules
out of the database? It looks to me like you'd have to pull them all
out (ie. ResourceSchedule.objects.all()), generate rrules for all of
them, and see which of the rrules fit into a desired timespan. Is that
right?
By the way, it warms my heart to see you storing bitfields. My area is
embedded devices, not web stuff, so I often spend my days working with
bitfields and masks and such. Good to see that tradition carried into
some Django :)
Jay P.
g...@gpdnet.co.uk
On Mar 8, 4:31 pm, "Jay Parlar" <par...@gmail.com> wrote:
> Mostly I'm cool with that, but one thing just isn't clicking yet: Your
> date_from and date_to fields, I'm guessing these represent the from/to
> dates of the *original* event, and aren't affected by repetitions? So
> if that's the case, how do you initially pull your ResourceSchedules
> out of the database? It looks to me like you'd have to pull them all
> out (ie. ResourceSchedule.objects.all()), generate rrules for all of
> them, and see which of the rrules fit into a desired timespan. Is that
> right?
>
> By the way, it warms my heart to see you storing bitfields. My area is
> embedded devices, not web stuff, so I often spend my days working with
> bitfields and masks and such. Good to see that tradition carried into
> some Django :)
>
> Jay P.- Hide quoted text -
>
> - Show quoted text -
Sorry, the date_from and date_to are probably superfluous for your
needs. For my system they represent the period that this schedule is
valid for and therefore the absolute extent that rrule can generate
dates for.
For my application I don't have to pull them all out. I'm writing a
booking/scheduleing system and mostly people will search for "things"
on a particular day(s) or for a particular month or between two
specific dates etc. Using the bitmaps and simple bitwise math I can
pull out just the ones that are *likely* to have dates in them.
You could always store another marker or two to give a good first line
SQL filtering depending on how you want to look for them. The idea is
to probably get a few more than you might need, but not *too many*
more! Finding all the records that fall on a monday or in the month of
May is pretty easy with the SQL, then just rrule them to find the
actual dates depending on the other repeat criteria.
Otherwise you would have to pull them all out yes. However, if you
have rules that repeat on every day or every other day you are not
likely to have very many of them for any one specific calendar.
Also I store these Schedules as a top level table. I then create
Resources (Events?) that "use" a specific schedule. This way I can
have lots of Resources that use the same Schedule, again minimising
the number of schedule records.
You can then create nice names for the schedules (in the description
field), call them "Weekly", "Monthly", "Every second Thursday" etc.
and have a pick list of common schedules to assign to events or
whatever.
Again, it's probably overkill for a simple calendar, but damn
powerful!! Just some ideas to mull over then :)
Regards,
Gary.
Jay Parlar
Ahh, ok, that makes sense now. It might be overkill for what I'm
doing, but I can just see the requirements for this continually
increasing. Having the structure already in place to do whatever I
want would be a good thing :)
Thanks,
Jay P.
Jeremy Dunck
> Also I store these Schedules as a top level table. I then create
> Resources (Events?) that "use" a specific schedule. This way I can
> have lots of Resources that use the same Schedule, again minimising
> the number of schedule records.
It seems we all have this problem. :)
I was thinking about doing something similar to what Gary's doing, but
I'm more concerned with fast access than minimal records. Also,
there's a "reasonable" window of time for which we'll ever have
records, say 10 years.
As such, I was thinking about having an Event model with recurrence
rules (perhaps admin'd with a little DSL), then blowing out a lot of
EventTime records for all the occurrences. Editing would always be to
Events; EventTimes would be regenerated upon Event save.
The reduces queries to a simple date range, allowing very efficient index usage.
Gary Doades
product.
The big upside is that you don't have to store exceptions if you remove
just one date in the future from the recurrence pattern, you just remove
that one date record from the database.
There can be a big downside depending on what you are using it for. I'm
using this sort of model for a "Resource Availability" kind of thing.
Bookings/reservations etc. can be made against the available resource
occurrences. If you want to change the schedule, you have to delete,
re-insert or at least update all the actual records in the database,
possibly all of these. If you have bookings against the original pattern
then what to do with those existing bookings becomes a problem.
You also have to consider storage. Several 10 of millions of explicitly
stored dates might become a problem :)
Basically the problem needs to be thought through from beginning to end.
Not only how you are going to query the records, but also how you might
update them together with any (possible) dependent records.
Bottom line options:
Store minimal data (rules) and dynamically regenerate actual dates on query.
or
Store actual absolute dates (possibly with the original generation rule)
and work out how to update them.
Storage and efficient query as opposed to raw cpu power perhaps?
It's not always easy.....
Regards,
Gary.