Query Expressions in Django

Query expressions in Django allow you to create complex database queries by combining database fields, operators, and functions. They are an essential part of building efficient and flexible database queries. In this article, we will look at various types of query expressions in Django with the help of a Python project.

Types of Query Expressions in Django

• Aggregating Data
• Performing Calculations
• Updating Records
• Filtering data

Aggregating Data

Suppose we want to calculate the total value of all products in our database. We can use the Sum query expression for this purpose. Here, we use the Sum expression to calculate the sum of the ‘price’ field across all Product objects. The result is stored in the ‘total_value’ dictionary.

Python3

from django.db.models import Sum

total_value = Product.objects.aggregate(total_value=Sum('price'))
print(total_value['total_value'])

Performing Calculations

Let’s say we want to retrieve a list of products with their total value (price multiplied by quantity). We can use the F() expression for this calculation. The annotated method allows us to add a calculated field to each object in the query set. In this case, we calculate the total value using the F() expression, which references the ‘price’ and ‘quantity’ fields.

Python3

from django.db.models import F

products_with_total_value = Product.objects.annotate(total_value=F('price') * F('quantity'))
for product in products_with_total_value:
    print(f"{product.name}: Total Value - {product.total_value}")

Updating Records

You can also use query expressions to update records efficiently. For instance, let’s say we want to increase the price of all products by 10%. Here, we use the update method along with the F() expression to multiply the ‘price’ field by 1.1, effectively increasing the price by 10%.

Python3

from django.db.models import F

Product.objects.update(price=F('price') * 1.1)

Filtering Data

Query expressions can also be used in filters to retrieve specific records. For example, let’s fetch products with a price greater than $50. In this query, we filter products where the ‘price’ is greater than 10 times the ‘quantity’ using the F() expression.

Python3

from django.db.models import F

expensive_products = Product.objects.filter(price__gt=F('quantity') * 10)

Setting up the Project

Starting the project

To install django follow these steps.

Command to start a project in Django

django-admin startproject queryexpressionsproject
cd my_app

Command to start the app

python3 manage.py startapp my_app

Now add this app to the ‘settings.py’

Setting up the files

model.py

You can use Django’s ORM to create, retrieve, update, and delete Product objects in your database. You can also use Django’s admin interface to manage these objects.

Python3

from django.db import models

class Product(models.Model):
    name = models.CharField(max_length=100)
    price = models.DecimalField(max_digits=10, decimal_places=2)
    quantity = models.PositiveIntegerField()

    def __str__(self):
        return self.name

views.py

In summary, this code defines two Django view functions. The home view returns a simple “Hello, Falcon World!” message, likely intended for a homepage. The expensive_products view queries the database to find expensive products, calculates their total values, and renders an HTML template with the filtered products as context data, presumably for displaying a list of expensive products to the user.

Python3

from .models import Product
from django.http import HttpResponse
from django.shortcuts import redirect, render
from django.db import models


def home(request):
    return HttpResponse('Hello, Falcon World!')


def expensive_products(request):
    # Calculate the total value and filter for expensive products
    expensive_products = Product.objects.annotate(
        total_value=models.ExpressionWrapper(
            models.F('price') * models.F('quantity'),
          output_field=models.DecimalField())
    ).filter(total_value__gt=1000)

    return render(request, 'myapp/index.html',
                  {'expensive_products': expensive_products})

Note: The F() expression allows you to reference database fields within your queries, making it possible to perform operations on database fields without retrieving the data into Python memory.

index.html

So, in summary, this HTML template is designed to display a list of expensive products with their names and total values.

HTML

<!DOCTYPE html>
<html>
<head>
    <title>Expensive Products</title>
</head>
<body>
    <h1>Expensive Products</h1>
    <ul>
        {% for product in expensive_products %}
            <li>{{ product.name }} - Total Value: ${{ product.total_value }}</li>
        {% endfor %}
    </ul>
</body>
</html>

urls.py

In Django, the urls.py file is used to map URLs to views or functions that handle HTTP requests and generate responses.

Python3

from django.urls import path
from . import views

urlpatterns = [
    path('hello/', views.home, name='home'),
    path('expensive-products/', views.expensive_products,
         name='expensive_products'),
]

Deployement of the project

Run these commands to apply the migrations:

python3 manage.py makemigrations
python3 manage.py migrate

Run the server with the help of following command:

python3 manage.py runserver

Output: