Get objects created in last 30 days, for each past day

Question

I am looking for fast method to count model's objects created within past 30 days, for each day separately. For example:

27.07.2013 (today) - 3 objects created
26.07.2013 - 0 objects created
25.07.2013 - 2 objects created
...
27.06.2013 - 1 objects created

I am going to use this data in google charts API. Have you any idea how to get this data efficiently?

Answer 1

items = Foo.objects.filter(createdate__lte=datetime.datetime.today(), createdate__gt=datetime.datetime.today()-datetime.timedelta(days=30)).\
    values('createdate').annotate(count=Count('id'))

This will (1) filter results to contain the last 30 days, (2) select just the createdate field and (3) count the id's, grouping by all selected fields (i.e. createdate). This will return a list of dictionaries of the format:

[
    {'createdate': <datetime.date object>, 'count': <int>},
    {'createdate': <datetime.date object>, 'count': <int>},
    ...
]

EDIT:

I don't believe there's a way to get all dates, even those with count == 0, with just SQL. You'll have to insert each missing date through python code, e.g.:

import datetime

# needed to use .append() later on
items = list(items)

dates = [x.get('createdate') for x in items]

for d in (datetime.datetime.today() - datetime.timedelta(days=x) for x in range(0,30)):
    if d not in dates:
        items.append({'createdate': d, 'count': 0})