Take the 2-minute tour ×

We are using Django.

We have a model object Story that have N Section objects ordered by a custom field "index". This reflects in the database as a Section table having an "index" column and a "story_id" column.

When a new Section is added, we just create it with a new "index" that is the maximum one.

So far so good.

The problem is : we want to insert a Section that would be ordered between two already existing sections. How to efficiently add a new Section entry but make it positionned between two indexes?

Lets say we have a Section with id A and index 1. And a Section with id B and index 2. I want to add a Section id C but with an index between 1 and 2 to make sure it's between A and B.

Solution we envisaged :

  1. Use integer indexes, on Section insertion just update the indexes of all the following Sections for the same Story id. Looks maybe a bit overkill, I don't know.
  2. Use floating point indexes, on Section insertion just generate a new index that is between the previous and the next index , like 0.5 for a Section between Section index 1 and 2. Might be full of surprises, so I'm not sure, but the simplest solution to implement.
  3. Remove the index field and just make sure that each Section know the following Section id (and maybe previous the Section id too). Works like a linked list so insertion is simple and efficient BUT how do you retrieve an ordered list of Section then? At the moment we have no idea...

How would be a good way to do this?

share|improve this question
Interesting question. My first thought would just be to run an update UPDATE Table SET Index=Index+1 WHERE Index > 1 –  JNK Aug 23 '11 at 14:13
I like @JNK's idea. It's simple and should be fast enough unless you have a crap ton of rows to update. –  NullUserException Aug 23 '11 at 17:05
Well first I'm not sure how to express it in Django, so please write an answer explaining how if you know how to do this - but I agree it seems straightforward... Second, I don't know if it have big performance cost for, say hundreds of rows to modify this way. Some informations about this might help because I'm not used to SQL/database optimization. –  Klaim Aug 23 '11 at 17:16
add comment

1 Answer

For option #1, How inefficient would the following be?

from django.db.models import F

section = Section.objects.create(...)
).exclude(pk=section.pk).update(index=F('index') + 1)
share|improve this answer
add comment

Your Answer


