Serialize Queryset - Group by date

Question

I've got a model that looks something like:

class Deal(models.Model):
    sender = models.ForeignKey(settings.AUTH_USER_MODEL)
    created_on = models.DateField(auto_now_add=True)
    recipient = modes.ForeignKey(settings.AUTH_USER_MODEL)
    ...

I'd like to get the daily count of deals sent or received by a particular user for the three month window ending today. To further complicate things, I want that dataset serialized in JSON format so that I have output like this:

{
    "timestamp_1": count_1,
    "timestamp_2": count_2,
    ...
    "timestamp_n": count_n
}

So far I have arrived at a query that'll look something like this:

# Assume we have a user object called "user"
Deal.objects.filter(Q(sender=user) | Q(recipient=user)).extra({'date_created' : "date(created_on)"}).values('date_created').annotate(created_count=Count('id'))

Still combing through the documentation, but does anyone have a better way to tackle this?

Answer 1

I don't think Django can handle this without RAW sqls. There's a great django third-party package, django-qsstats-magic which is designed to make repetitive tasks such as generating aggregate statistics of querysets over time easier. It supports postgresql, Mysql, and sqlite.

The code below solves you problem.

import qsstats
import datetime

Deal.objects.filter(Q(sender=user) | Q(recipient=user))

qss = qsstats.QuerySetStats(qs, date_field='date_created', aggregate=Count('id'))

three_month_ago = datetime.datetime.now() - datetime.timedelta(months=3)
qss.time_series(start=three_month_ago, interval='days')