Filtering and Ordering QuerySets

Filtering Querysets: The WHERE Clause of Django

In the last lesson, we used get() to fetch a single object and all() to fetch every object. The real power, however, lies in filter(), which allows you to retrieve a QuerySet of objects that match specific criteria. Think of it as Django's equivalent of an SQL WHERE clause.

Field Lookups: Specifying the Filter Condition

The basic syntax for filtering is Model.objects.filter(field_name=value). This performs an exact match. But what if you need more complex comparisons? For that, Django uses field lookups, which follow the format field__lookuptype=value (note the double underscore).

Let's watch a video that demonstrates basic filtering and introduces several essential lookups.

Here's a summary of some of the most common lookups:

You can find the complete list in the official documentation, which is an excellent resource to bookmark.

Chaining Filters and Using exclude

What if you need to apply multiple conditions? You can chain filter() calls together. Each subsequent filter narrows down the QuerySet returned by the previous one. This is equivalent to using the AND operator in SQL.

You can also use the exclude() method, which works just like filter() but returns objects that do not match the given condition. This is like using WHERE NOT (...) in SQL.

Let's see how this works.

So, this Django ORM query:

Restaurant.objects.filter(restaurant_type='Chinese').exclude(name__icontains='buffet')

Is equivalent to this SQL query:

SELECT * FROM restaurant_restaurant
WHERE restaurant_type = 'Chinese' AND NOT (LOWER(name) LIKE '%buffet%');

Filtering Across Relationships

One of the most powerful features of the ORM is the ability to filter based on the fields of a related model. You can traverse ForeignKey, ManyToManyField, or OneToOneField relationships using the same double-underscore syntax.

This is the ORM's way of performing an SQL JOIN for you, behind the scenes.

For example, if a Book model has a ForeignKey to an Author model, you can find all books by authors with a specific name:

Book.objects.filter(author__name='J.K. Rowling')

The video below demonstrates this concept clearly.

Ordering Querysets

Once you have the data you want, you'll often need to sort it. The order_by() method does exactly this, acting as the ORM's ORDER BY clause.

• Model.objects.order_by('field_name') sorts in ascending order.
• Model.objects.order_by('-field_name') sorts in descending order (note the minus sign).
• You can sort by multiple fields: Model.objects.order_by('category', '-price') first sorts by category (A-Z), and then by price (high to low) within each category.

This order_by functionality is often exposed in REST APIs through query parameters, allowing the client to control the sorting of results. This directly relates to your goal of building APIs with Django.

Product.objects.filter(
    price__gte=50, 
    price__lte=100,
    stock_quantity__gt=0
).order_by('-price')

Put it into Practice

Theory is great, but the best way to learn the ORM is to use it. The following exercises use a set of models for a book-publishing database.

First, here are the models you'll be working with. Imagine these are in a models.py file in your project.

class Author(models.Model):
    firstname = models.CharField(max_length=100)
    lastname = models.CharField(max_length=100)
    joindate = models.DateField()
    popularity_score = models.IntegerField()
    followers = models.ManyToManyField('User')

class Book(models.Model):
    title = models.CharField(max_length=100)
    price = models.IntegerField(null=True)
    published_date = models.DateField()
    author = models.ForeignKey('Author', on_delete=models.CASCADE)
    publisher = models.ForeignKey('Publisher', on_delete=models.CASCADE)

class Publisher(models.Model):
    firstname = models.CharField(max_length=100)
    lastname = models.CharField(max_length=100)
    joindate = models.DateField()

class User(models.Model):
    username = models.CharField(max_length=100)
    email = models.CharField(max_length=100)

Now, try to write the Django ORM queries for the following problems.

Conclusion

You've now unlocked the full power of basic data retrieval in Django. By combining filter(), exclude(), a wide array of field lookups, and order_by(), you can construct queries to select and sort data with a high degree of precision.

Key Takeaways:

• filter() is your primary tool for selecting data, acting like SQL's WHERE clause.
• Field Lookups (__gt, __icontains, etc.) provide the specific conditions for your filters.
• Chaining filter() calls or passing multiple arguments creates an AND condition.
• exclude() is the inverse of filter(), creating a NOT condition.
• Relationship Spanning using __ lets you filter based on related models, effectively performing a JOIN.
• order_by() sorts your results, with a leading - indicating descending order.

So far, all our combined filters have used an implicit AND. But what if you need to find objects that match condition A OR condition B? This requires a different tool. In our next lesson, we will explore Q objects, which allow you to build even more complex queries with explicit OR and NOT logic.