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?!
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:
- Create exemplary
- Try Django ORM to get list of articles.
- Try to summarize data out of list.
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.
TruncDate is all about? Simple
TruncDate handles very one task,
django documentation says:
Truncates a date up to a significant component.
articles.annotate(date=TruncDate('created')) adds new date column that comes from
.values('date').order_by('-date') we get list of dates ordered by most recants first.
.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
I hope you get something amazing to apply to your next Django project. See you later.