Get objects created in the last 30 days, for every last day

Question

I am looking for a quick method of counting model objects created in the last 30 days, for each day separately. For instance:

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 the Google Charts API. Do you know how to use this data effectively?

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 (1) filtering results will contain the last 30 days, (2) select only the created field and (3) count the identifier by grouping all selected fields (i.e. create). This will return a list of format dictionaries:

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

EDIT:

I do not believe that there is a way to get all dates, even those with count == 0 , only with SQL. You will need to insert each missing date through python code, for example:

 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}) 

Answer 2

I think this may be a slightly more optimized solution with the @knbk solution with python. This has fewer iterations, and the iterations inside SET highly optimized in python (both in processing and in CPU cycles).

 from_date = datetime.date.today() - datetime.timedelta(days=7) orders = Order.objects.filter(created_at=from_date, dealer__executive__branch__user=user) orders = orders.annotate(count=Count('id')).values('created_at').order_by('created_at') if len(orders) < 7: orders_list = list(orders) dates = set([(datetime.date.today() - datetime.timedelta(days=i)) for i in range(6)]) order_set = set([ord['created_at'] for ord in orders]) for dt in (order_set - dates): orders_list.append({'created_at': dt, 'count': 0}) orders_list = sorted(orders_list, key=lambda item: item['created_at']) else: orders_list = orders