Filtering and Ordering Data

A Quick Setup

To make our queries interesting, we need some varied data to work with. Open your Django shell and let's create a few users and tasks. For the sake of these examples, imagine we've added a due_date field to our Task model (due_date = models.DateField(null=True, blank=True)).

Run the following code in your shell to set up our practice data:

# In the Django shell
from tasks.models import Task
from django.contrib.auth.models import User
from datetime import date, timedelta

# Get or create users
user_alice, _ = User.objects.get_or_create(username='alice')
user_bob, _ = User.objects.get_or_create(username='bob')

# Clear out any old tasks to start fresh
Task.objects.all().delete()

# Create a sample set of tasks
Task.objects.create(title='Finalize report', description='Draft and send the Q3 report', due_date=date.today(), completed=False, user=user_alice)
Task.objects.create(title='Plan team meeting', description='Set agenda for the weekly sync', due_date=date.today() + timedelta(days=2), completed=False, user=user_alice)
Task.objects.create(title='Buy groceries', description='Milk, bread, and eggs', due_date=date.today() + timedelta(days=1), completed=True, user=user_bob)
Task.objects.create(title='Fix login bug', description='Users reporting 500 error on login page', due_date=date.today() - timedelta(days=1), completed=False, user=user_bob)
Task.objects.create(title='Follow up on client feedback', description='', due_date=None, completed=False, user=user_alice)

print("Sample data created!")

With our data in place, let's start querying.

1. Advanced Filtering with Field Lookups

You're already familiar with filter() for exact matches (e.g., Task.objects.filter(completed=True)). But what if you want to find tasks due after a certain date, or tasks whose titles contain a specific word? For this, we use field lookups.

Field lookups are specified using a double underscore (__) after the field name. For example, price__gt=100 translates to "where the price is greater than 100".

The following video is an excellent deep dive into filtering, lookups, and ordering. We'll be watching it in sections.

Here is a quick reference table of some of the most common lookups:

# In the Django shell
from datetime import date

overdue_tasks = Task.objects.filter(completed=False, due_date__lt=date.today())
print(overdue_tasks)

# You should see the "Fix login bug" task.

2. Querying Across Relationships

One of the most powerful features of the ORM is the ability to filter based on fields in related models. You do this using the same double-underscore (__) syntax to "span" the relationship.

For example, to find all tasks assigned to the user named alice:

# The first 'user' is the ForeignKey field on the Task model.
# The second 'username' is the field on the related User model.
>>> Task.objects.filter(user__username='alice')
<QuerySet [<Task: Finalize report>, <Task: Plan team meeting>, <Task: Follow up on client feedback>]>

This is incredibly useful and allows you to build complex queries without needing to fetch intermediate objects.

3. Sorting Results with order_by()

By default, query results are not guaranteed to be in any specific order. To control the sorting, you use the order_by() method.

• Ascending Order: Task.objects.order_by('due_date')
• Descending Order: Prepend a hyphen (-) to the field name. Task.objects.order_by('-due_date') will put the tasks with the latest due dates first.
• Multiple Fields: You can sort by multiple criteria. Task.objects.order_by('completed', '-due_date') will sort by completion status first (False then True), and then by due date (newest first) within each group.

# In the Django shell
alice_tasks = Task.objects.filter(user__username='alice').order_by('completed', 'title')
print(alice_tasks)

A Note on get() vs. filter()

As a final reminder from the last lesson, remember the crucial difference between get() and filter():

• filter() returns a QuerySet (a list-like object). It can contain zero, one, or many items. It never raises an error if no items are found.
• get() returns a single model instance. It is strict:
  • If it finds zero records, it raises a DoesNotExist error.
  • If it finds more than one record, it raises a MultipleObjectsReturned error.

Use get() only when you are certain your query will match exactly one object, typically when querying by a primary key (id) or another unique field.

Extra Practice

Your preference is for practice-heavy learning, which is a great way to master the ORM. The following article provides a large set of practice problems with solutions. The models are for a library (Author, Book, Publisher), not our task manager, but the principles are identical. This is a good test of your ability to apply these concepts to a new data structure.

Conclusion

You've now added some serious firepower to your data-querying toolkit. By combining filter(), exclude(), and order_by() with field lookups and relationship spanning, you can retrieve very specific slices of data from your database efficiently and expressively.

Key Takeaways:

• Field Lookups (__gt, __icontains, etc.) are used with filter() and exclude() to create specific conditions.
• Chaining methods like filter(...).filter(...).order_by(...) allows you to build complex queries step-by-step.
• Querying across relationships with the double-underscore syntax (user__username) is a powerful way to filter based on related models.
• order_by() gives you full control over the sorting of your results, in both ascending and descending order.
• Always use get() for a single, unique object and filter() for everything else.

Preview of the Next Lesson:
So far, all our work has been in the Django shell. It's a fantastic tool for learning and debugging, but it's not a web application. In the next module, we will finally leave the shell behind and start building the visible parts of our project. You will learn how to set up a Django project and app, create views to handle web requests, and map URLs to those views. This is where your backend logic will start to take shape and interact with the outside world.