CRUD relationship records across Django models

In the previous chapter, you learned how Django model relationships can help you improve data maintenance through special model data types (i.e ForgeignKey, ManyToManyField and OneToOne). CRUD operations made on Django model relationships also have a special syntax.

Although the same syntax from previous sections is applicable for direct operations made on Django model relationship data types, you can also make reverse operations on the model that's opposite to the Django model that defines the relationship data type.

One to many CRUD operations

One to many relationships are established through ForgeignKey model data types. Listing 8-25 shows a one to many relationship between two models, including a series of direct query operations on the related model.

Listing 8-25. One to many ForeignKey direct query read operations

class Menu(models.Model):
    name = models.CharField(max_length=30)

class Item(models.Model):
    menu = models.ForeignKey(Menu, on_delete=models.CASCADE)
    name = models.CharField(max_length=30)
    description = models.CharField(max_length=100)

# Get the Menu of a given Item 
Item.objects.get(name='Whole-Grain Oatmeal').menu.id

# Get the Menu id of a given Item 
Item.objects.get(name='Whole-Grain Oatmeal').menu.name

# Get Item elements that belong to the Menu with name 'Drinks'
Item.objects.filter(menu__name='Drinks')

In listing 8-25 you can see the Item model declares a ForeignKey relationship to the Menu model. Once an Item model is related to a Menu model in this way, it's possible to access a Menu model using Python's dot notation as shown in listing 8-25 (e.g. menu.id and menu.name to get the id and name of the related Menu instance on the Item reference). Notice in listing 8-25 it's also possible to create a query that references a related model using __ (two underscores) (a.k.a. "follow notation") to indicate a field in the related model.

The operations in listing 8-25 use the same query syntax as non-relationship models, because the operations are created parting from the model that has the relationship data type. However, Django also supports CRUD operations initiated on models that don't have the relationship data type.

Listing 8-26 illustrates a series of CRUD actions made through an instance of the Menu model done against its related Item model. These tasks are called reverse operations, because the model holding the relationship -- ForeignKey -- is reached in reverse.

Listing 8-26. One to many ForeignKey reverse query read operations with _set syntax

from coffeehouse.items.models import Menu, Item

breakfast_menu = Menu.objects.get(name='Breakfast')

# Fetch all Item records for the Menu
breakfast_menu.item_set.all()

# Get the total Item count for the Menu 
breakfast_menu.item_set.count()

# Fetch Item records that match a filter for the Menu
breakfast_menu.item_set.filter(name__startswith='Whole')

Listing 8-26 starts with a standard Django query for a Menu record. Although the Menu model lacks an explicit relationship to the Item model, the Item model does declare a relationship to a Menu model, and Django creates a reverse access pattern with the <one_model>.<many_model>_set syntax.

Therefore parting from a Menu record, you can see it's possible to get all Item records that have a relationship with a Menu record using the menu_record.item_set.all() syntax. Similarly, as shown in the last example in listing 8-26, it's possible to generate a query that filters a set of Item records parting from a Menu record using the same _set syntax.

Just as the reverse _set syntax is used to perform read operations parting from models that don't have an explicit relationship field toward the model that has the relationship field, it's also possible to use the same _set syntax to execute other database operation (e.g. Create, Update , Delete), as illustrated in listing 8-27.

Listing 8-27. One to many ForeignKey reverse query create, update, delete operations with _set syntax

from coffeehouse.items.models import Menu, Item

breakfast_menu = Menu.objects.get(name='Breakfast')

# Create an Item directly on the Menu
# NOTE: Django also supports the get_or_create() and update_or_create() operations
breakfast_menu.item_set.create(name='Bacon, Egg & Cheese Biscuit',
description='A fresh buttermilk biscuit...',calories=450)

