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

id	first_name	last_name
1	Anji	B
2	Jessi	S
3	Rohan	C

Table: Address

id	customer_id	phone	line	city
1	1	987654321	sri nagar	Hyderabad
2	2	123654789	sai nagar	Bangalore
3	1	987456321	prakash nagar	Medak
4	1	321456987	Joshenbeg	Rangareddy

Desired Data Format:

customer_id	customer_name	phone_numbers
1	Anji	987654321, 987456321, 321456987
2	Jessi	123654789

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=','),
    customer_name=F('customer__first_name')
)
print(queryset)
# 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.

References:
https://docs.djangoproject.com/en/dev/_modules/django/contrib/postgres/aggregates/general/#StringAgg

Related Blog Articles

Django Create Table From Model

Django made creating the tables in the database very simple with its built-in ORM. To create table in the database with django is to create a django model with all required fields and then create migrations and apply them. Let's jump-in learn how to create database table with django model.

Django Model Crud Operations

Let's do CRUD operations on django model. In the previous article we have seen how to create a table or model in the django application. Let's write basic queries like insert, update and delete queries using django ORM.