0👍
A good way (performance-wise) to close the gap (book2 moved from shelf=1, position=1) will be this (move the last book on shelf to the opened position):
Book.objects.filter(shelf=1).order_by('-position')[0].update(position=1)
An index on (shelf, position) is also needed to maintain performance.
(Found this answer in a related question)
1👍
What you want to do is very similar to a leaderboard implementation in rdbms. This is a really good read for doing that.
Essentially, what you want to do (in sql) is:
UPDATE book SET position = position - 1 WHERE shelf = X and position > Y
Where X is the shelf you moved the book from, and Y is its former position on that shelf. SQL syntax may vary.
In that article there are also examples of doing an insert of position into an existing shelf where positions must increase to make room for the newly inserted book. The article goes on to explain how to best do these in batches as moving one at a time, and then updating N records per move can be quite costly especially if you are repositioning the same books in the same shelf repeatedly.
For example, if you have 100 books on shelf 1, and you move two of them in positions [22, 56], then the optimal solution would move each book from 23-55 by one position, and each book from position 56-100 by two positions. The naive approach would move everything from 23-100 by one position. Then move everything from 56-100 by one position again.
- [Answer]-502 Bad Gateway connecting Nginx to django via socket
- [Answer]-Django Providing initial data for models with model triggers
- [Answer]-Efficiently doing thousands of lookups in a single table via django's ORM?