14

Why does rails not populate an auto-incrementing column in the item returned from a create? Is there a better way to do this?

In rails, when you do a = Foo.create then a.id is populated

But if you have a field that was created via 

def up   
  execute "ALTER TABLE my_table ADD COLUMN my_auto_incrementing_column INTEGER AUTO_INCREMENT not null UNIQUE KEY;"
end

Then that field does not appear when you use create. You have to use a reload also.

a = Foo.create
a.id # not nil
a.my_auto_incrementing_column # nil
a.reload
a.my_auto_incrementing_column # is now populated

Version information:

$ ruby -v
ruby 1.9.3p484 (2013-11-22 revision 43786) [x86_64-darwin14.5.0]
$ bundle exec rails -v
Rails 3.2.12
$ mysql --version
mysql  Ver 14.14 Distrib 5.6.26, for osx10.10 (x86_64) using  EditLine wrapper

Some background:

This code is being applied to a large existing in-production rails codebase that requires that all id fields be UUIDs. The auto_increment column is not a primary key, because it was added after we had discovered that a new external integration partner could not handle using our existing long unique identifiers (UUIDs).

We are working hard to update our version of ruby but we don't want to wait for that as a solution to this problem. Also, after reading changelogs in activerecord, I still don't have proof that any future version of ruby/rails will contain a bugfix for this problem.

The code which I want to improve:

class Foo < ActiveRecord::Base
  has_one :object_containing_auto_incrementing_column

  def my_method
    if self.object_containing_auto_incrementing_column.nil?
      self.object_containing_auto_incrementing_column = ObjectContainingAutoIncrementingColumn.create(owner: self)
      self.object_containing_auto_incrementing_column.reload
    end
    self.object_containing_auto_incrementing_column.my_auto_incrementing_column
  end
end
compiledweird
  • 918
  • 2
  • 12
  • 30
  • Possibly a duplicate of http://stackoverflow.com/a/9534482/531479 – CWitty Dec 07 '15 at 19:50
  • @CWitty http://stackoverflow.com/questions/9513739/generate-an-auto-increment-field-in-rails/9534482#9534482 was very helpful in designing this solution, but it does not explain why rails has this bug or how to get around it. Also it is based on postgres, so some of the advice has syntax which does not apply. – compiledweird Dec 07 '15 at 21:09
  • 1
    Please find out what `SHOW CREATE TABLE` thinks the table looks like. I want to see if activerecord correctly translated what your code said. – Rick James Jan 08 '16 at 05:31
  • It sounds like your code is changing it on disk and not in memory and so the reload is required to fetch the data. mysql of course uses flush as well for modifications to tables. http://apidock.com/rails/ActiveRecord/Persistence/reload – Shawn Jan 08 '16 at 18:08
  • 1
    This is a limitation of MySql itself. Take a look at the `mysql2` driver. It has a `last_id` method specifically for getting the id of the last insert. http://www.rubydoc.info/gems/mysql2/0.4.2/Mysql2/Client#last_id-instance_method. There is no equivalent method for getting other fields. You have to do a select after the insert, which is what you are doing with `reload`. Postgres and oracle have a `RETURNING` statement for doing this, but it looks like that is not supported by `ActiveRecord`. – Justin Howard Jan 12 '16 at 21:35

2 Answers2

5

After looking at the source code it does not appear that ActiveRecord tries to populate auto-incrementing columns. It only assigns the value that is returned by the INSERT statement to the #id attribute and nothing else.

 # ActiveRecord:: Persistence::ClassMethods
 def create
 # ...
   self.id ||= new_id if self.class.primary_key
 # ...
 end

If you want to populate my_auto_incrementing_column without hitting the DB twice, I think there is no way around patching ActiveRecord itself.

Have a look at how the insert method is implemented:

  # Returns the last auto-generated ID from the affected table.
  #
  # +id_value+ will be returned unless the value is nil, in
  # which case the database will attempt to calculate the last inserted
  # id and return that value.
  #
  # If the next id was calculated in advance (as in Oracle), it should be
  # passed in as +id_value+.
  def insert(arel, name = nil, pk = nil, id_value = nil, sequence_name = nil, binds = [])
    sql, binds = sql_for_insert(to_sql(arel, binds), pk, id_value, sequence_name, binds)
    value      = exec_insert(sql, name, binds)
    id_value || last_inserted_id(value)
  end

There might not be any trivial way to change the current API to populate your field in question.

Tawan
  • 459
  • 3
  • 8
0

Apparently it is still not possible to get in create() a field (not id) generated in autoincrement at database level by SEQUENCE.

I worked around the problem

in my case: PostgreSQL AUTOINCREMENT by SEQUENCE

with an after_create callback

Migration

class CreateFoo < ActiveRecord::Migration[7.0]
  def change
    create_table :foo do |t|
      t.integer :autoinc_field
    end
    execute "CREATE SEQUENCE table_name_seq OWNED BY table_name.autoinc_field INCREMENT BY 1 START WITH 100000"
    execute "ALTER TABLE table_name ALTER COLUMN autoinc_field SET DEFAULT nextval('table_name_seq');"
  end
end

Model

class Foo < ApplicationRecord
  after_create :reload
end

Result

>> Foo.create!.autoinc_field 
=> 100000
Nix
  • 1