CODEWITHSUNDEEP
ruby-on-railspostgresqltodomvc
Aurimas
Aurimas

Reputation: 2727

How to implement saving of order of todos / list?

I am developing an application which is very similar to todo list in its nature, except order of todos matters and can be changed by user.

What's a good way to save this order in db without having to re-save whole todo list upon change of order?

I am developing in Rails, Postgres and React, newest versions.

I am thinking to save it as an array in User Todos (there can be multiple users of the application), but I am thinking it could complicate things a little as every time I create a todo I would have to save the List also.

Upvotes: 2

Views: 602

Answers (2)

Benoit Essiambre
Benoit Essiambre

Reputation: 328

This was a bit of a head scratcher but here is what I figured:

create table orderedtable (
   pk SERIAL PRIMARY KEY,
   ord INTEGER NOT NULL,
   UNIQUE(ord) DEFERRABLE INITIALLY DEFERRED
)

DEFERRABLE INITIALLY DEFERRED is important so that intermediate states don't cause constraint violations during reordering.

INSERT INTO orderedtable (ord) VALUES (1),(2),(3),(4),(5),(10),(11)

Note that when inserting in this table it would be more efficient to leave gaps between ord values so as to minimize the amount of order values that need to be shifted when inserting or moving rows later. The consecutive values are for demonstration purposes.

Here's the trick: You can find a consecutive sequence of values starting at a particular value using a recursive query.

So for example, let's say you wanted to insert or move a row just above position 3. One way would be to move rows currently at position 4 and 5 up by one to open up position 4.

WITH RECURSIVE consecutives(ord) AS (
  SELECT ord FROM orderedtable WHERE ord = 3+1 --start position
UNION ALL
  SELECT orderedtable.ord FROM orderedtable JOIN consecutives ON orderedtable.ord=consecutives.ord+1 --recursively select rows one above, until there is a hole in the sequence
)
UPDATE orderedtable
SET ord=orderedtable.ord+1
FROM consecutives
WHERE orderedtable.ord=consecutives.ord;

The above renumbers the ord from 1,2,3,4,5,10,11 to 1,2,3,5,6,10,11 leaving a hole at 4. If there was already a hole at ord=4 , the above query wouldn't have done anything.

Then just insert or move another row by giving it the now free ord value of 4.

You could push rows down instead of up by changing the +1s to -1s.

Upvotes: 0

Akshay Chhikara
Akshay Chhikara

Reputation: 56

You can look into acts_as_list gem and for this you'll have to add an additional column position in your table. But this will do mass update on the records. But this gem is frequently updated.

If you want an optimised solution and minimise the number of updates on changing the list then you should check ranked_model gem but this one is not frequently updated. There is a brief on how it works :-

This library is written using ARel from the ground-up. This leaves the code much cleaner than many implementations. ranked-model is also optimized to write to the database as little as possible: ranks are stored as a number between -2147483648 and 2147483647 (the INT range in MySQL). When an item is given a new position, it assigns itself a rank number between two neighbors. This allows several movements of items before no digits are available between two neighbors. When this occurs, ranked-model will try to shift other records out of the way. If items can't be easily shifted anymore, it will rebalance the distribution of rank numbers across all members of the ranked group.

You can refer this gem and make your own implementation as it only supports rails 3 & 4.

Upvotes: 1

Related Questions