October 19, 2026

Django QuerySets and ORM: Filtering, Searching, Ordering, Aggregation, and Advanced Queries

Introduction

Django ORM (Object-Relational Mapping) allows developers to interact with databases using Python instead of writing SQL for every operation.

A QuerySet represents a collection of database records that can be filtered, ordered, and combined using Django's ORM methods.

In this module, you will learn how to retrieve records, search data, apply filters, work with related models, perform aggregations, and display database results in Django templates.

We will use a Student model for practical examples.

1. Create a Student Model

Open your app's models.py file.

from django.db import models


class Student(models.Model):
    name = models.CharField(max_length=100)
    email = models.EmailField(unique=True)
    age = models.PositiveIntegerField()
    city = models.CharField(max_length=100)
    course = models.CharField(max_length=100)
    marks = models.DecimalField(max_digits=5, decimal_places=2)
    created_at = models.DateTimeField(auto_now_add=True)

    def __str__(self):
        return self.name

Create and apply migrations:

python manage.py makemigrations
python manage.py migrate

If your project already has a Student model, adapt the examples to its existing fields instead of creating a duplicate model.

2. Retrieve All Records

Use all() to retrieve every student record.

from your_app.models import Student

students = Student.objects.all()

To display student names:

for student in students:
    print(student.name)

Replace your_app with the actual Django app name.

Important: QuerySets are lazy. Django generally executes the database query when the results are evaluated, such as when iterating over them or converting them into a list.

3. Retrieve a Single Record

Use get() when you expect exactly one record.

student = Student.objects.get(id=1)

print(student.name)

get() raises an exception if no record matches or if multiple records match.

For web views, use get_object_or_404() when you want Django to return a 404 response if the record does not exist.

from django.shortcuts import get_object_or_404
from your_app.models import Student

student = get_object_or_404(Student, id=1)

4. Filter Records Using filter()

The filter() method returns records that match the specified conditions.

Find Students from Delhi

students = Student.objects.filter(city="Delhi")

Find Students with Marks Above 80

students = Student.objects.filter(marks__gt=80)

Find Students Aged 18 or Older

students = Student.objects.filter(age__gte=18)

Find Students with Marks Between 60 and 90

students = Student.objects.filter(
    marks__gte=60,
    marks__lte=90
)

Multiple conditions supplied to filter() are combined using AND logic.

5. Common Django Field Lookups

Django provides field lookups for comparing values and searching text.

Example:

students = Student.objects.filter(
    city__in=["Delhi", "Mumbai"]
)

6. Exclude Records

Use exclude() to return records that do not match a condition.

students = Student.objects.exclude(city="Delhi")

Find students whose marks are not below 40:

students = Student.objects.exclude(marks__lt=40)

exclude() is useful for creating lists that omit specific categories or values.

7. Search Records with Q Objects

Use Q objects when you need OR conditions or more complex combinations.

from django.db.models import Q
from your_app.models import Student

students = Student.objects.filter(
    Q(city="Delhi") | Q(city="Mumbai")
)

Find students who live in Delhi or have marks above 90:

students = Student.objects.filter(
    Q(city="Delhi") | Q(marks__gt=90)
)

Combine AND and OR conditions:

students = Student.objects.filter(
    Q(city="Delhi") &
    (Q(marks__gte=80) | Q(age__lt=20))
)

Use parentheses to make complex conditions easier to read and maintain.

8. Order Records with order_by()

Sort records by a field.

Ascending Order

students = Student.objects.order_by("name")

Descending Order

students = Student.objects.order_by("-marks")

Sort by Multiple Fields

students = Student.objects.order_by("city", "-marks")

This sorts by city first and then by marks in descending order within each city.

9. Limit QuerySet Results

Use slicing to limit the number of records.

top_students = Student.objects.order_by("-marks")[:5]

Retrieve the first ten records:

students = Student.objects.all()[:10]

Django can translate suitable QuerySet slices into SQL limits. Ordinary QuerySets do not support arbitrary negative indexing.

10. Count Records and Check Whether Results Exist

Count the number of matching students:

