[LRUG] Scoping sequences to a parent

[Andrew Stewart]

> If it’s just the index of the child within the ordered collection of children then I’d expect that to be fine.

Yes, that's what it is.

> The locking behaviour depends on what autoincrement lock mode you’re using, but hopefully that is already working.

My current (lock-y) implementation doesn't use MySQL's autoincrement feature.  It's simply an integer column in the parent which is incremented in the child's after_create callback, which will be part of the same transaction.

class Parent < ActiveRecord::Base
  has_many :children

  def next_child_number
    increment! :child_number
    child_number
  end
end

class Child < ActiveRecord::Base
  belongs_to :parent

  after_create :set_number

  def set_number
    n = parent.next_child_number
    update_column :friendly_id, "#{other_stuff}-#{n}"
    update_column :number, n
  end
end

> Your subsequent email suggests that you want to actually store this value against the child rather than derive it, which isn’t quite what I meant, but it should be doable. Once the autoincremented ID has been assigned, the child index is fixed (i.e. you can safely assign it with an UPDATE whenever is convenient) as long as you don’t allow previous children to be deleted.

Which begs the original question: how to derive the child index safely?

(As far as I can remember from 8 years ago, I implemented it the way I did because it seemed more efficient to just keep an index counter on the parent than to query the child table each time.  Although I don't understand yet why I'm getting occasional deadlocks / uniqueness violations, I assume it's because of the involvement of the parent table.)