Mainly what I do, is that I keep a field "sort_order" in the table which initially is a copy of the primary key. This is the field that I update to change the order. And it is the field I "order by" when I select from the database.
This solution may not be a good one if reordering involves a really big number of items. In a case where the list to reorder is really big (... I don't know, say hmm... > 1000 or something) I would think twice before using this approach. But the applications I've used this for, rarely involves more than 100 items per sorting (in the majority of updates only a few), so the solution works fine. Example: Some container contains a list of items that user must be able to decide the sorting of.
It is very important that the sort_order field is unique. Otherwise you'll have problems if you move an item to another container. Also, when moving an item to another list, moving the item first or last in the list may be what you want.
I have created an example using Python and MySQL, and I have tried to make it as readable as I can, and hope that people should be able to understand the code without knowing Python:
import sys
import MySQLdb
# create a database 'test' that the db user may use
conn = MySQLdb.connect(user='username',
passwd='password',
db='test')
cursor = conn.cursor()
cursor.execute('DROP TABLE IF EXISTS todo')
cursor.execute('DROP TABLE IF EXISTS todolist')
cursor.execute(
('CREATE TABLE todolist ('
' id INTEGER NOT NULL AUTO_INCREMENT,'
' name VARCHAR(255) NOT NULL,'
' PRIMARY KEY (id))'))
cursor.execute(
('CREATE TABLE todo ('
' id INTEGER NOT NULL AUTO_INCREMENT,'
' sort_order INTEGER,'
' todolist_id INTEGER, '
' description VARCHAR(255) NOT NULL,'
' PRIMARY KEY (id))'))
def insert_todolist(listname):
cursor.execute('INSERT INTO todolist SET name = %s', listname)
return cursor.lastrowid
def insert_todo(list_id, description):
cursor.execute('INSERT INTO todo SET todolist_id=%s, description=%s',
(list_id, description))
todo_id = cursor.lastrowid
# allways set sort_order to same as newly auto id, if effect that's last
cursor.execute('UPDATE todo SET sort_order = id WHERE id = %s', todo_id)
return todo_id
def reorder(move_todo_id, place_after_todo_id):
# find which list the todo to move is in
cursor.execute('SELECT todolist_id FROM todo WHERE id = %s', move_todo_id)
todolist_id, = cursor.fetchone()
# fetch a list of all todos and theyr sort keys in that list
query = ('SELECT id, sort_order FROM todo '
'WHERE todolist_id = %s ORDER BY sort_order')
cursor.execute(query, todolist_id)
# split the id and the sort_order from each row into two lists
todo_id_list = [] # this is the list to edit
sort_order_list = [] # this list will remain unchanged
for todo_id, sort_order in cursor.fetchall():
todo_id_list.append(todo_id)
sort_order_list.append(sort_order)
# remove the chosen todo from the list, will insert in selected place soon
todo_id_list.remove(move_todo_id)
index_of_place_after = 0
if place_after_todo_id: # if 0 user wants first in list, else get index
index_of_place_after = todo_id_list.index(place_after_todo_id) + 1
todo_id_list.insert(index_of_place_after, move_todo_id)
# merge the two lists into a list of reordered items of id and sort_order
insert_values = []
for i in range(0, len(todo_id_list), 1):
insert_values.append((sort_order_list[i], todo_id_list[i]))
# update in database
cursor.executemany(
'UPDATE todo SET sort_order = %s WHERE id = %s',
insert_values)
# insert some initial data
list_id = insert_todolist('My todolist')
todo1_id = insert_todo(list_id, 'Buy milk')
todo2_id = insert_todo(list_id, 'Fix car')
todo3_id = insert_todo(list_id, 'Do the dishes')
while True:
try:
# print the current items in the database
cursor.execute('SELECT * FROM todolist')
todolists = cursor.fetchall()
print '-'*80
for l in todolists:
cursor.execute(
('SELECT id, description FROM todo '
'WHERE todolist_id = %s ORDER BY sort_order'),
l[0])
print '%s %s' % l
for t in cursor.fetchall():
print ' %s, %s' % t
print '-'*80
print 'Actions:\n 1. New list, 2. New todo, 3. Reorder'
choice = raw_input('Choose action? ').strip()
if choice == '1':
list_id = insert_todolist(raw_input('New list name: '))
print 'New list created with id %s' % list_id
elif choice == '2':
list_id = int(raw_input('Add to which list? '))
todo_id = insert_todo(list_id, raw_input('Describe: '))
elif choice == '3':
todo1_id = int(raw_input('Todo to move: '))
todo2_id = int(raw_input('Move after (0 for first): '))
reorder(todo1_id, todo2_id)
except KeyboardInterrupt:
break
# clean up
cursor.execute('DROP TABLE IF EXISTS todo')
cursor.execute('DROP TABLE IF EXISTS todolist')
cursor.close()
conn.close()
sys.exit()
Running this:
%> python sort_order_test.py -------------------------------------------------------------------------------- 1 My todolist 1, Buy milk 2, Fix car 3, Do the dishes -------------------------------------------------------------------------------- Actions: 1. New list, 2. New todo, 3. Reorder Choose action? 3 Todo to move: 3 Move after (0 for first): 0 -------------------------------------------------------------------------------- 1 My todolist 3, Do the dishes 1, Buy milk 2, Fix car -------------------------------------------------------------------------------- Actions: 1. New list, 2. New todo, 3. Reorder Choose action? 3 Todo to move: 2 Move after (0 for first): 3 -------------------------------------------------------------------------------- 1 My todolist 3, Do the dishes 2, Fix car 1, Buy milk --------------------------------------------------------------------------------
