Queries and filters

There is not much sense in having a database if you don't know how to get data from it. Let's find out how it works in Django!

Let's talk about the Model object manager. We will use it to get and filter the data for a particular model. Once you learn the syntax rules, you'll be able to easily make queries to your database. It gives you the flexibility to retrieve any objects you want.

Reading the data is the most common operation for a web application. The clients get data from the server more often than modify or delete it.

Model object manager

An instance of the Model class represents a single row in the table of your database. To start working with a set of rows, call the Model object manager methods.

The manager is a special class to get object(s) from a database to modify them. Django model manager is the interface through which database query operations are provided to Django models. Objects is the default model manager, and each model needs to have at least one model manager. To access the Manager of your model, get the attribute objects of the Model class.

Currently, we work on the tournament application for the Quidditch league. The season is coming, but the website is not ready! Wizards from Hogwarts get used to working with the books and papers, so they know nothing on databases. Fortunately, you don't need magic to start querying and searching. We create models Team and Player and that's how we define them:

from django.db import models


class Team(models.Model):
    name = models.CharField(max_length=64)


class Player(models.Model):
    height = models.FloatField()
    name = models.CharField(max_length=64)
    team = models.ForeignKey(Team, on_delete=models.CASCADE)


team_model_manager = Team.objects
player_model_manager = Player.objects

Don't worry if you don't yet understand how ForeignKey or other relationship fields work. You'll learn more about them in the subsequent topics, and for now you can just follow the examples.

It's not necessary to give an alias name to the Manager, you can use its methods simply like this: Team.objects.filter(name="Ballycastle Bats"). You can choose what you like more, but for clarity, we will access it directly in all the examples.

This small snippet helps you fill the tables with the data:

falmouth_falcons = Team.objects.create(name="Falmouth Falcons")
montrose_magpies = Team.objects.create(name="Montrose Magpies")

Player.objects.create(name="Karl Broadmoor", height=180, team=falmouth_falcons)
Player.objects.create(name="Kevin Broadmoor", height=183, team=falmouth_falcons)
Player.objects.create(name="Alasdair Maddock", height=175, team=montrose_magpies)
Player.objects.create(name="Lennox Campbell", height=197, team=montrose_magpies)

You can also use bulk_create method to add a list of objects into the database:

 Player.objects.bulk_create([
    Player(name="Karl Broadmoor", height=180, team=falmouth_falcons),
    Player(name="Kevin Broadmoor", height=183, team=falmouth_falcons),
    Player(name="Alasdair Maddock", height=175, team=montrose_magpies),
    Player(name="Lennox Campbell", height=197, team=montrose_magpies)
])

You can use the code above in any file of your app, but don't forget to import model classes from models.py and remember that you should migrate your models before using them.

Getting an object

One step at a time, we will start from getting the team we want and then move on to getting a distinct player.

We will carefully pick the parameters for our first query. Our Team model has two fields: id and name. The id field is generated automatically for every model, though we do not specify it explicitly.

We are certain that we have a team named Falmouth Falcons. Let's try to get it with the manager:

 falcons = Team.objects.get(name="Falmouth Falcons")

Looks fine. But what happens if we get a team that doesn't exist?

 tornados = Team.objects.get(name="Tutshill Tornados")

This call raises the Team.DoesNotExist exception. Unlike Python's dict get method, the manager's get method may raise an Exception. To prevent this situation and keep our program from crashing, you can wrap this call in a try-except construction:

try:
    tornados = Team.objects.get(name="Tutshill Tornados")
except Team.DoesNotExist:
    ...

Let's try to get the Karl Broadmoor player profile from the database:

 karl_broadmoor = Player.objects.get(name="Karl Broadmoor")

Karl plays for Falmouth Falcons, so we get his profile with no errors. Suppose you want to make a query that returns multiple objects:

falcons = Team.objects.get(name="Falmouth Falcons")
falcon_player = Player.objects.get(team=falcons)

You will get not a player but a Player.MultipleObjectsReturned exception.

So, to prevent exceptions, you should keep in mind two rules:

