CODEWITHSUNDEEP
pythondjangodjango-querysetannotate
damon
damon

Reputation: 8467

How do I get the number of posts on each day with annotation in Django?

I have a Post model that has a datetime.date field for posted_date. I need to find how many posts are made by a user on each day. Creating a query for each day seems silly.

I couldn't figure out how to make the aggregation API query using annotate.

Is

Post.objects.filter(author=someuser).annotate(dailycount=Count('posted_day'))

the correct way? Then, how do I use this to get number of posts for a particular day?

My model is:

class Post(models.Model):
    posted_day=models.DateField(default=date.today)
    author=models.ForeignKey(User,null=True)

Upvotes: 1

Views: 309

Answers (1)

agf
agf

Reputation: 176780

You are almost there. You need two additional clauses:

day_counts = Post.objects.filter(author=someuser).values('posted_day').annotate(
                                       dailycount=Count('posted_day')).order_by()

The values('posted_day') enables the grouping, and the empty order_by ensures the results are ordered by posted_day so the default ordering doesn't interfere.

The clearest documentation of this seems to be in the Django Aggregation docs section on the Order of annotate() and values() clauses.

values returns a list of dicts like: 

[{'posted-day': 'the-first-day', 'dailycount': 2}, . . . ,
 {'posted-day': 'the-last-day', 'dailycount': 3}]

so if you wanted the last day the user posted, it would be the last item in the list:

last_day_dict = day_counts[-1]
date = last_day_dict['posted_day']
count = last_day_dict['dailycount']

You can then compare date to today() to see if they match, and if they don't the user didn't post today, and if he did, he posted count times.

Upvotes: 1

Related Questions