total_students = Student.objects.count()

Count students from Delhi:

delhi_students = Student.objects.filter(
    city="Delhi"
).count()

Check whether any student has marks above 90:

has_top_students = Student.objects.filter(
    marks__gt=90
).exists()

exists() is useful when you only need to know whether matching records exist rather than retrieve the full collection.

11. Aggregation: Average, Maximum, Minimum, and Sum

Django's aggregation functions calculate summary values across database records.

from django.db.models import Avg, Max, Min, Sum
from your_app.models import Student

summary = Student.objects.aggregate(
    average_marks=Avg("marks"),
    highest_marks=Max("marks"),
    lowest_marks=Min("marks"),
    total_marks=Sum("marks")
)

print(summary)

The result is a dictionary containing the calculated values. If there are no matching records, some aggregate values may be None.

Count Students by City

Use annotate() with Count() to calculate a result for each group.

from django.db.models import Count
from your_app.models import Student

city_summary = (
    Student.objects
    .values("city")
    .annotate(total=Count("id"))
    .order_by("-total")
)

This groups students by city and calculates the number of students in each city.

12. Understand annotate() and values()

annotate() adds calculated values to each result or group in a QuerySet.

values() returns dictionaries containing the selected fields instead of complete model instances.

Example:

students = Student.objects.values(
    "name",
    "city",
    "marks"
)

Example using annotate():

students = Student.objects.annotate(
    name_length=models.functions.Length("name")
)

You can then access the calculated field on each result:

for student in students:
    print(student.name, student.name_length)

For this example, import models from django.db or import Length directly from django.db.models.functions.

13. Update Records with update()

Use update() for a bulk update.

Student.objects.filter(
    city="Delhi"
).update(city="New Delhi")

This updates all matching records in a single database operation.

Important: update() operates directly on the database and does not call each model instance's save() method. Use instance-level updates when you need custom save behavior.

14. Delete Records with delete()

Delete all records matching a condition:

Student.objects.filter(
    marks__lt=20
).delete()

Delete a single record:

student = Student.objects.get(id=1)
student.delete()

Warning: Deletion is destructive. Always verify the filter conditions and test with sample data before running bulk delete operations on production databases.

15. Search and Filter Data in a Django View

Create a view that accepts a search term and city filter.

views.py

from django.shortcuts import render
from .models import Student


def student_search(request):
    students = Student.objects.all()

    search_query = request.GET.get("q", "").strip()
    city = request.GET.get("city", "").strip()

    if search_query:
        students = students.filter(
            name__icontains=search_query
        )

    if city:
        students = students.filter(city__iexact=city)

    students = students.order_by("name")

    return render(
        request,
        "student_search.html",
        {
            "students": students,
            "search_query": search_query,
            "city": city,
        }
    )

urls.py

from django.urls import path
from . import views

urlpatterns = [
    path(
        "students/search/",
        views.student_search,
        name="student_search"
    ),
]

Include the app's URL configuration in the project's main URL configuration if necessary.

student_search.html

<h1>Search Students</h1>

<form method="get">
    <input
        type="text"
        name="q"
        placeholder="Search by student name"
        value="{{ search_query }}"
    >

    <input
        type="text"
        name="city"
        placeholder="Filter by city"
        value="{{ city }}"
    >

    <button type="submit">Search</button>
</form>

<h2>Student Results</h2>

<ul>
    {% for student in students %}
        <li>
            {{ student.name }} —
            {{ student.city }} —
            {{ student.marks }}
        </li>
    {% empty %}
        <li>No matching students found.</li>
    {% endfor %}
</ul>

This example uses GET parameters because searching and filtering are read-only operations. The search term and city are passed through the URL.

16. Improve Performance with QuerySet Optimization

Efficient database access becomes important as your application grows.

Use select_related()

For ForeignKey and OneToOneField relationships:

students = Student.objects.select_related("classroom")

This example applies only if the Student model has a classroom foreign key.

Use prefetch_related()

For many-to-many or reverse relationships:

courses = Course.objects.prefetch_related("students")

This example assumes a Course model with a students many-to-many field.

