Django | Get count of articles per month

Question

I got two models Article and Author implemented like this:

class Author(models.Model):
    name = models.CharField(max_length=50)

class Article(models.Model):
    name = models.CharField(max_length=50)
    author = models.ForeignKey(Author, on_delete=models.CASCADE)
    pub_date = models.DateField()

On my template I want to plot a graph (using chart.js on the frontend side) showing the authors publications per month, for the last 12 months. For that I need the count of articles published each month.

This is the query to get the authors articles:

articles = Article.objects.filter(author=author)

What would be best practice to get the count per month?

I've thought about annotating each of the twelve month separately to the QuerySet, but haven't found a way that works for me.
Alternatively, I thought about dealing with this on the JS site in the users browser.

Any suggestions/recommendations?

score 1 · Accepted Answer · answered May 31 '22 at 18:10

If you need to group data in Django you'll need to use ORM aggregation features.

And to use that on dates you can leverage their ORM helper functions.

from django.db.models.functions import TruncMonth
from django.db.models import Count

Article.objects
    .filter(author=author)                 # Filter by the author   
    .annotate(month=TruncMonth('created')) # Truncate by month and add 'month' to values
    .values('month')                       # Group By month
    .annotate(count_id=Count('id'))        # Count the number of articles in the grouping
    .order_by('-month')[:12]               # Sort by months in descending order (latest to earliest) and get the 12 first results

Django | Get count of articles per month

What would be best practice to get the count per month?

1 Answers1