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

idfirst_namelast_name
1AnjiB
2JessiS
3RohanC

Table: Address

idcustomer_idphonelinecity
11987654321sri nagarHyderabad
22123654789sai nagarBangalore
31987456321prakash nagarMedak
41321456987JoshenbegRangareddy

Desired Data Format:

customer_idcustomer_namephone_numbers
1Anji987654321, 987456321, 321456987
2Jessi123654789

 

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

Read Next: how to use templates in django

Read Prev: getting started with Django Rest Framework

Blog Archive