Creating queries in django can go from simple to complex. Queries can be calculated in two places;
- Python side and
- Database side.
Writing queries from the python side always seem like the easiest thing to do but in reality, it reduces the performance of our application. So if the performance of your application means anything to you, you should read this article to the end without missing a thing as it will help you write django queries like a pro.
Query expressions describe a value or a computation that can be used as part of an update, create, filter, order by, annotation, or aggregate. When an expression outputs a boolean value, it may be used directly in filters. There are a number of built-in expressions (documented below) that can be used to help you write queries. Expressions can be combined, or in some cases nested, to form more complex computations.
Django supports negation, addition, subtraction, multiplication, division, modulo arithmetic, and the power operator on query expressions, using Python constants, variables, and even other expressions.
Going forward, I assume you're already familiar with the basics of django.
# app. models
class Company(models.Model):
name = models.CharField(max_length=300)
num_employees = models.IntegerField(default=0)
num_chairs = models.IntegerField(default=0)
is_registered = models.BooleanField(default=False)
class Meta:
verbose_name_plural = "Companies"
db_table = "Company"
indexes = [
models.Index(fields=["name", "is_registered"])
]
constraints = [
models.CheckConstraint(check=models.Q(num_employees__gte=0), name="num_employees_gte_0"),
models.CheckConstraint(check=models.Q(num_chairs__gte=0), name="num_chairs_gte_0")
]
def __str__(self) -> str:
return self.name
class Product(models.Model):
class Meta:
db_table = "Product"
indexes = [
models.Index(fields=["name", "price", "company"])
]
constraints = [
models.CheckConstraint(check=models.Q(price__gte=0), name="price_gte_0")
]
name = models.CharField(max_length=100)
company = models.ForeignKey(Company, on_delete=models.CASCADE)
price = models.DecimalField(max_digits=10, decimal_places=2)
def __str__(self) -> str:
return self.name
The F() query expression is used to query objects based on a model's field or its ForeignKey. It can be used to update model objects, filter queries if it returns a boolean value, etc. It makes it possible to refer to model field values and perform database operations using them without actually having to pull them out of the database into Python memory.
F() therefore can offer performance advantages by:
- getting the database, rather than Python, to do work
- reducing the number of queries some operations require
Some examples
# Find companies that have more employees than chairs.
Company.objects.filter(num_employees__gt=F("num_chairs"))
In the above event, the Company was filtered based on its field(num_chairs).
companies = Company.objects.filter(num_employees__lt=F("num_chairs")).annotate(workers_needed=F("num_chairs") - F("num_employees"))
The above query filters the Company object to those whose employees are less than the chairs they have in which case they want to get the number of seats to be filled. If this were to be done without the F() expression, that'd be a lot of computations.
Say the company wants to increase the prices for all its products. An easy way to do that will be;
Product.objects.update(price= F("price") + 150)
Which is better than
for product in Product.objects.all():
product. price += 150
product. save()
Updating a single object
product = Product.objects.first()
product.price = F("price") + 150
product.save()
F() assignments persist after Model.save()
F() objects assigned to model fields persist after saving the model instance and will be applied on each save(). For example:
product = Product.objects.first()
product.price = F("price") + 150
product. save()
product.name = "New name"
product.save()
The product's price will be updated twice in this case. If it’s initially 50, the final value will be 450. This persistence can be avoided by reloading the model object after saving it, for example, by using refresh_from_db().
product = Product.objects.first()
product.price = F("price") + 150
product. save()
product.refresh_from_db()
product.name = "New name"
product.save()
Using F() with logical operations
This happens to be my favorite part as it allows you to toggle boolean values.
company = Company.objects.first()
company.is_registered = ~F("is_registered")
company.save()
company. refresh_from_db()
If the value of is_registered is True, it changes it to False and vice versa.
That would be all for this part, see you on the next.
Top comments (2)
Now I know the usefulness of the F() expression
That's good to hear.