[Ruby] How to give MAX+1 ID to registered data when adding a new record

1 minute read

I think there are cases where you want to automatically assign a unique ID when adding a new record with Rails. For example, add the maximum value of the my_id field + 1.

I wrote a sample that gives a unique id = my_id when registering the name of a room.

Success example

  def assign
    my_id = -1
    room = Room.find_by(room: params[:room])
    if !room.nil?
      my_id = room.my_id
    else
      Room.connection.execute(
        "INSERT INTO rooms (my_id, room, created_at, updated_at) SELECT COALESCE(max(my_id), 0)+1,'#{params[:room]}','#{Time.now}','#{Time .now}' from rooms"
      )
      my_id = Room.find_by(roomr: params[:room]).my_id
    end
    redirect_to("/rooms/#{my_id}")
  end

The point is

  • Perform maximum value search of my_id by subquery of INSERT statement so that only one query is issued (exclusive)
  • Set the current time to created_at, update_at
  • Take null measures with COALESCE

Failure example

  def assign
    my_id = -1
    room = Room.find_by(room: params[:room])
    if !room.nil?
      my_id = room.my_id
    else
      new_room = Room.create(
        my_id: Room.max(my_id) + 1,
        room: params[:room]
      )
      my_id = new_room.my_id
    end
    redirect_to("/rooms/#{my_id}")
  end

In the failure example, between begin transaction and commit transaction

  1. SELECT statement –> for searching Room.max(my_id)
  2. For INSERT statement –> Room.create

Will be issued, Exclusion is not enough when assign is called from multiple clients at the same time my_id is duplicated

Actually

I really want to write it in ActiveRecord without issuing raw SQL, Isn’t there a good way?

Reference URL

Bring up the maximum value of the column + 1 on INSERT I want to do MAX+1 at the same time as the INSERT statement. I want to register data and issue max number during INSERT To ensure that ActiveRecord find_or_create_by is executed