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.
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.
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.
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
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
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.
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 AM → AD, 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
