Django’s Object-Relational Mapper (ORM) is one of the most powerful features of the Django framework. It allows developers to interact with their database using Python objects instead of writing raw SQL queries. This not only improves productivity but also makes your code more readable, secure, and portable across different database systems.

Whether you are building a simple blog or a complex enterprise application, understanding how to query, filter, and aggregate data using the Django ORM is essential. With just a few lines of Python code, you can retrieve records, apply conditions, sort results, and even compute statistics such as counts, sums, and averages — all without touching SQL.

In this tutorial, you will learn how to:

Create and retrieve records using Django models
Filter and refine query results
Chain and combine queries for more complex data retrieval
Perform aggregations such as COUNT, SUM, AVG, MIN, and MAX
Use annotations to compute values per object

By the end of this guide, you’ll be comfortable writing expressive, efficient database queries using Django’s ORM and applying them in real-world projects.

Setting Up the Django Project and Models

To understand Django ORM properly, we need a real database and some models to work with. In this section, we’ll create a small Django project with a simple data structure that we’ll use throughout the tutorial.

1. Install Django

First, make sure you have Python 3.10+ installed.

Create a virtual environment and install Django:

python -m venv venv
source venv/bin/activate   # macOS / Linux
# venv\Scripts\activate    # Windows

pip install django

Verify the installation:

django-admin --version

2. Create a Django Project

Create a new project:

django-admin startproject ormproject
cd ormproject

Run the development server:

python manage.py runserver

Visit:
http://127.0.0.1:8000/
You should see the Django welcome page.

3. Create an App

We’ll create an app called store to hold our models.

python manage.py startapp store

Add it to INSTALLED_APPS in ormproject/settings.py:

INSTALLED_APPS = [
    "django.contrib.admin",
    "django.contrib.auth",
    "django.contrib.contenttypes",
    "django.contrib.sessions",
    "django.contrib.messages",
    "django.contrib.staticfiles",
    "store",   # 👈 our app
]

4. Define Our Models

Open store/models.py and define two models: Category and Product.

from django.db import models


class Category(models.Model):
    name = models.CharField(max_length=100)

    def __str__(self):
        return self.name


class Product(models.Model):
    name = models.CharField(max_length=150)
    price = models.DecimalField(max_digits=10, decimal_places=2)
    stock = models.IntegerField()
    category = models.ForeignKey(
        Category,
        on_delete=models.CASCADE,
        related_name="products"
    )
    created_at = models.DateTimeField(auto_now_add=True)

    def __str__(self):
        return self.name

This gives us:

A Category (e.g., Electronics, Books, Clothing)
A Product that belongs to a category has a price and a stock count

5. Create the Database Tables

Run migrations:

python manage.py makemigrations
python manage.py migrate

6. Register Models in Django Admin

Open store/admin.py:

from django.contrib import admin
from .models import Category, Product

admin.site.register(Category)
admin.site.register(Product)

Create a superuser:

python manage.py createsuperuser

Run the server and open:

http://127.0.0.1:8000/admin

Categories: Electronics, Books
Products:
- Laptop – $1200 – stock 5 – Electronics
- Headphones – $150 – stock 30 – Electronics
- Django Book – $40 – stock 100 – Books

These records will be used for all ORM queries in the next sections.

Basic Queries with Django ORM

(objects.all, get, create)

Django ORM works through the model’s objects manager. It gives you a powerful API to create, retrieve, update, and delete records using Python instead of SQL.

To try these examples, open the Django shell:

python manage.py shell

Then import our models:

from store.models import Category, Product

1. Retrieve All Records (`objects.all()`)

To get all categories:

Category.objects.all()

To get all products:

Product.objects.all()

This returns a QuerySet, which behaves like a lazy list. Django does not hit the database until the data is actually needed.

You can loop through results:

for product in Product.objects.all():
    print(product.name, product.price)

2. Create Records (`objects.create()`)

You can insert data directly from Python:

electronics = Category.objects.create(name="Electronics")
books = Category.objects.create(name="Books")

Create products:

Product.objects.create(
    name="Smartphone",
    price=800,
    stock=20,
    category=electronics
)

