1

I have a lot of data in the activerecord model and I want it's ID to keep organized sequentially even if I delete one record.

For example the ID in the table were 1, 2, 3, 4 and when I delete ID 2 it will be 1, 3, 4 I want it to automatically fill the deleted number (2), anyone know how to do that?

nyi
  • 3,123
  • 4
  • 22
  • 45
Fauzi Rachman
  • 103
  • 1
  • 2
  • 9
  • I'm not sure if a solution for something like this already exists, but I believe it could get real messy if you have associations around models – Subash Apr 03 '18 at 07:13
  • Why do you want to make ID sequential ? If you provide the context, we can help you out on that. – Ashik Salman Apr 03 '18 at 09:00
  • Instead of changing ids(which is very bad practise), you can use something similar to row_number from your underlying database. Ref: https://stackoverflow.com/questions/11952245/select-row-number-in-postgres, https://www.postgresql.org/docs/current/static/functions-window.html – rubish Apr 03 '18 at 13:58

3 Answers3

1

If you will update the id it may effect on other associated table. So, I will suggest you to add a sequence_id (or any) to the table. then sort the table by id & update the sequence_id column of each record. Then you can get order 1, 2, 3, 4 from sequence_id.

You can do this like (here User is a model of users table):

after_destroy :update_sequence

def update_sequence
  User.order("id").find_each.with_index do |user ,i|
    user.update_attribute :sequence_id, i + 1
  end
end
Matt
  • 435
  • 3
  • 9
Kishan Ku. Patel
  • 311
  • 1
  • 2
  • 10
  • Would be a good idea to only update the records after the one that was just deleted, if possible. You might also want to move this code into a background job (so you're not waiting for literally every record to process before pageload, assuming you can destroy records through a view). – Matt Apr 03 '18 at 14:50
  • Agree with you Matt, we can delete a record through ajax and can call a another ajax with in the success method to update the sequence. – Kishan Ku. Patel Apr 04 '18 at 07:19
1

I would definitely not use the id to do this, as changing the primary key value is going to be very problematic.

You could add an additional column, but whether it is worth doing so depends on what problem you are trying to solve here.

You would definitely have some difficult edge cases to deal with, in which a destroy action has to temporarily prevent any other destroy or insert actions from happening until it has renumbered all of the following values.

You should also consider what happens then you delete row "3" and you have a subsequent 2 million rows to renumber.

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
0

I will suggest you, to use gem act_as_list

This will provide the capabilities for sorting and reordering a number of objects in a list

Ganesh
  • 1,924
  • 1
  • 18
  • 31
  • Another option along these lines is [ranked-model](https://github.com/mixonic/ranked-model) – Matt Apr 03 '18 at 14:53