Ordering Objects with Respect to a Foreign Key in Django

A common requirement in Django web applications is to retrieve objects in a certain order. This order can be global, where all objects of a given model are ordered with respect to a certain attribute, for example their modification date. But what if you want to retrieve objects with respect to a certain foreign key?

In Projectify, we want our tasks to retain their ordering with respect to the section they belong to, such that the order of tasks in a To-Do or Done section stays consistent. We also want to be able to insert a task into another section at a specified order.

We have tried two different approaches, and settled on using Django’s meta option order_with_respect_to. We combined order_with_respect_to with additional guarantees to satisfy our data integrity requirements.

First approach, using django-ordered-model

We first tested whether the django-ordered-model package meets our needs. It comes with a clean interface and has good Django admininistration integration. The OrderedModel base class provided by the package gives you methods to change the order of an object. All that you have to do is change your model to inherit from OrderedModel. the OrderedModel. Here’s how you can make OrderedModel part of your Django model, from their documentation:

from django.db import models
from ordered_model.models import OrderedModel


class Item(OrderedModel):
  name = models.CharField(max_length=100)

When created, objects appear in order of first created to last created:

foo = Item.objects.create(name="Foo")
bar = Item.objects.create(name="Bar")

This is how you can move objects to arbitrary positions:

# move to arbitrary position n
foo.to(12)
bar.to(13)

# move an object up or down
foo.up()
foo.down()

Move object to the top
foo.top()

One caveat is that these methods are called by hooking into the update() method, not the save() method. Therefore, if you override the save() method as a substitute for Django signals, or want to just update fields, you have to pass the fields to update and the new values, like so:

foo.to(12, extra_update={'modified': now()})

The primary issue we ran into the package is the transient duplication of the order field, that sometimes didn’t resolve on its own. We wanted to have a guarantee at the database level that no two child objects can have the same order number. We opened an issue detailing the situation, and the package maintainers replied that the code is structured in such a way that supports neither guarantees nor even standard database transactions.

Second approach, using Django’s meta option

We settled on using the native Django implementation order_with_respect_to option. We added our own guarantees on top of it. This meta option can be set on any model that has a parent-child relationship. From the Django docs:

from django.db import models

class Question(models.Model):
	text = models.TextField()
	# ...

class Answer(models.Model):
	question = models.ForeignKey(Question, on_delete=models.CASCADE)
	# ...

	class Meta:
    	order_with_respect_to = 'question'

How do you retrieve or update the order of Answer objects using Django’s order_with_respect_to option? This option equips the parent class Question with two methods: one method lets you retrieve the order of objects and the other method lets you update the order of objects. Here’s how to retrieve the order of objects within a Question object:

>>> question = Question.objects.get(id=1)
>>> question.get_answer_order()
[1, 2, 3]

The numbers in the array [1, 2, 3] represent the primary keys of each Answer object. To reorder the Answer objects, you can call the method set_answer_order():

>>> question.set_answer_order([3, 1, 2])

Django implicitly sets the order on object creation. Additionally, when you delete an object, Django automatically updates the order. Django makes two additional methods available for the ordered Answer objects. These methods are get_next_in_order() and get_previous_in_order().

What we need to implement ourselves is a method to change the order of the objects. In our case, we needed a method to move an object to the n-th position safely.

To change the order of objects, we used two features from the Django ORM. The first are standard database transactions, which ensure that either the whole operation in the context block is successfully committed to the database, or none of the operations in the context block are committed.

The second feature we used is select_for_update. The purpose of this method is to lock certain rows in the database for the duration of the transaction. This is necessary since changing the order of one object necessitates changing the order of all related items.

Here are the relevant parts of our implementation from the Projectify codebase:

@transaction.atomic
def section_move(
    *,
    section: Section,
    order: int,
    who: User,
) -> None:
    """
    Move to specified order n within project.

    No save required.
    """
    ...
    project = section.project
    neighbor_sections = project.section_set.select_for_update()
    # Force queryset to be evaluated to lock them for the time of
    # this transaction
    len(neighbor_sections)
    # Django docs wrong, need to cast to list
    order_list = list(project.get_section_order())
    # The list is ordered by pk, which is not uuid for us
    current_object_index = order_list.index(section.pk)
    # Mutate to perform move operation
    order_list.insert(order, order_list.pop(current_object_index))
    # Set new order
    project.set_section_order(order_list)
    project.save()
    ...

The code builds on the aforementioned features of database transactions and row locking. We use standard list manipulation tools in Python, but you can come up with the new order in any manner you prefer. However, since the orders are relative to a foreign key, we don’t expect the order list to be very large.

In conclusion, we recommend using native Django ORM features to implement ordering of objects with respect to a foreign key. Future work on this could include the implementation of a Django administration integration, or factoring out the ordering code as a mixin class or abstract Django model.

Note: Migrating from django-ordered-model to our own implementation

At Projectify, we care deeply about the data of our users. We want to make sure that database operations never affect data integrity.

Since we’ve already been using django-ordered-model, and since migrations are by default executed in a transaction, we can’t simply change the order field values in the same operation where we are modifying the schema.

We found it useful to have a database dump restored to a certain state so we can test our migration code. We use the following PostgreSQL command to run a restore:

pg_restore \
  --clean \
  --dbname {db_name} \
  --host localhost \
  --port 5432 \
  --username {db_user} \
  --no-owner \
  database_backup

Here, {db_name} is the name of the database, {db_user} is the PostgreSQL user that accesses this database, and database_backup is the path to the database dump that you want to restore:

Karim Marzouq originally wrote this article in 2022 and published it on his blog. You are free to use this work under the terms of the CC-BY-SA Version 4.0.

JWP Consulting GK

First approach, using django-ordered-model

Second approach, using Django’s meta option

Note: Migrating from django-ordered-model to our own implementation