Product.objects.create(
    name="Python Django Book",
    price=45,
    stock=50,
    category=books
)

This immediately writes to the database.

3. Create Records (Two-Step Method)

Another way is to create an object first, then save it:

p = Product(
    name="Wireless Mouse",
    price=25,
    stock=100,
    category=electronics
)
p.save()

This is useful when you need to modify values before saving.

4. Get a Single Record (`objects.get()`)

To fetch exactly one record:

product = Product.objects.get(id=1)
print(product.name)

Or by another field:

product = Product.objects.get(name="Smartphone")

⚠️ get() expects exactly one match:

If no record is found → raises DoesNotExist
If more than one record is found → raises MultipleObjectsReturned

Example with error handling:

try:
    product = Product.objects.get(name="Laptop")
    print(product.price)
except Product.DoesNotExist:
    print("Product not found")

5. Count Records

To count rows:

Product.objects.count()

6. Behind the Scenes (SQL)

You can see the SQL Django generates:

print(Product.objects.all().query)

This helps you understand performance and optimize queries later.

Filtering Data with `filter()`, `exclude()`, and Field Lookups

Most real applications don’t fetch all records — they fetch specific data. Django ORM provides a rich filtering system that maps cleanly to SQL WHERE clauses.

1. Filtering with `filter()`

filter() returns a new QuerySet containing only records that match the conditions.

Get all products that cost more than $100:

Product.objects.filter(price__gt=100)

Get all products in the Electronics category:

electronics = Category.objects.get(name="Electronics")
Product.objects.filter(category=electronics)

Or using the foreign key ID:

Product.objects.filter(category_id=electronics.id)

2. Excluding Records with `exclude()`

exclude() removes matching records from the result set.

Get all products except those that are out of stock:

Product.objects.exclude(stock=0)

Get all products that are NOT in the Books category:

books = Category.objects.get(name="Books")
Product.objects.exclude(category=books)

3. Field Lookups

Django provides powerful field lookups using the double underscore (__) syntax.

Lookup	Meaning
`exact`	Exact match
`iexact`	Case-insensitive exact match
`contains`	Contains substring
`icontains`	Case-insensitive contains
`gt`	Greater than
`gte`	Greater than or equal
`lt`	Less than
`lte`	Less than or equal
`in`	In a list
`startswith`	Starts with
`endswith`	Ends with

Examples:

# Price greater than or equal to 500
Product.objects.filter(price__gte=500)

# Name contains "book" (case-insensitive)
Product.objects.filter(name__icontains="book")

# Stock between 10 and 50
Product.objects.filter(stock__gte=10, stock__lte=50)

# Products with specific IDs
Product.objects.filter(id__in=[1, 3, 5])

4. Chaining Filters

You can chain multiple filters:

Product.objects.filter(category=electronics).filter(price__lt=1000)

This is equivalent to SQL AND.

5. Filtering Across Relationships

You can filter using fields from related models.

Get all products whose category name is "Electronics":

Product.objects.filter(category__name="Electronics")

Find all categories that have products priced above 500:

Category.objects.filter(products__price__gt=500)

(products comes from related_name="products" in the Product model.)

6. Using `Q` Objects (OR Conditions)

By default, filters use AND. For OR, use Q:

from django.db.models import Q

Product.objects.filter(
    Q(price__lt=50) | Q(stock__gt=100)
)

This returns products that are cheap OR have a lot of stock.

Sorting and Limiting Query Results

(order_by, first, last, slicing)

Django ORM gives you full control over the order and size of the result set, just like SQL’s ORDER BY and LIMIT.

1. Sorting with `order_by()`

Sort products by price (ascending):

Product.objects.order_by("price")

Sort by price (descending):

Product.objects.order_by("-price")

Sort by name alphabetically:

Product.objects.order_by("name")

Sort by multiple fields (category, then price):

Product.objects.order_by("category", "-price")

2. Get the First and Last Record

Get the cheapest product:

Product.objects.order_by("price").first()

Get the most expensive product:

Product.objects.order_by("price").last()

If the queryset is already ordered:

Product.objects.first()
Product.objects.last()

