CODEWITHSUNDEEP
djangodjango-querysetdjango-orm
Sandeep Balagopal
Sandeep Balagopal

Reputation: 1983

django filter queryset based on list of dates and get the count on each day

I have a model like this

class MyModel(models.Model):
  date = models.DatetimeField(auto_now_add=True)

and i have a list of dates (dates of last seven days, it can be dates of last 30 days as well). What i am trying to do is get the number of MyModel objects created in a particular day in all of the dates. Like below.

[{'date1': 2}, {'date2': 3}, {'date3': 7}....{'daten':26}]

the keys of the dictoary are dates and values are the number of objects created in that particular date. by using a forloop and hitting db multiple times i am getting the output, but is there a way to get it from a single query ?

Upvotes: 2

Views: 1888

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 477676

You can make a query that counts the number of objects for each date:

from datetime import timedelta
from django.db.models import Count, F
from django.utils.timezone import now

qs = MyModel.objects.filter(
    date__date__gte=now().date()-timedelta(days=30)
).values(
    datedate=F('date__date')
).annotate(
    number=Count('pk')
).order_by('datedate')

Next we can post-process this to a list of dictionaries, or perhaps better a single dictionary that maps the data on the count:

# list of dictionaries
[{r['datedate']: r['number']} for r in qs]

# single dictionary
{r['datedate']: r['number'] for r in qs}

If the date is not present, it will not be in the dictionary. So dates that are not in the dictionary have count 0. This is due to the closed world assumption (CWA).

Upvotes: 3

Related Questions