• You can only pass the parameters with the names of the fields of your model or with valid field lookups;

• You should be sure that with this query you will get exactly one object.

It appears that life is not that easy with the get queries. Occasionally, we get an object, sometimes we get an error, and we're never sure what happens next. Data may change, and our valid call will start raising an Exception. You may turn to other manager methods and see what they can do for you.

Filtering objects

Like the standard Python filter function, the manager's filter method returns only the objects that match the query. You don't have to know initially how many objects it will return, so it's safer than the get method.

The only rule is similar to the first rule for the get method:

• You can only pass parameters with names of the fields of your model or with valid field lookups.

Now, we will make our queries without fear of DoesNotExist and MultipleObjectReturned situations. We can modify our call:

 tornados = Team.objects.filter(name="Tutshill Tornados")

Although we do not have Tornados in the database, no exception is raised. So, what is the difference between these two methods? The answer is the return type. The get method always returns an instance of a particular model, while the filter method returns a QuerySet, a wrapper for a set of objects of a given model in the database.

Filtering objects only by their exact value is not always convenient: you may want to get objects that satisfy a trickier condition. For example, it could be games where the home team scored more than 12 points. We'll start with this query:

 great_score_at_home_games = Game.objects.filter(home_team_points__gt=12)

The special syntax for the parameter is: the field name, double underscores, the field lookup, a special name for actions on the field value you want to make when filtering data.

To retrieve an object from the QuerySet you can iterate it over or get the item by the index as you get it from the Python's list.

tornados = Team.objects.filter(name="Tutshill Tornados")
if len(tornados) == 1:
    tornados_team = tornados[0]

It's important to note that this method is good if your database contains few elements. If it's big, this may lead to memory overflow, so we recommend the count() method to count the number of elements:

 Team.objects.filter(name="Tutshill Tornados").count()

In addition, we want to get a Falmouth Falcons player. Let's do it with the combination of the filter and first methods:

falcons = Team.objects.get(name="Falmouth Falcons")
falcon_player = Player.objects.filter(team=falcons).first()

The last pitfall you should consider is that the first method does not raise any exceptions: if no objects are found, it returns None. So, before accessing any properties of an object, make sure that it's not None.

Using get_or_create

While working with databases, you'll often encounter situations where you want to get a specific object if it exists, or create it if it doesn't. Django provides a handy method for this - get_or_create.

This method tries to fetch an object from your database based on the parameters you provide. If the object exists, it returns a tuple containing the object and a boolean value False. If the object does not exist, this method creates it with the provided parameters and returns a tuple containing the new object and True.

Let's take an example. Suppose we want to get a team with the name "Puddlemere United". If it exists, we want to get it, otherwise, we want to create it. Here's how we can do it:

team, created = Team.objects.get_or_create(name="Puddlemere United")

In this case, if a team named "Puddlemere United" exists in the database, team will be that Team instance and created will be False. If the team does not exist, a new team will be created, team will be the new Team instance, and created will be True.

There's another way to achieve the same result, although it involves more steps. First, you check if the object exists using the .exists() method, and if it doesn't, you create it. Here's how you can do it:

if not Team.objects.filter(name="Puddlemere United").exists():
    team = Team.objects.create(name="Puddlemere United")
    created = True
else:
    team = Team.objects.get(name="Puddlemere United")
    created = False

In this case, if a team named "Puddlemere United" exists in the database, team will be that Team instance and created will be False. If the team does not exist, a new team will be created, team will be the new Team instance, and created will be True.

While both methods achieve the same result, get_or_create is more efficient because it performs the operation in a single database query, while the second method may require two queries (one to check if the object exists, and another to create it if it doesn't). Therefore, it's generally recommended to use get_or_create when you want to get an object if it exists, or create it if it doesn't.

Conclusion

Getting data from a database is an operation you will constantly refer to. We started polishing our skills by getting and filtering data. We found out how to retrieve a single object and a QuerySet to work with them as we work with other Python classes. However, the main purpose of Django is to provide the tools to make web services, and you can easily apply your query skills for doing analytics and reports.