3. Limiting Results with Slicing

You can limit results using Python slicing syntax.

Get the first 5 products:

Product.objects.all()[:5]

Get products 6 to 10:

Product.objects.all()[5:10]

Get the top 3 most expensive products:

Product.objects.order_by("-price")[:3]

Django converts these into SQL LIMIT and OFFSET queries automatically.

4. Pagination Example

Get products for page 2 (5 items per page):

page = 2
page_size = 5
start = (page - 1) * page_size
end = start + page_size

Product.objects.order_by("id")[start:end]

This pattern is commonly used in APIs and list views.

5. Check if Results Exist

Product.objects.filter(stock=0).exists()

Returns True or False without loading all records — very efficient.

Aggregations: `Count`, `Sum`, `Avg`, `Min`, and `Max`

Aggregations allow you to calculate statistics across multiple rows in the database, just like SQL’s COUNT, SUM, AVG, MIN, and MAX.

Django provides these via the django.db.models module.

1. Basic Aggregation

Import the aggregation functions:

from django.db.models import Count, Sum, Avg, Min, Max

Get the total number of products:

Product.objects.aggregate(Count("id"))

Get the total stock across all products:

Product.objects.aggregate(Sum("stock"))

Get the average product price:

Product.objects.aggregate(Avg("price"))

Get the cheapest and most expensive products:

Product.objects.aggregate(
    Min("price"),
    Max("price")
)

Result example:

{'price__min': 25, 'price__max': 1200}

2. Naming Aggregation Results

You can give meaningful names:

Product.objects.aggregate(
    total_products=Count("id"),
    total_stock=Sum("stock"),
    average_price=Avg("price")
)

Result:

{
  'total_products': 10,
  'total_stock': 305,
  'average_price': Decimal('312.50')
}

3. Aggregating with Filters

Aggregate only electronics products:

Product.objects.filter(category__name="Electronics").aggregate(
    total_stock=Sum("stock"),
    avg_price=Avg("price")
)

4. Grouping with `annotate()`

aggregate() gives a single result.
annotate() computes values per row or per group.

Count how many products each category has:

Category.objects.annotate(product_count=Count("products"))

Loop through results:

for category in Category.objects.annotate(product_count=Count("products")):
    print(category.name, category.product_count)

5. More Useful Annotations

Total stock per category:

Category.objects.annotate(
    total_stock=Sum("products__stock")
)

Average price per category:

Category.objects.annotate(
    avg_price=Avg("products__price")
)

6. Filtering on Aggregated Values

Find categories with more than 5 products:

Category.objects.annotate(
    product_count=Count("products")
).filter(product_count__gt=5)

This becomes an SQL HAVING clause behind the scenes.

Advanced Query Techniques

values(), values_list(), distinct(), and F Expressions

These tools help you optimize queries, work with raw data, and perform database-level calculations.

1. Using `values()`

values() returns dictionaries instead of model objects.

Get only product names and prices:

Product.objects.values("name", "price")

Result:

[
  {"name": "Laptop", "price": 1200},
  {"name": "Headphones", "price": 150}
]

This is great for APIs and JSON responses.

2. Using `values_list()`

values_list() returns tuples (or flat lists).

Product.objects.values_list("name", "price")

Result:

[("Laptop", 1200), ("Headphones", 150)]

Get only product names as a flat list:

Product.objects.values_list("name", flat=True)

3. Using `distinct()`

Remove duplicates.

Get all unique category IDs in products:

Product.objects.values_list("category_id", flat=True).distinct()

Get all categories that actually have products:

Category.objects.filter(products__isnull=False).distinct()

4. F Expressions (Database-Level Operations)

F expressions allow you to compare fields or update them without pulling data into Python.

Import it:

from django.db.models import F

Find products where stock is less than 10:

Product.objects.filter(stock__lt=10)

Increase stock by 10 for all electronics:

Product.objects.filter(category__name="Electronics").update(
    stock=F("stock") + 10
)

This happens inside the database — no race conditions, no loops.

5. Comparing Fields

Find products where stock is less than price (just an example):

Product.objects.filter(stock__lt=F("price"))

