Search
  • Team FastApp

Django Queryset with an unusual operation "Merge"

Consider, we have a StudentHistory model defined as:

class StudentHistory(models.Model):
    first_name = models.CharField(max_length=30, blank=True)
    last_name = models.CharField(max_length=150, blank=True)
    cell_phone = models.CharField(max_length=30, blank=True)
    email = models.EmailField(max_length=255, unique=True)
    marks = models.FloatField(null=True, blank=True)

We can write two querysets on this model:

queryset1 = StudentHistory.objects.filter(
    first_name__startswith="abc"
)
queryset2 = StudentHistory.objects.filter(
    email__endswith=".com"
)

Let's learn a bit about the Django queryset merging. We have three possible ways for merging the queryset.


1. If the querysets are of same model then we can use "|" (pipe) operator for merging

querysets:

merged_queryset = queryset1 | queryset2

# Or we can write

merged_queryset = (queryset1 | queryset2).distinct() 

We can still perform operation on the merged queryset:

merged_queryset.distinct().order_by('first_name')
# Or any other queryset operation for that matter.

Limitation: With | operator, we can only merge querysets of same model.



2. If the querysets are of different models, we can use "chain()" built-in

function.

from itertools import chain
merged_queryset = list(chain(queryset1, queryset2))

We also can sort the below merged_queryset on first_name using attrgetter function

from operator import attrgetter

merged_queryset = sorted(
    chain(queryset1, queryset2),
    key=attrgetter('first_name'),
    reverse=True
)

Limitation: Chain function returns a list of objects instead of an queryset object. This can be problematic when the results are large in numbers and their pagination is needed.



3. Django queryset have in-built method "union()":

Union method can be used for merging querysets of same or different models.

Case1: If the querysets are of same model

Consider the StudentHistory model defined earlier in the post for below examples:

# will contain only distinct records from both the querysets
merged_queryset = queryset1.union(queryset2) 

# will contain all the records from both the querysets, including duplicates
merged_queryset = queryset1.union(queryset2, all=True)

# will contain only distinct records from both the querysets.
merged_queryset = queryset1.union(queryset2, all=False) 

Case 2: If the querysets are of different models -

Now along with the already defined model StudentHistory we define another model Teacher below for this case:

class Teacher(models.Model):
    first_name = models.CharField(max_length=30, blank=True)
    last_name = models.CharField(max_length=150, blank=True)
    email = models.EmailField(max_length=255, unique=True)

Note - When models are different, union() function needs same named fields from the related model's querysets.

Example:

queryset1 = StudentHistory.objects.filter(
    first_name__startswith="abc"
).values_list('first_name', 'last_name', 'email')
queryset2 = Teacher.objects.filter(
    email__endswith=".com"
).values_list('first_name', 'last_name', 'email')

Thus we fetch only 3 same named fields i.e first_name, last_name, email. We can now perform union function on querysets:

merged_queryset = queryset1.union(queryset2)


92 views0 comments

Recent Posts

See All

Django Queries Simplified [Part - 1]

This blog post aims to make django querying simple for the developers who are new in the django world. Keep reading :) What is a django query? A django query is an expression which can be used to writ

Python3.6 - What the f-string !

In Python 3.6, there is a new way to format the strings, which helps the user in faster coding, less googling, and better readable code. Which was previously a little tedious by using %-format or even