usage of group_by and string_agg in django, postgreSQL

usage of group_by and string_agg in django, postgreSQL

In this article we are going to learn how to optimize the queries in Django applications. Django has a built-in ORM which converts the django query into optimized SQL query. In some cases we need to group the data based on some common django field or a table column. This can also be done without using the group_by and string_agg in django query. In such cases we need to query for all matching records and then form the groups based on the fields. In this case we need to query first and then process the results. It will cost more time (i.e: SQL query time + Data Processing time). If we use group_by and string_agg in django with postgreSQL database, to optimize the query time and get the desired data format.

Let's consider the below models

from django.db import models

class Customer(models.Model):
    first_name = models.CharField(max_length=50)
    last_name = models.CharField(max_length=50)

class Address(models.Model):
    customer = models.ForeignKey(Customer, on_delete=models.CASCADE)
    phone = models.CharField(max_length=15)
    line = models.CharField(max_length=50)
    city = models.CharField(max_length=50)

Now, consider the below data tables

Table: Customer


Table: Address

11987654321sri nagarHyderabad
22123654789sai nagarBangalore
31987456321prakash nagarMedak

Desired Data Format:

1Anji987654321, 987456321, 321456987


To get data format like above table we need use group_by and string_agg in django with postgreSQL database. Let's see the below django query

from django.db.models import F
from django.contrib.postgres.aggregates.general import StringAgg

queryset = Address.objects.values('customer_id').order_by('customer_id').annotate(
    phone_numbers=StringAgg('phone', delimiter=','),
# Output: <QuerySet [{'phone_numbers': '987654321,987456321,321456987',
# 'customer_name': 'Anji', 'customer_id': 1},
# {'phone_numbers': '123654789', 'customer_name': 'Jessi',
# 'customer_id': 2}]>


Try above code to test it practically in your project.


Read Next: how to use templates in django

Read Prev: getting started with Django Rest Framework

Blog Archive