Keep a sequence column gapless after deletes

Questions / Help
,
1

I’ve got a Project schema that has an order column which is the order for rows to be shown to users. This column should always start at zero and have gapless increments of 1 (sequence).

After one or many projects are remove, I want to fill all the gaps that may have been produced and I’m having difficulties translating this into a transaction step. Could you give me some pointers? This is what I got so far:

          |> Multi.update_all(
            :slide_projets,
            from(pro in Project,
              select: %{new_order: over(row_number(), :order)},
              where: pro.user_id == ^user.id,
              where: pro.is_active == true
            ),
            set: [order: :new_order]
          )

Which produces this error

     ** (Ecto.Query.CastError) deps/ecto/lib/ecto/repo/queryable.ex:166: value `:new_order` in `update` cannot be cast to type :integer in query:

     from p0 in Timetask.Schemas.Project,
       where: p0.user_id == ^8344,
       where: p0.is_active == true,
       update: [set: [order: ^:new_order]],
       select: %{new_order: over(row_number(), :order)}

Obviously the code is trying to set the atom itself, but then I don’t know how to access the value.

2

If i understand correctly, that order field is an integer. So when you’re removing a project, every project which order field is higher, from the same user and active should come down (by 1).

In this case, you don’t need to compute the new order value. Select all rows with you where conditions, adding one where you narrow the list to projects with an order field value higher than the one you’ve just removed, and then use the update operator inc: update: [inc: [order: -1]].

That only works when you’re removing one project at the time though.

1 Like
3

The thing is it may be that many projects have been deleted because it’s part of a cascading delete coming from a related table (actually setting it to inactive).

They could also be spread across the sequence, so instead of hand managing the order column, I want to do a hard reset for simplicity and peace of mind.

4

Got it. Then you might go towards making a subquery, to access the integer you’re trying to extract. Something like:

from(pro in Project,
   join: s in subquery(project_row_order_query), on: s.project_id == pro.id),
   update: [set: [order: s.new_order]]
)

With the projected_row_order_query the query that compute the new order field value for each project you’re trying to modify (you’ll have to also return the related project_id to be able to assign the value on the proper row).

3 Likes