Ordering in one-to-many relationships?

Using Django Using the ORM
1

Hello everyone. I have a one-to-many relationship between two models and I want to ensure an ordering among the many objects. Let’s say I have these two models:

  • List can contain any number of items
  • Item is a member of exactly one list

And the following constrains:

  • The order of items within one list is well-defined
  • We can move around items inside the list
  • We can insert items anywhere inside the list
  • We can delete items from the list (deleted items are gone for good)
  • We do not have to transfer items between lists

My question is, how I can I establish and maintain such an order? For establishing I was thinking of adding an extra field to the Item model, let’s call it order. The order has to be unique among all items that share the same list field, but not among all items ever (there can be multiple “third item” items as long as they belong to separate lists). I can declare a field as unique, but then it will be globally unique. Is there a way to declare “the pair of list and order” as unique?

Next, what about moving items? Lets say I move object item_a all the way to the front, I then need to update the values of all other order fields as well. How can I atomically adjust the values so I don’t end up with undefined ordering if someone pulls the plug to the database during the reordering?

Finally, when deleting an item there would be a “hole” in the ordering. Should I be concerned about that hole or just ignore it? The hole will be skipped over when ordering query results, but could it trip me up when new items are added to a list?

An alternative I can think of would be to go for a “linked list” approach in which every item also tracks its predecessor and successor. This would make modifying the list cheaper, but it would make reading the list more expensive because I could no longer rely on order_by.

I expect the list to be read much more often than modified, and each list will have at most around ten or so items. So I think the approach with a separate order field is better suited.

2

This type of issue has been discussed here a couple times before. See Race condition in auto-incrementing value for one of the discussions.

Start there and see if that answers some of your questions. Feel free to reply here if you want to discuss any of those ideas in more detail.

3

Thank you, after some more digging I have found a very simple solution using order_with_respect_to. The documentation explains it all, I have to use set_XXX_order with a list of the IDs of all child items. Inserting items is tricky because I cannot set the order before the item actually exists, so I have to make sure to first save all the children in the DB, then set the order. Everything gets wrapped inside atomic to ensure ACID.

class Item(models.Model)
    list = ForeignKey(TodoList, on_delete=models.CASCADE)
    
    class Meta:
        order_with_respect_to = 'list'

ItemEditorFormset = forms.inlineformset_factory(
    TodoList,
    Item,
    form=ItemEditorForm,
    can_order=True,
    extra=0)


def items_update(request: HttpRequest, handle: str) -> HttpResponse:
    list = TodoListHandle.objects.get(value=handle).list
    
    formset = ItemEditorFormset(request.POST, instance=list)
    if formset.is_valid():
        with transaction.atomic():
            formset.save()
            ordered_ids = [
                form.cleaned_data['id'].id for form in formset.ordered_forms
            ]
            list.set_item_order(ordered_ids)

    return redirect(reverse('editor', args=[handle]))

The result is pretty clunky though, but it works. I can work from there using something like HTMX, but that’s beyond the scope of this thread.