Date
Jul. 19th, 2024
 
2024年 6月 14日

Post: Combining QuerySets in Django

Combining QuerySets in Django

Published 11:04 Apr 08, 2020.

Created by @ezra. Categorized in #Programming, and tagged as #Django, #Python.

Source format: HTML

Table of Content

QuerySet the Django Way

Django's documentation is pretty straightforward about the QuerySet API. It states:

Internally, a QuerySet can be constructed, filtered, sliced, and generally passed around without actually hitting the database. No database activity actually occurs until you do something to evaluate the queryset. 1

In other words, it is recommended to make queries and manipulate data using QuerySets rather than using raw queries. Here's why:

Performance

Hitting the database only at the moment of evaluation allows for the performance of a great number of operations like slicing and filtering. This increases the flexibility and performance of the potential query, in contrast to raw queries, which hit the database each time you call.

Convenience

It's easier to filter, slice, iterate, and manipulate data via QuerySet because you can generate a list of QuerySets that provide several methods for making manipulation easier. In contrast, with raw queries you have to deal with a very rigid structure that requires extra work to deliver the same result.

Security

Using raw queries requires caution because each time there is a need to avoid any parameters the user controls to protect the query from SQL injection.

Sometimes there is a need to combine multiple QuerySet results into one to group and iterate them in a list. There are two possible situations:

If the QuerySet Operates Under the Same Model

It is recommended to use pipes and the | operator to make the merge operation.

Consider the following models:

class Group(models.Model):
 code = models.CharField(max_length=80, unique=True)
 name = models.CharField(max_length=128)
 users = models.ManyToManyField(User, related_name='groups')
 roles = models.ManyToManyField(Role, related_name='groups', blank=True)
 products = models.ManyToManyField(Product, related_name='groups', blank=True)
class Client(models.Model):
 name = models.CharField(max_length=30, unique=True)
 groups = models.ManyToManyField(Group)

Let's say you want to display all the groups for a specific client, together with the group that belongs to a user, using the product name CMS A. Note that this User might have different groups.

client = Client.objects.get(name='Django Client Name')
user = User.objects.get(username='cdiaz')
django_groups = client.groups.all()
cdiaz_groups = user.client.filter(products__name='CMS A')

At this point we have two different QuerySets, one containing all client groups and another containing all the user groups with the product name.

groups = django_groups | cdiaz_groups # merge querysets

You actually can look into the SQL query that Django generates just by using the query attribute. The full command for the groups case is print(groups.query). Django automatically combines both queries (django_groups and cdiaz_groups) into a single SQL query.

The process is very straightforward, but take into account that this will only work on QuerySets from the same model and before slicing.

If the QuerySets Operate Under Different Models

Let’s say that we have three different models (Post, Article, Page) that we want to combine into one in order to do some operations like filtering and sorting. One approach would be using itertools chain to achieve this:

from itertools import chain
def get_all_documents():
 articles = Article.objects.all()
 posts = Post.objects.all()
 pages = Page.objects.all()
 return list(chain(pages, articles, posts))

Now it's possible to sort the resulting list (e.g., by date) using the sorted() function and making some little tweaks.

from itertools import chain
def get_all_documents_sorted():
 articles = Article.objects.all()
 posts = Post.objects.all()
 pages = Page.objects.all()
 result_list = sorted(
 chain(pages, articles, posts),
 key=attrgetter('date_created'), reverse=True)
 return result_list

In this way, the list can be obtained in reverse order. It is important to note that Django executes a different SQL query for each set of three in this case, but they are not executed until the sorted function iterates over each one of them.

Pinned Message
HOTODOGO
I'm looking for a SOFTWARE PROJECT DIRECTOR / SOFTWARE R&D DIRECTOR position in a fresh and dynamic company. I would like to gain the right experience and extend my skills while working in great teams and big projects.
Feel free to contact me.
For more information, please view online résumé or download PDF
本人正在寻求任职 软件项目经理 / 软件技术经理 岗位的机会, 希望加⼊某个新鲜⽽充满活⼒的公司。
如有意向请随时 与我联系
更多信息请 查阅在线简历下载 PDF