Django aggregation on a date range

Question

Hi all, I have been lurking and learning in here for a while. Now i have a problem that somehow i cannot see an easy solution. In order to learn django i am bulding an app that basically keeps track of booked items. What I would like to do is to show how many days per month for a selected year one item has been booked.

i have the following models:

Asset(Model)

BookedAsset(Model):
 asset = models.ForeignKey(Asset)
 startdate = models.DateField()
 enddate = models.DateField()

So having the following entries:

asset 1, 2010-02-11, 2010-02-13
asset 2, 2010-03-12, 2010-03-14
asset 1, 2010-04-30, 2010-05-01

I would like to get returned the following:

asset 1    asset 2
-------    -------
Jan = 0    Jan = 0
Feb = 2    Feb = 0
Mar = 0    Mar = 2
Apr = 1    Apr = 0
May = 1    May = 0
Jun = 0    Jun = 0
Jul = 0    Jul = 0
Aug = 0    Aug = 0
Sep = 0    Sep = 0
Oct = 0    Oct = 0
Nov = 0    Nov = 0
Dec = 0    Dec = 0

I know i need to first get the number of days in a date range (and keep track if they fall out of the current month and into the next month) and then do an agregate on the number of days. I am just stuck on how to do it elegantly in Django.

Any help (or hint in the right direction) is greatly appreciated.

Answer 1

I can't think of a way of doing it with the model structure you have.

This is a fairly complex requirement, and however you solve it is probably going to require quite a lot of custom SQL. I think as a start though you might need to consider changing your structure so that you have a BookedAssetDay table, which represents each day separately for a booking.

class BookedAsset(models.Model):
    asset = models.ForeignKey(Asset)
    day = models.DateField()

Then the query looks something like:

BookedAsset.objects.extra(
    select={'month': 'MONTH(day)'}
).values('asset', 'month').annotate(Count('bookedasset__month'))

Answer 2

At the end i went with a custom query as a solution to my question:

cursor = connection.cursor()
cursor.execute("""select to_char(allmonths.yeardate::date, 'YYYY/MM/DD') as monthdate,
                    COUNT("day") as bookings
                    from (
                    select distinct date_trunc('month', (date %s - offs)) as yeardate 
                    from generate_series(0,365,28) as offs
                    ) as allmonths
                    left join bookings_bookedassetday 
                    on EXTRACT(MONTH from "day")=EXTRACT(MONTH from yeardate) 
                    and asset_id=%s and EXTRACT(YEAR from "day") = %s
                    group by allmonths.yeardate
                    order by allmonths.yeardate asc""", [year+'-12-31', id, year])

query = cursor.fetchall()

Maybe it is not the most django-ist way of doing it but i found it way easier than figuring it out with pure django :|

If somebody else has a better alternative i am all ears :)

Answer 3

I wouldn't go for either answer above (even do I like punching out some custom SQL but I haven't done that in at least 5 years) and your model is simple enough to be workable in a relational paradigm.

The answer you're looking for is is the annotate() function in django's aggregation functionality: http://docs.djangoproject.com/en/dev/topics/db/aggregation/ Only thing you need to know is how to get at the month for the date, which is explained in the queryset documentation: http://docs.djangoproject.com/en/dev/ref/models/querysets/#month

A rough example to get it for one asset:

BookedAsset.objects.filter(asset=asset).annotate(month_count=Count('startdate__month')).order_by('startdate__month')

(obviously wrong, but I couldn't be arsed to recreate your structure to give you the exact correct statement, fiddle around a bit and read the documentation)

You could probably even though it in one go for all assets by using the join syntax in the same docs.