6. Calculated Fields with `annotate()` and `F`

Create a virtual field:

Product.objects.annotate(
    inventory_value=F("price") * F("stock")
)

Loop through:

for p in Product.objects.annotate(inventory_value=F("price") * F("stock")):
    print(p.name, p.inventory_value)

Performance Tips: `select_related`, `prefetch_related`, and Query Optimization

Django ORM is powerful, but careless queries can lead to slow pages and unnecessary database hits. The most common problem is the N+1 query issue, which we’ll fix using select_related() and prefetch_related().

1. The N+1 Query Problem

Consider this:

products = Product.objects.all()

for p in products:
    print(p.name, p.category.name)

This runs:

1 query for all products
1 extra query per product to fetch its category

If you have 100 products → 101 queries 😬

2. Fixing It with `select_related()`

Use select_related() for ForeignKey and OneToOne relationships.

products = Product.objects.select_related("category")

for p in products:
    print(p.name, p.category.name)

Now Django uses a single SQL JOIN — only 1 query.

3. Using `prefetch_related()`

Use prefetch_related() for ManyToMany and reverse ForeignKey relationships.

Example: load categories with their products:

categories = Category.objects.prefetch_related("products")

for c in categories:
    print(c.name)
    for p in c.products.all():
        print(" -", p.name)

Django will execute:

One query for categories
One query for all related products

Instead of dozens of queries.

4. Combining Both

You can mix them:

Product.objects.select_related("category").prefetch_related("category__products")

5. Limiting Loaded Fields with `only()` and `defer()`

Load only certain fields:

Product.objects.only("name", "price")

Defer heavy fields:

Product.objects.defer("created_at")

This reduces memory and query time.

6. Avoiding Unnecessary Queries

Use exists() instead of count():

Product.objects.filter(stock=0).exists()

Instead of:

Product.objects.filter(stock=0).count() > 0

7. Debugging Queries

In Django shell:

from django.db import connection
print(connection.queries)

Or in settings.py:

DEBUG = True

Then watch the SQL queries in the console.

Common ORM Patterns and Best Practices

After learning queries, filters, and performance tuning, it’s important to apply good design patterns so your Django projects stay fast, readable, and bug-free.

1. Use QuerySets, Not Python Loops

❌ Bad:

total = 0
for p in Product.objects.all():
    total += p.stock

✅ Good:

from django.db.models import Sum
Product.objects.aggregate(total_stock=Sum("stock"))

Let the database do the work — it’s faster and more efficient.

2. Use `get_object_or_404` in Views

Instead of:

product = Product.objects.get(id=pk)

Use:

from django.shortcuts import get_object_or_404

product = get_object_or_404(Product, id=pk)

This prevents crashes and returns a proper HTTP 404 response.

3. Avoid Hardcoding IDs

❌ Bad:

Product.objects.filter(category_id=1)

✅ Good:

electronics = Category.objects.get(name="Electronics")
Product.objects.filter(category=electronics)

This makes your code more readable and less fragile.

4. Use `exists()` Instead of `len()`

❌ Bad:

if len(Product.objects.filter(stock=0)) > 0:
    ...

✅ Good:

if Product.objects.filter(stock=0).exists():
    ...

This avoids loading all records into memory.

5. Keep Queries in Managers or Services

Move complex logic out of views.

Example custom manager:

class ProductManager(models.Manager):
    def in_stock(self):
        return self.filter(stock__gt=0)

In models.py:

class Product(models.Model):
    ...
    objects = ProductManager()

Usage:

Product.objects.in_stock()

6. Use Transactions for Critical Updates

from django.db import transaction

with transaction.atomic():
    product.stock -= 1
    product.save()

Prevents partial updates when something fails.

7. Avoid Raw SQL Unless Necessary

Django ORM protects you from SQL injection and makes code portable.

Only use raw SQL when:

Performance requires it
ORM cannot express the query

8. Add Database Indexes

For frequently filtered fields:

class Product(models.Model):
    name = models.CharField(max_length=150, db_index=True)
    price = models.DecimalField(max_digits=10, decimal_places=2, db_index=True)

This dramatically improves query speed.

