Tidbits | Nov. 28, 2018

Keeping Django Models Ordered

by Frank Wiles

Most of us know how to order objects using the Django ORM using order_by('field') which creates a SQL query for us using ORDER BY field. That's not the type of ordering I'm going to talk about today.

I'm talking about keeping a set of objects in order with respect to each other, without any gaps. This has come up for us a couple of times in the last 6 months of client projects, and I've found a good pattern for dealing with it I wanted to share with you.

Django Drag and Drop Ordering

Think of a drag-n-drop interface your app might need. You have some subset of objects that are currently in order 1, 2, 3, 4, ..., n and you want the item in position 4 to move into position 2. The fact that drag and drop is the UI we're using isn't essential to the discussion here, just an example. This pattern would also work for any arrow based nudging movement or even a rudimentary queue system that may not have a visible UI to it at all.

The naive approach is to loop over all the objects and adjust the order manually. This honestly works fine for small sets of objects, but performance will suffer if we're talking about hundreds or thousands of elements in your set.

Setting the Stage

To give us a concrete example to work with we're going to create a couple of models. We're going to have a Task model that has some number of Steps that need to be completed in order. We'll be reordering the steps in our UI often so let's make it easy and elegant.

Our Models

class Task(models.Model):
    name = models.CharField(max_length=100)

class Step(models.Model):
    task = models.ForeignKey(Task, related_name='steps')
    name = models.CharField(max_length=100)
    order = models.IntegerField(default=1)

    objects = StepManager()

I'm purposefully keeping the number of fields small for illustration purposes, but you would probably also want creation/modification times and booleans for whether or not the Step or Task is completed. They aren't useful for our example here.

Where you put the meat of this logic is up to you, but I think it makes the most sense to do as a manager method.

Ordering Logic

Here is the manager you need, skim it and we'll dive into the details in a second.

from django.db import models, transaction
from django.db.models import F

class StepManager(models.Manager):
    """ Manager to encapsulate bits of business logic """

    def move(self, obj, new_order):
        """ Move an object to a new order position """

        qs = self.get_queryset()

        with transaction.atomic():
            if obj.order > int(new_order):
                qs.filter(
                    task=obj.task,
                    order__lt=obj.order,
                    order__gte=new_order,
                ).exclude(
                    pk=obj.pk
                ).update(
                    order=F('order') + 1,
                )
            else:
                qs.filter(
                    task=obj.task,
                    order__lte=new_order,
                    order__gt=obj.order,
                ).exclude(
                    pk=obj.pk,
                ).update(
                    order=F('order') - 1,
                )

            obj.order = new_order
            obj.save()

Assuming we had several steps already created for a task we can move it like this:

Step.objects.move(step_3_obj, 5)

This would move the object to the 5th position. In the real world, you would probably also want to ensure that the new order position is not below 1 or beyond the total count for the steps to avoid weird gaps.

Let's go through this step by step (pun intended):

  • After getting our default QuerySet we start an atomic transaction. We want to renumber the tasks that need updating, including the one moving, all together or not at all. This transaction helps ensure that.
  • We then limit our query to Steps which are part of this Task. This is a crucial bit and would depend on the nature of your application. If we didn't set this we would be reordering Steps without regard for the Tasks they were a part of which would definitely be a bug.
  • Next, we have to make a decision. Is our new order larger or smaller then our current order? This determines which items need to move around.
  • If it's larger, we need to move all of the Steps that are less than our current order and greater than or equal to our new order. Otherwise, we need the ones less than or equal to our new order position and greater than our current position.
  • We then exclude ourselves.
  • Then we issue an update call using an F() expression to either increment or decrement the object's order by 1. F expressions allow us to modify each row in our query by a value contained on that row.
  • Finally, we set our object's order directly and save.

Creating new objects

If we're going to have this setup, we also need to handle creating new objects putting them in order next, to do that override the create method on your manager like so:

from django.db import models, transaction
from django.db.models import F, Max

class StepManager(models.Manager):
    # ... previous code here ...

    def create(self, **kwargs):
        instance = self.model(**kwargs)

        with transaction.atomic():
            # Get our current max order number
            results = self.filter(
                task=instance.task
            ).aggregate(
                Max('order')
            )

            # Increment and use it for our new object
            current_order = results['order__max'] + 1
            if current_order is None:
                current_order = 0

            value = current_order + 1
            instance.order = value
            instance.save()

            return instance

Now we can create new objects normally with Step.objects.create(task=<task_obj>, name='Testing') and it will give us the proper order.

Benefits

The most significant benefit to this pattern is that we're pushing most of the work onto our database to do the incrementing and decrementing of all the objects. Also, we're doing it with two fairly speedy UPDATE queries. One to reset all of the intervening rows and one last one to set the model object we're wanting to move.

If we had merely looped over our objects, we would be forced to generate an individual update query per Step which is inefficient and does not scale well.

We've also encapsulated this logic into one spot to stay DRY and make writing tests for this logic easier.

Production Indexing

There is an index you should consider on this to make your common queries faster. On your Step model you may want:

class Step(models.Model):

    # ... original fields and manager definitions ... 

    class Meta:
        index_together = ('task', 'order')

This will create a multi-column index by Task ID and the order which will make this query fast Step.objects.filter(task=<TaskObj>).order_by('order').

DRF Ordering API

So this is all dandy, but if you're ordering some Django models with a drag and drop interface, you're doing it with Javascript so we need an API. If we're building an API with Django we're using Django REST Framework so here is an easy way to implement that.

from rest_framework import viewsets, status
from rest_framework.decorators import action
from rest_framework.response import Response
from django_filters.rest_framework import DjangoFilterBackend

from . import models
from . import serializers

class StepViewSet(viewsets.ModelViewSet):
    queryset = models.Step.objects.all().order_by('order')
    serializer_class = serializers.StepSerializer
    filter_backends = (DjangoFilterBackend, )
    filter_fields = ('task', )

    @action(methods=['post'], detail=True)
    def move(self, request, pk):
        """ Move a single Step to a new position """
        obj = self.get_object()
        new_order = request.data.get('order', None)

        # Make sure we received an order 
        if new_order is None:
            return Response(
                data={'error': 'No order given'},
                status=status.HTTP_400_BAD_REQUEST,
            )

        # Make sure our new order is not below one
        if int(new_order) < 1:
            return Response(
                data={'error': 'Order cannot be zero or below'},
                status=status.HTTP_400_BAD_REQUEST,
            )

        models.Step.objects.move(obj, new_order)

        return Response({'success': True, 'order': new_order})

Where these endpoints reside is going to depend on how you configure things in your Django project, but assuming you're using a DRF SimpleRouter and have things setup on /api/ your URL patterns are going to be something like:

  • GET /api/steps/ for a list of all steps
  • GET /api/steps/?task=<task_id> for all Steps by Task (in Step order)
  • POST /api/steps/<step_id>/move/ with a JSON body of {'order': <int>} of the that Step's new order position.

NOTE This API example excludes any authentication and authorization concerns which you will need to address before using in production. You don't want just anyone reordering your steps!

Hopefully, you've found this informative and can put this pattern to use in your next project. If you need help implementing things of this nature at your company reach out to us to check on our availability.

How to smartly keep a set of Django ORM objects in order for drag-n-drop like situations.

2018-11-28T09:25:19.010399 2018-11-28T15:55:36.574638 2018