Include 0 values when using values() and Count() in django

Question

I was not quite sure how to word the question.

I am trying to make a method in django that queries up objects based on the date they were created at. I plan to use this data to generate a graph.

So far, I am using this:

query.extra(select={"day": "DATE(created_at)"}).values('day').annotate(models.Count("id"))

That works perfectly given that there is at least 1 per day. Without that 1 in each day, it skips the day in my graph.

I would like to avoid looping day by day if possible. This method goes over quite a few days.

Is there an easy way to modify this to include when there are 0 objects in a given day? What would be the best way to go about this?

Answer 1

I would recomend to use only django queries to obtain the data from the database, and then you can process it.

So, I would something like this:

import datetime

def daterange(start_date, end_date):
    """ 
    Generate an iterator of dates between the two given dates. 
    taken from http://stackoverflow.com/questions/1060279/
    """ 
    for n in range(int ((end_date - start_date).days)):
        yield start_date + datetime.timedelta(n)

def get_graph_data(start_date, end_date):
    query = MyModel.objects.all().annotate(models.Count("id"))
    data = query.values('created_at', 'id__count')

    # go through all the dates between start_date and end_date
    # and fill the missing gaps with 0 
    return dict((d, data[d] if d in data else 0) for d in daterange(t, to))        

Answer 2

To me this sounds more like a problem with the graph rather than the a problem on a query level. However if you really want to solve this on a query level you need to do something similar with the answer given here generate days from date range. It will be ugly yes. Maybe solve this on a python level? More on the python solution here Generate a list of datetimes between an interval in python