# Create an Item separately and then add it to the Menu
new_menu_item = Item(name='Grilled Cheese',description='Flat bread or whole wheat ...',calories=500)
# Add item to menu using add()
# NOTE: bulk=False is necessary for new_menu_item to be saved by the Item model manager first
# it isn't possible to call new_menu_item.save() directly because it lacks a menu instance
breakfast_menu.item_set.add(new_menu_item,bulk=False)

# Create copy of breakfast items for later
breakfast_items = [bi for bi in breakfast_menu.item_set.all()]

# Clear menu references from Item elements (i.e. reset the Item elements menu field to null)
# NOTE: This requires the ForeignKey definition to have null=True 
# (e.g. models.ForeignKey(Menu, null=True)) so the key is allowed to be turned null
# otherwise the error 'RelatedManager' object has no attribute 'clear' is thrown
name= breakfast_menu.item_set.clear()

# Verify Item count is now 0 
breakfast_menu.item_set.count()
0

# Reassign Item set from copy of breakfast items
breakfast_menu.item_set.set(breakfast_items)

# Verify Item count is now back to original count
breakfast_menu.item_set.count()
3

# Clear menu reference from single Item element (i.e. reset an Item element menu field to null)
# NOTE: This requires the ForeignKey definition to have null=True 
# (e.g. models.ForeignKey(Menu, null=True)) so the key is allowed to be turned null
# otherwise the error 'RelatedManager' object has no attribute 'remove' is thrown
item_grilled_cheese = Item.objects.get(name='Grilled Cheese')
breakfast_menu.item_set.remove(item_grilled_cheese)

# Delete the Menu element along with its associated Item elements 
# NOTE: This requires the ForeignKey definition to have blank=True
# and on_delete=models.CASCADE (e.g. models.ForeignKey(Menu, blank=True, on_delete=models.CASCADE))
breakfast_menu.delete()

In listing 8-27 you can see that after obtaining a reference to a Menu record, you can generate an Item record using the create() method directly on the _set reference. Listing 8-27 also illustrates how it's possible to first generate an Item record and later link it to a Menu record using the add() method that also works on the _set reference.