Conclusion and Next Steps

Django’s ORM is more than just a database abstraction layer — it’s a powerful toolkit that lets you build expressive, safe, and highly efficient data-driven applications using pure Python. In this tutorial, you’ve gone from setting up a simple data model to writing advanced queries, aggregations, and performance-optimized data access patterns.

You now know how to:

Create and retrieve data using Django models
Filter, sort, and limit query results
Perform database-level aggregations and annotations
Use advanced tools like F expressions and values()
Optimize queries with select_related() and prefetch_related()
Apply best practices for clean and scalable ORM usage

These skills are essential for building real-world Django applications such as e-commerce platforms, dashboards, content management systems, and REST APIs.

You can find the full source code on our GitHub.

We know that building beautifully designed Mobile and Web Apps from scratch can be frustrating and very time-consuming. Check Envato unlimited downloads and save development and design time.

That's just the basics. If you need more deep learning about Python, Django, FastAPI, Flask, and related, you can take the following cheap course:

Thanks!

Getting Started with Django ORM: Queries, Filters, and Aggregations

Learn Django ORM with practical examples of queries, filters, and aggregations. Master data retrieval, performance, and best practices in Django.

Table of Contents:

Setting Up the Django Project and Models

1. Install Django

2. Create a Django Project

3. Create an App

4. Define Our Models

5. Create the Database Tables

6. Register Models in Django Admin

Basic Queries with Django ORM

1. Retrieve All Records (objects.all())

2. Create Records (objects.create())

3. Create Records (Two-Step Method)

4. Get a Single Record (objects.get())

5. Count Records

6. Behind the Scenes (SQL)

Filtering Data with filter(), exclude(), and Field Lookups

1. Filtering with filter()

2. Excluding Records with exclude()

3. Field Lookups

4. Chaining Filters

5. Filtering Across Relationships

6. Using Q Objects (OR Conditions)

Sorting and Limiting Query Results

1. Sorting with order_by()

2. Get the First and Last Record

3. Limiting Results with Slicing

4. Pagination Example

5. Check if Results Exist

Aggregations: Count, Sum, Avg, Min, and Max

1. Basic Aggregation

2. Naming Aggregation Results

3. Aggregating with Filters

4. Grouping with annotate()

5. More Useful Annotations

6. Filtering on Aggregated Values

Advanced Query Techniques

1. Using values()

2. Using values_list()

3. Using distinct()

4. F Expressions (Database-Level Operations)

5. Comparing Fields

6. Calculated Fields with annotate() and F

Performance Tips: select_related, prefetch_related, and Query Optimization

1. The N+1 Query Problem

2. Fixing It with select_related()

3. Using prefetch_related()

4. Combining Both

5. Limiting Loaded Fields with only() and defer()

6. Avoiding Unnecessary Queries

7. Debugging Queries

Common ORM Patterns and Best Practices

1. Use QuerySets, Not Python Loops

2. Use get_object_or_404 in Views

3. Avoid Hardcoding IDs

4. Use exists() Instead of len()

5. Keep Queries in Managers or Services

6. Use Transactions for Critical Updates

7. Avoid Raw SQL Unless Necessary

8. Add Database Indexes

Conclusion and Next Steps

Related Articles

1. Retrieve All Records (`objects.all()`)

2. Create Records (`objects.create()`)

4. Get a Single Record (`objects.get()`)

Filtering Data with `filter()`, `exclude()`, and Field Lookups

1. Filtering with `filter()`

2. Excluding Records with `exclude()`

6. Using `Q` Objects (OR Conditions)

1. Sorting with `order_by()`

Aggregations: `Count`, `Sum`, `Avg`, `Min`, and `Max`

4. Grouping with `annotate()`

1. Using `values()`

2. Using `values_list()`

3. Using `distinct()`

6. Calculated Fields with `annotate()` and `F`

Performance Tips: `select_related`, `prefetch_related`, and Query Optimization

2. Fixing It with `select_related()`

3. Using `prefetch_related()`

5. Limiting Loaded Fields with `only()` and `defer()`

2. Use `get_object_or_404` in Views

4. Use `exists()` Instead of `len()`