TruncDate in Django ORM

Introduction

If you have been programming web application in Django probably you really enjoy Django ORM. It’s has advanced capabilities to construct complex SQL queries to any database. Let’s discover some of them. Ready?!

Overview

Today, we are going to learn how to use TruncDate in conjunction with annotation and ordering in Django ORM.

Steps are as simple as following:

1. Create exemplary Article model.
2. Try Django ORM to get list of articles.
3. Try to summarize data out of list.

Create exemplary Article model

from django.db import models

class Article(models.Model):
    title = model.CharField(max_length=128)
    body = models.TextField()
    author = models.ForeignKey(User)
    tags = models.ManyToMany(Tag)
    published = models.BooleanField(dafault=False)

    created = models.DateTimeField(auto_now=True, auto_now_add=True)
    updated = models.DateTimeField(auto_now=True)

    def __str__(self):
        return self.title

    class Meta:
        ordering = ('-created', )

Try Django ORM to get list of articles

To get all Articles from database

articles = Article.objects.all()

To get all published Articles

published_articles = Article.objects.filter(published=True)

To get all published Articles alongside with list of each article tags

published_articles = Article.objects.filter(published=True).select_related('tags')

Let’s be more ambitious something like all articles published today

from django.utils import timezone

today = timenoze.today()
published_articles = Article.objects.filter(published=True, created__date=today)

Try to summarize data out of list

Let us imagine, we want to have info about frequency of published articles in nice tabular format.

Number of published articles in days
22 Feb 2018 :  7 articles
21 Feb 2018 :  4 articles
10 Feb 2018 :  8 articles
28 Jan 2018 : 18 articles
12 Jan 2018 :  8 articles
22 Dec 2017 :  2 articles
22 Nov 2017 :  5 articles

We can do this in a single shot of database query like this:

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

articles = Article.objects.filter(published=True)

bundle = (articles.annotate(date=TruncDate('created'))
                  .values('date')
                  .order_by('-date')
                  .annotate(count=Count('id')))

Let’s understand bit by bit. What is TruncDate is all about? Simple

TruncDate handles very one task, django documentation says:

Truncates a date up to a significant component.

So articles.annotate(date=TruncDate('created')) adds new date column that comes from TruncDate.

After .values('date').order_by('-date') we get list of dates ordered by most recants first.

Lastly .annotate(count=Count('id')) counts exact the same dates in the list and saves in count That’s it.

And then you can render it in Django templates

Number of published articles in days

Conclusion

I hope you get something amazing to apply to your next Django project. See you later.