Best way of guaranteeing and updating ordering of items retrieved from database?

Let’s say you have a one-to-many relationship between Day and Todo: i.e., one day has many todos. The order of the todos of a day is important. The todos must be retrieved from the database ordered in a specific way, that was determined when the todos were inserted into the database. Additionally, todos can be deleted, inserted and moved.

I have a similar setup in my application, at the moment. I rely on the order of certain items when they are retrieved from the database and I update their relative position whenever necessary. Currently, I update the positions of the items manually. When an items is, for example, deleted, all the items down the list shift their position with -1.

I was wondering if there is a better way of doing this. Possibly a solution that is built into Postgres or Ecto, for example. I am hoping to reduce the risk of problems with data integrity. I have been looking for a built-in solution, but have not been able to find confirmation that is does or does not exist.

Do you happen to know a better way?


As of LiveView v0.19 and Ecto v3.10, dynamically adding, removing, and re-ordering child associated records can be nicely handled by using the new :sort_param option for Ecto’s cast_assoc/3 which pairs well with LiveView’s inputs_for.


also have a look at how this example does it:

believe it does the sorting using sortablejs using liveview hooks…


Maybe I did not understand the question, but there is order_by

And if you have an auto-incremented primary id as the default in ecto, you can just order_by id

I think you’re asking the best way to manage the actual column dictating the order?

I think what you are doing is fine if you know you have relatively small collections. Otherwise, this is a really good article that discusses various solutions.


Not sure I understand OP well either but I’d think that using update_all with the inc option should work fine.

Example 1: Ecto.Repo — Ecto v3.10.3

Example 2: Composable transactions with Multi — Ecto v3.10.3


Ordering can be a bit tricky at times. You either have to load and update a bunch of of records or find a hacky way to enforce order.

Two ways have noticed:

Sql update many:
You can, if you can fetch all records after, or before the one you are modifying, write a query like:

update set position = position +/-1 where group is x

But it comes with some complexity if you are moving an item.

Another way is to rely in a string for sorting where you sort by “a” for the first record, “b” for the second. If you want to insert between you set the position to “an” (assuming n is in the middle of the alphabet). If you want to insert something more, you find the value between the ones you already have and either append a letter in the middle of the alphabet, or find something in between. An odd algorithm but it is efficient for large ordered lists.

Im writing on my phone right now so I’m sorry for the non-perfect examples and spelling that might be odd :slight_smile:


Sounds a bit like how Figma do their multi-user editing.


Reading this was indeed helpful. I ended up not having to change much. Mostly I had to optimize my queries a bit.

Thanks again for the source.

1 Like

Only problem with that is what to do after you’ve added the 26th element. I’ve done something similar but with decimals, which doesn’t have that same issue and is easier math to do on reordering.

It’s all about the next layer. "A" > "B" but "A" > "AD" > "AM" > "AMM" > "B", meaning whenever there is a need to add or reorder an item you only need to know the target neighbors and either find a letter in between, or increase the resolution by adding another letter.

If custom ordering of potentially extremely large lists is a requirement it will be hard to beat the scalability and efficiency of abusing text fields like this. With decimals there is a limit in how many decimals the database column accepts for example.

The algorithm however isn’t straight forward and has some limits. Like if an item has to be appointed position “A”, a recalculation would have to be done or nothing could be positioned before it.

But anyway, In the end it’s all about what requirements the solutions have :slight_smile:

1 Like

Right, I’m sure it’s very implementation dependent. For my use case the initial add of the element was always to the end of the list, and then later reordered if the user wanted to, so the list always needed to be able to grow, so if I used the alphabet then to maintain the same algorithm I could only add 26 items, otherwise after the Z item I’d have had to break the pattern and make a ZZ for the next, ZZZ etc. It’d have still worked, but been more complex. Realistically, if I’d anticipated > 26 elements I’d have probably used two letters as my top level, AA, AB, etc.

Agreed re the limitation of decimal types, that’s definitely a consideration, but that limitation is on the number of reorders that can be done in the same part of the list, so for most use cases (and certainly mine) that was never a limit we approached.

I was curious so I did a quick test to see how many reorders I could do before running out of precision, the default precision for an elixir Decimal is 28 (a very long way short of Postgres’s ~ 147k), and even that allows for 90 reorders in a given slot.

Correction, it allows for 90 reorders when the list is in single digits. Subtract ~ 4 (I think) per order of magnitude of the integer part.

1 Like

Oh, I wanted to add, if you use B as the first element then you can always reposition in front of that first element, you’d just use A for the calculation but never assign it to an element, so you’d have no A but then potentially AMAD, AS etc.

Also, if you expanded the space a little to include 32 characters (or any power of 2, conveniently ?` - ?\s #⇒ 64 :), then you’d have easier divisions.

I still find the math very awkward for deciding how to find the midpoint between two strings, much more awkward than finding the midpoint between two decimals :slight_smile: