group by query in Django ORM

Django ORM is very strong and popular ORM itself. Sometimes we can not figure out the way how we can write a sql query using django ORM similar to a simple raw sql query. For example, a group by query is a simple query. But in django, we can not apply group_by() on a queryset as we can do in many ORMs. django ORM does not have a method group_by(). In this short article, we will see that how we can write group by query using django ORM.

 

Here is the django model used in the example to make it more clear. Two database tables are used in the example.

  1. SessionDetail – contains a class/session details
  2. SessionSchedule – contains the class/session timings. There will be one-to-many relationship between sessionDetail and sessionSchedule.
class SessionDetail(models.Model):

   slug = models.SlugField(blank=True)
   title = models.CharField(_('Title'), max_length=250)
   description = models.TextField()
   outline = models.TextField()

   def __str__(self):
       return '{} - {}'.format(self.id, self.title)

 

#this table contains session's additional information like session timing.
class SessionSchedule(models.Model):
   sessions = models.ForeignKey(SessionDetail, on_delete=models.PROTECT)
   slug = models.SlugField(default=None, blank=True)
   session_date = models.DateField(default=None)
   start_time = models.TimeField(default=None)
   end_time = models.TimeField(default=None)
  
   def __str__(self):
       return '{} - {}'.format(self.id, self.sessions.title)

Now if we want to retrieve all the sessions which have a schedule at any specific day. Then we can do this like:

SessionDetail.objects.filter(sessionschedule__session_date=sessionDate)

Now if we want to group the records by sessionDetails.id then we have to use annotate() clause.

 

Import count from models

from django.db.models import Count

queryset = SessionDetail.objects.filter(sessionschedule__session_date=sessionDate)
queryset = queryset.annotate(SessionDetail__id=Count('id')).all()

Output from the above query will be same as the output from the query below:

Query: select sessiondetail.id, sessiondetail.title,sessiondetail.slug,sessiondetail.description,sessiondetail.outline
from sessiondetail
left join sessionschedule ON sessiondetail.id=sessionschedule.sessions
where sessionschedule.session_date=sessionDate
group by sessiondetail.id

 

The annotate() clause returns a queryset. We can further modify this queryset by using any other queryset operation like filter(), order_by() or even additional calls to annotate() clause.

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to Top
Shares