李二狗 — @Meniny
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.
