How to implement an orderable list

Working with Data
, ,
1

Many applications, e.g. todo lists, need to maintain a user-defined order of items. Suppose I have the following collections/documents:

users → { _id, name }
todos → { user_id, text, position }

I’d like to have todos look something like:

{ user_id: abc…, position: 0, text: “Do laundry” } { user_id: abc…, position: 1, text: “Clean room” } { user_id: abc…, position: 2, text: “Brush teeth” } { user_id: def…, position: 0, text: “Walk dog” } { user_id: def…, position: 1, text: “Buy dog toy” }

For each user ID, the positions should always be 0, 1, 2… N with no duplicates and no gaps. Moreover, the users should be able to re-order their list, e.g. something like this UI, and maintain integrity of the list:

image

The problem is further explored here: User-defined Order in SQL

I’ve implemented this in my Ruby application using the Mongoid Orderable gem: GitHub - mongoid/mongoid_orderable: Acts as list mongoid implementation. However, when processing many re-order actions with concurrent requests, my lists always get out of sync (position duplicates or gaps occur, e.g. positions become 0, 0, 1, 1, 1, 3, 4, 6, 6, 8… etc.)

I am experimenting using transactions with mixed results. I’d like to hear if there is a recommended/canonical way to do this in MongoDB?

2

Hi @Johnny_Shields,

Welcome to MongoDB community!

Have you considered just holding this data in an array of the specific user/page you are showing on the application.

{
user_id ,
list_id,
tiles : [ {"task" : "Do laundry" }, { "task" : "Do laundry" }....]
}

When you want to change position manipulate the array on the application side and update it as a whole to MongoDB:

db.todos.update({user_id : ..., list_id : ...},{$set : {tiles : newArray})

This operation is guaranteed to be atomic and there is no need for transactions.

If you wish to lock a record for update you can get the records you show with a field you will use as locking:

db.todos.findAndModify({user_id : ..., list_id : ..., lock:false},{$set : { lock:true}})

db.todos.update({user_id : ..., list_id : ...},{$set : {tiles : newArray, lock:false})

Thanks,
Pavel

3

For my use case I would not be able to use an Array embedded in a parent document. My app is not actually a “Todo” list; my documents have 100s of fields and I can have 1,000+ in one list. (I agree that an Array would work for simple use cases.)

How would you do this assuming it is a requirement for each document to have a “position” field?

4

@Johnny_Shields,

If the findAndModify mechanism does not work for you even when updating multiple documents consider indeed using transactions.

The find and modify works in a way that each document when selected from the specific list is only available for this process to update. Many applications do this by having a pen button before you edit lists, therefore the database update the documents that noone else will edit this list.

You can have a parent document for each list to save who edits it in each time.

Thanks
Pavel