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

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

Support Me on Patreon