Next, in listing 8-27 is an example of the clear() relationship method. The clear() method is used to dissociate relationships, in the case of listing 8-27, it sets the Menu reference for all Item records associated with a Menu named 'Breakfast' to NULL (i.e. it doesn't delete any data, it just removes the relationship reference). It's worth mentioning that in order to call the clear() method, a model field must be declared with the null=True option in order for the relationship reference to be sett to NULL.

The add() relationship method in listing 8-27 is used to associate a list of instances on a relationship. In the case of listing 8-27, it reverts the logic made by the clear() method in the same listing. An important aspect of the add() relationship method is that behind the scenes it uses a model's standard update() method to add the relationship, this in turn requires both model records to be previously saved before creating the relationship. You can bypass this limitation by using the bulk=False -- used in listing 8-27 -- to delegate the save operation to the related manager and create the relationship without saving the related object beforehand.

The remove() relationship method works like the clear() relationship method, but is designed to dissociate relationships in a granular way. In the case of listing 8-27, the remove() method sets the Menu reference for Item record named 'Grilled Cheese' to NULL (i.e. it doesn't delete any data, it just removes the relationship reference). Similar to the clear() relationship method, a model field must be declared with the null=True option in order for the relationship reference to be sett to NULL.

Finally, listing 8-27 illustrates how calling the delete() method on a model instance with a relationship, deletes the instance on which it's called and also its related model instances. In the case of listing 8-27, breakfast_menu.delete() deletes the Menu named 'Breakfast' and all the Item instances linked to it. Similar to the clear() and remove() relationship methods, the delete() relationship method requires a model field be declared with the on_delete=models.CASCADE option in order to automatically delete related models.

Many to many CRUD operations

In a similar fashion to one to many relationships, many to many relationships also support both direct and reverse CRUD operations. Listing 8-28 shows a many to many relationship between two models, including a series of direct query operations on the related model.

Listing 8-28 Many to many ManyToManyField direct query read operations

class Amenity(models.Model):
    name = models.CharField(max_length=30)
    description = models.CharField(max_length=100)

class Store(models.Model):
    name = models.CharField(max_length=30)    
    address = models.CharField(max_length=30,unique=True)
    city = models.CharField(max_length=30)
    state = models.CharField(max_length=2)
    email = models.EmailField()
    amenities = models.ManyToManyField(Amenity,blank=True)

# Get the Amenity elements of a given Store
Store.objects.get(name='Downtown').amenities.all()

# Fetch store named Midtown
midtown_store = Store.objects.get(name='Midtown')

# Create and add Amenity element to Store
midtown_store.amenities.create(name='Laptop Lock',description='Ask our baristas...')

# Get all Store elements that have amenity id=3
Store.objects.filter(amenities__id=3)

In listing 8-28 you can see the Store model declares a ManyToManyField relationship to the Amenity model. Once an Store model is related to an Amenity model in this way, it's possible to access the Amenity model using Python's dot notation as shown in listing 8-28 (e.g. amenities.all() to get all related Amenity instance on the Store reference). In addition, listing 8-28 also illustrates how it's possible to create Amenity instances using the create() method directly on the model amenities reference. Also notice in listing 8-28 how it's possible to create a query that references a related model using __ (two underscores) (a.k.a. "follow notation") to indicate a field in the related model.

The operations in listing 8-28 use the same query syntax as non-relationship models, because the operations are created parting from the model that has the relationship data type. However, Django also supports CRUD operations initiated on models that don't have the relationship data type.

Listing 8-29 illustrates a series of CRUD actions made through an instance of the Amenity model done against its related Store model. These tasks are called reverse operations, because the model holding the relationship -- ManyToManyField -- is reached in reverse.

Listing 8-29- Many to many ManyToManyField reverse query create, read, update and delete operations with _set syntax

from coffeehouse.stores.models import Store, Amenity

wifi_amenity = Amenity.objects.get(name='WiFi')

# Fetch all Store records with Wifi Amenity
wifi_amenity.store_set.all()

# Get the total Store count for the Wifi Amenity
wifi_amenity.store_set.count()

# Fetch Store records that match a filter with the Wifi Amenity
wifi_amenity.store_set.filter(city__startswith='San Diego')

# Create a Store directly with the Wifi Amenity
# NOTE: Django also supports the get_or_create() and update_or_create() operations
wifi_amenity.store_set.create(name='Uptown',address='1240 University Ave...')

# Create a Store separately and then add the Wifi Amenity to it
new_store = Store(name='Midtown',address='844 W Washington St...')
new_store.save()
wifi_amenity.store_set.add(new_store)

# Create copy of breakfast items for later
wifi_stores = [ws for ws in wifi_amenity.store_set.all()]

# Clear all the Wifi amenity records in the junction table for all Store elements
wifi_amenity.store_set.clear()

# Verify Wifi count is now 0 
wifi_amenity.store_set.count()
0

# Reassign Wifi set from copy of Store elements
wifi_amenity.store_set.set(wifi_stores)

# Verify Item count is now back to original count
wifi_amenity.store_set.count()
6

# Reassign Store set from copy of wifi stores
wifi_amenity.store_set.set(wifi_stores)

# Clear the Wifi amenity record from the junction table for a certain Store element
store_to_remove_amenity = Store.objects.get(name__startswith='844 W Washington St')
wifi_amenity.store_set.remove(store_to_remove_amenity)

# Delete the Wifi amenity element along with its associated junction table records for Store elements
wifi_amenity.delete()

In listing 8-29 you can see the various examples of many to many Django model reverse query operations. Notice the similarities to the one to many relationship CRUD operation examples shown in listing 8-26 and listing 8-27. Among the notable differences of calling relationship methods in one to many and many to many relationships, are:

• The add() and remove() relationship methods when applied on many to many models uses a model's standard bulk_create() and delete() methods to add and remove the relationship, respectively. This in turn means a model's standard save() method isn't called, therefore if either model's save() method executes custom logic it will never run with the add() and remove() relationship methods.
• If you want to execute custom logic when a many to many relationship is created or removed, use the m2m_changed signal. See the previous chapter section on model signals for additional details.
• If you declare a custom junction table for a many to many model relationship, the add(), create(), remove() and set() relationships methods are disabled. See the previous chapter section 'Options for relationship model data types' in the sub-section 'Database Options' for how to use custom junction tables.

One to one CRUD operations

CRUD operations on Django one to one relationships are much simpler than the previous relationship CRUD operations, simply because one to one relationships are inherently much simpler. In the previous chapter, you learned how one to one relationships resemble an inheritance hierarchy, where one model declares generic fields and a second (related) model inherits the fields of the former and adds more specialized fields.

This means one to one relationships just have direct query operations, as reverse operations don't make sense since models follow a hierarchy structure. Listing 8-30 shows a one to many relationship between two models, including a series of query operations on the related model.

Listing 8-30 One to one OneToOneField query operations

from coffeehouse.items.models import Item

# See listing 8-25 for Item model definition 

class Drink(models.Model):
    item = models.OneToOneField(Item,on_delete=models.CASCADE,primary_key=True)
    caffeine = models.IntegerField()

# Get Item instance named Mocha
mocha_item = Item.objects.get(name='Mocha')

# Access the Drink element and its fields through its base Item element 
mocha_item.drink.caffeine

# Get Drink objects through Item with caffeine field less than 200
Item.objects.filter(drink__caffeine__lt=200)

# Delete the Item element and its associated Drink record
# NOTE: This deletes the associated Drink record due to the
# on_delete=models.CASCADE in the OneToOneField definition
mocha_item.delete()

# Query a Drink through an Item property 
Drink.objects.get(item__name='Latte')

As you can see in listing 8-30, the operations for one to one Django model relationships are much simpler than the previous example, albeit the query operations still use the same dotted notation to move through the relationship models and fields as well as __ (two underscores) (a.k.a. "follow notation") to perform queries by field on the related model.

Read performance relationship methods: select_related() and prefetch_related()

In these last Django model relationship CRUD operation sections -- listings 8-25 through 8-30 -- you learned how easy it's to traverse from one model to another model via its relationship field to access other fields. For example, for a one to many relationship between an Item and Menu model, you can access the name field on a Menu record using the syntax item.menu.name; similarly for a many to many relationship between a Store and Amenity model, you can access the name field on an Amenity record using the syntax store.amenities.all()[0].name.

While this dot notation provides an effortless approach to access fields in related models -- similar to how the defer() and load() methods allow effortless access to deferred data -- this technique also generates additional database hits that can be prevented with the select_related() and prefetch_related() methods.

The selected_related() method accepts related model fields arguments that should be read as part of an initial query. Although this creates a more complex initial query, it avoids additional database hits on related model fields. Listing 8-31 illustrates an example of the select_related() method, along with a query that forgoes its use.

Listing 8-31 Django model select_related syntax and generated SQL

from coffeehouse.items.models import Item

# See listing 8-25 for Item and Menu model definitions

# Inefficient access to related model
for item in Item.objects.all():
     item.menu # Each call to menu creates an additional database hit

# Efficient access to related model with selected_related()
for item in Item.objects.select_related('menu').all():
     item.menu # All menu data references have been fetched on initial query 

# Raw SQL query with select_related 
print(Item.objects.select_related('menu').all().query)
SELECT "items_item"."id", "items_item"."menu_id", "items_item"."name", "items_item"."description",
"items_item"."size", "items_item"."calories", "items_item"."price", "items_item"."stock",
"items_menu"."id", "items_menu"."name" FROM "items_item" LEFT OUTER JOIN "items_menu"
ON ("items_item"."menu_id" = "items_menu"."id")

# Raw SQL query without select_related
print(Item.objects.all().query)
SELECT "items_item"."id", "items_item"."menu_id", "items_item"."name", "items_item"."description",
"items_item"."size", "items_item"."calories", "items_item"."price", "items_item"."stock" FROM "items_item"

In listing 8-31 you can see there are two variations to access the related Menu model for all Item model records. The first variation uses the Item.objects.all() syntax to get all Item model records and then directly accesses the menu field to gain access to the corresponding Menu record. The problem with this approach is that getting the Menu record for each Item record generates an additional database hit, so if you have one hundred Item records this implies an additional one hundred database hits!

The second variation in listing 8-31 adds the select_related('menu') method to the query, ensuring the related Menu record for each Item record is also fetched as part of the initial query. This technique guarantees that all relationship data is fetched in a single query.

In the bottom half of listing 8-31 you can see the raw SQL generated when the select_related() method is used and omitted. When select_related() is used, a more complex LEFT OUTER JOIN query is used to ensure all related data data is read in one step.

The prefetch_related() method solves the same problem as the select_related() method, but does so using a different technique. As you saw in listing 8-31, the select_related() method fetches related model data in a single query by means of a database JOIN, however, the prefetch_related() method executes its join logic once the data is in Python.

Although a database JOIN solves a multi-query problem in a single-query, it's a heavyweight operation that is often used sparingly. For this reason, the select_related() method is limited to single value relationships (i.e. ForeignKey and OneToOneField model fields), since multi-value relationships associated with a junction table (i.e. ManyToManyField) can produce an inordinate amount of data in a single query.

When a query uses the prefetch_related() method, Django first executes the primary query and later generates QuerySet instances for all the related models declared inside the prefetch_related() method. All of this happens in a single step, so by the time you attempt to access related model references, Django already has a pre-filled cache of related results, which it joins as Python data structures to produce the final results. Listing 8-32 illustrates an example of the prefetch_related() method.

Listing 8-32 Django model prefetch_related syntax and generated SQL

from coffeehouse.items.models import Item
from coffeehouse.stores.models import Store

# See listing 8-25 for Item  model definitions
# See listing 8-28 for Store  model definitions

# Efficient access to related model with prefetch_related()
for item in Item.objects.prefetch_related('menu').all():
     item.menu # All menu data references have been fetched on initial query 

# Efficient access to many to many related model with prefetch_related()
# NOTE Store.objects.select_related('amenities').all() is invalid due to many to many model
for store in Store.objects.prefetch_related('amenities').all():
     store.amenities.all()

# Raw SQL query with prefetch_related 
print(Item.objects.prefetch_related('menu').all().query)
SELECT "items_item"."id", "items_item"."menu_id", "items_item"."name", "items_item"."description",
"items_item"."size", "items_item"."calories", "items_item"."price", "items_item"."stock" FROM "items_item"

# Raw SQL query with prefetch_related
print(Store.objects.prefetch_related('amenities').all().query)
SELECT "stores_store"."id", "stores_store"."name", "stores_store"."address", "stores_store"."city",
"stores_store"."state", "stores_store"."email" FROM "stores_store"

The first query in listing 8-32 is equivalent to the query presented in listing 8-31 that fetches the related Menu model for all Item model records, except that it uses the prefetch_related() method. The second query in listing 8-32 is made on a many to many model relationship to fetch the related amenities model instances for all Store model records using the prefetch_related() method. It's worth mentioning this last query is only possible with the prefetch_related() method because it's a many to many model relationship.

Finally, in the bottom half of listing 8-32, you can confirm the raw SQL produced by queries that use the prefetch_related() method appears as a plain SQL query (i.e. no JOIN). In this case, it's Django/Python itself that's charged with managing and creating the additional QuerySet data structures needed to efficiently read related model data.

1. https://docs.djangoproject.com/en/1.11/ref/models/querysets/#prefetch-objects ↑