Retrieve Only Required Fields

students = Student.objects.only("name", "email")

Or retrieve dictionaries:

students = Student.objects.values("name", "email")

Use these optimizations only when they fit the access pattern. For performance-sensitive pages, inspect the actual SQL queries and measure query counts rather than assuming an optimization always helps.

17. Common Errors and Solutions

Error 1: FieldError — Cannot resolve keyword

Cause: A filter references a field that does not exist on the model.

Solution: Check the exact field names in models.py.

Error 2: DoesNotExist

Cause: get() did not find a matching record.

Solution: Use get_object_or_404() in a view or handle the exception when appropriate.

Error 3: MultipleObjectsReturned

Cause: get() matched more than one record.

Solution: Use a unique lookup or use filter() when multiple results are expected.

Error 4: QuerySet is not iterable as expected

Cause: The code may be treating a single model instance, a dictionary, and a QuerySet as interchangeable.

Solution: Check whether the operation returns a model instance, a QuerySet, or dictionaries produced by values().

Error 5: Slow database queries

Cause: A page may execute repeated queries or retrieve unnecessary data.

Solution: Consider select_related(), prefetch_related(), and selecting only required fields. Measure performance before and after changes.

18. Frequently Asked Questions

Q1. What is a Django QuerySet?

A QuerySet represents a collection of database records that can be queried using Django ORM methods.

Q2. What is the difference between filter() and get()?

filter() returns a QuerySet, potentially containing zero, one, or many records. get() returns one object and raises an exception if the result is missing or not unique.

Q3. What is the difference between exclude() and filter()?

filter() selects records matching its conditions. exclude() removes records matching its conditions from the result.

Q4. What is Q in Django ORM?

Q objects allow complex conditions, including OR logic and grouped expressions.

Q5. What is the difference between aggregate() and annotate()?

aggregate() returns summary values for the selected QuerySet. annotate() adds calculated values to individual results or groups.

Q6. Are QuerySets evaluated immediately?

Most QuerySet operations are lazy. The query is generally executed when the results are evaluated, although some operations such as count() and exists() execute their own queries.

Q7. Why use order_by()?

It controls the order in which records are returned from the database.

19. Django ORM Interview Questions and Answers

Q1. What is Django ORM?

Django ORM maps Python model classes to database tables and provides a Python API for database operations.

Q2. What does filter() return?

It returns a QuerySet containing records that match the supplied conditions.

Q3. What is the purpose of Q objects?

They enable complex query conditions such as OR expressions and grouped AND/OR logic.

Q4. What is the difference between values() and values_list()?

values() returns dictionaries. values_list() returns tuples by default, or scalar values when flat=True is used with a single selected field.

Q5. What does annotate() do?

It adds calculated values to each result or group in a QuerySet.

Q6. How can database queries be optimized in Django?

Use appropriate query planning, select_related(), prefetch_related(), field selection, indexes where suitable, and query inspection.

Q7. What is a lazy QuerySet?

A lazy QuerySet delays database execution until its results are needed.

20. Practical Assignment

Create a Student Search and Analytics Dashboard.

Requirements:

1. Display all students.

2. Add a name search using icontains.

3. Add filtering by city.

4. Add a marks filter for students above a specified score.

5. Add ascending and descending sorting by marks.

6. Display the total number of students.

7. Display average, highest, and lowest marks.

8. Group students by city and display the count for each city.

9. Add a top-five students section.

10. Use select_related() or prefetch_related() if the application includes related models.

11. Test empty search results and invalid record IDs.

12. Verify that the dashboard displays accurate data after records are added, updated, or deleted.

Conclusion

Django QuerySets and ORM methods provide a powerful way to search, filter, sort, update, and analyze database records using Python. By learning filter(), exclude(), Q, order_by(), aggregate(), and annotate(), you can build efficient data-driven applications without writing raw SQL for every operation.

These skills are useful for student management systems, hospital management systems, e-commerce websites, dashboards, and other database-driven applications.

Next Module: Django Pagination and Search Filters — Building Fast, User-Friendly Data Listing Pages.