Making a model collection reorderable

Questions / Help
1

I am working on a small Phoenix+Ecto project. One of its capabilities is that users can create collections of items, and then put these items in a user-specified order. (so if a new item is added, a user might decide that this item should go in the second position of all items in the collection).

Now storing something like this in a relational database is a bit of a problem. The only approach I can think of is to make a ‘position’ integer column (and a unique constraint on position + collection_id), and when something is moved from one location in a collection to another location, to update the positions of all items in-between.

What would be the nicest way to build this using Ecto?

2 Likes
2

The only thing I would change in your idea is the integer field to a float field. So instead of updating all the items, you could just change the position field. For example, if you have:

[%Item{id: 1, pos: 1}, 
 %Item{id: 2, pos: 2}, 
 %Item{id: 3, pos: 3},
 %Item{id: 4, pos: 4}]

And you want to put the item with the id == 3 between the the items 1 and 2, instead of having to change all the items you could just update the pos field to the median of the item position ((item1.pos + item2.pos) / 2), in the end you would have something like:

[%Item{id: 1, pos: 1}, 
 %Item{id: 3, pos: 1.5},
 %Item{id: 2, pos: 2}, 
 %Item{id: 4, pos: 4}]

Did you get it?

3 Likes
3

@kelvinst Thank you for your answer!

Using floats is an interesting idea, but as they are of limited precision, you will run into problems if you only rely on being able to divide them in half each time you insert something between two other items. If you start out with the integers, I believe you can exactly insert something fifty-three times in the place between the first and second item before you reach numbers smaller than the IEEE 64 bit epsilon value.

Since asking this question, I’ve come across the ranked_model ruby gem and the similar acts_as_list ruby gem. Both use an integer to store a collection. ranked_model goes into a little more detail, and states that it tries to spread out across the whole 64bit integer range to allow many updates before reordering the whole collection.

That is probably going to be far too complicated/cumbersome for my application though.
I will stick with the simple integer column:

  • When inserting an item at the end, select the highest value and give it the value after that.
  • When moving an item to a position it already has, do nothing :smiley: .
  • When moving an item to an earlier position, increment the positions of all items between the new up to and including the old position.
  • When moving an item to a later position, decrement the positions of all items between the old up to and including the new location.
  • When deleting an item, just delete it as the remaining items will stay ordered.

I am wondering how I can writing this code in a re-usable fashion. in Rails, I’d add post-insert/update/destroy hooks, but these are (explicitly) not part of Ecto+Phoenix’ philosophy:

  • How to implement above algorithm in a way that it can be re-used with multiple schemas?
  • How to ensure that the required SQL query is executed at the same time (preferably in one compound query, or otherwise in a database transaction) as the one that creates/updates/deletes the other fields of the model?
1 Like
4

Well that’s a good approach too. But I got your reuse focus on the question.

For this, I would use Ecto.Multi, it’s pretty easy to build a pipeline of ecto operations and put all of them into a transaction. There are examples of how to use it in the linked docs.

I would try to make it as simple as possible. Here is a start:

defmodule EctoListOrder do
  def update(module, resource, new_pos, old_pos) when new_pos < old_pos do
    multi =
      Multi.new 
      |> Multi.update(:"index#{old_pos}", module.changeset(resource, %{pos: new_pos}))
    (new_pos..old_pos-1)
    |> Enum.reduce(multi, fn(pos, multi) -> 
      res = module |> Repo.get_by(pos: pos)
      Multi.append(multi, :"index#{pos}", module.changeset(res, %{pos: pos+1}))
    end)
  end
end

This is one of the multiple headings you should do of course, but did you get the idea?

2 Likes
5

Honestly I’d probably be lazy and just have a foreign key to the one previous (or next) in the ordering along with another ordering field to break any ties (could even have a unique column on the two joint ordering columnss too)…

1 Like
6

That’s neat. If you don’t have to load big quantity of ordered data, that’s a very good idea.

7

Always ways to cache that ordering too, or a trigger to update an internal ordering field or something, or a view, or maybe an index even…

1 Like
8

@OvermindDL1 wait, how would that work? Would it be possible to ORDER BY something like that?

9

As long as you have something else to group on to grab chunks, like a forum thing would be, it is trivial, otherwise there are still ‘ways’ but it depends on each use-case in my experience.

1 Like
10

I have exactly the same problem. @Qqwy what did you end up doing?
I’m a begginer, so honestly i didn’t understand @OvermindDL1 's solution.

1 Like
11

Sorry for my slow reply.

In the end, I ended up with a quite convoluted three-step query:

  1. flip order location from a positive number to the negative of the new location.
  2. increment (or decrement if the new location is lower than the old) all values between the old and new order location.
  3. set order location of all negative numbers to positive.

This should be executed together in a database transaction, but will even detect conflicts when ran in parallel (with a database-transaction-level lower than ‘serialized’) with another insertion/deletion/re-ordering because at one of these steps, a duplicate order location will then end up happening.

1 Like