The next available record identifier

Question

@user = User.new 

@user.id returns nil, but I need to know it before I save it. Is it possible?

Answer 1

No, you cannot get the identifier before saving. The ID number comes from the database, but the database will not assign an identifier until you call save . All of this assumes that you are using ActiveRecord, of course.

Answer 2

Yes, you can!

I had the same question and examined the documents. The ability to resolve this issue is very similar to your database type.

Oracle and Postgresql have useful features to easily solve this problem. For MySQL (oracle) or SkySQL (open-source), this seems more complicated (but still possible). I would recommend that you avoid using these (MySQL / SkySQL) databases if you need advanced database tools.

First, you should try to avoid this situation as much as possible in the design of your application , since it is dangerous to play with identifiers before they are saved.

There may be a situation where you have no other choice: For example, when two tables refer to themselves and for security reasons you do not allow DELETE or UPDATE in these tables.

If so, you can use (postgreSQL, Oracle) nextval database to generate the next ID number without actually inserting a new record.

Use it in conjunction with the find_by_sql method.

To do this using postgreSQL and Rails, for example, select one of your rail models and add a class method (rather than an instance method!). This is possible using the word " self " at the beginning of the method name. self tells Ruby that this method is used only by the class and not by its instance variables (objects created using "new").

My Rails Model:

 class MyToy < ActiveRecord::Base ... def self.my_next_id_sequence self.find_by_sql "SELECT nextval('my_toys_id_seq') AS my_next_id" end end 

When creating a table with Rails migration by default, Rails automatically creates a column called id and sets it as the primary key table. To make sure that you are not getting a “duplicate primary key error”, Rails automatically creates a sequence inside the database and applies it to the id column. For each new record (row) that you insert into your table, the database will calculate by itself what will be the next identifier for your new record.

The rails automatically record this sequence with the table name with the addition of "_id_seq" . The following postgreSQL sequence should apply to this sequence as described here .

Now about find_by_sql , as described here , it will create an array containing instances of the new objects of your class. Each of these objects will contain all the columns created by the SQL statement. These columns will appear in each instance of the new object as attributes. Even if these attributes do not exist in your class model !

As you reasonably understood, our next function returns only one value. Thus, find_by_sql will create an array containing an instance of one object with a single attribute . To make it easier to read the value of this attribute itself, we will call the resulting SQL column "my_next_id", so our attribute will have the same name.

So what is it. We can use our new method:

 my_resulting_array = MyToy.my_next_id_sequence my_toy_object = my_resulting_array[0] my_next_id_value = my_toy_object.my_next_id 

And use it to solve our deadlock situation:

 my_dog = DogModel.create(:name => 'Dogy', :toy_id => my_next_id_value) a_dog_toy = MyToy.new(:my_dog_id => my_dog.id) a_dog_toy.id = my_next_id_value a_dog_toy.save 

Remember that if you do not use your my_next_id_value, this identification number will be lost forever. (I mean that it will not be used by any entry in the future).

The database does not wait when you use it. If somewhere at any time your application should insert a new record in its my_table_example file (possibly simultaneously with the game with my_next_id_sequence), the database will always assign the identifier number of this new record immediately after the one you created using my_next_id_sequence, considering that your my_next_id_value is reserved. This can lead to situations where the entries in your my_table_example file are not sorted by the time they were created.

Answer 3

I had a similar situation. I called the sequence using find_by_sql in my model, which returns an array of the model. I got the identifier from the first arry object. something like below.

 Class User < ActiveRecord::Base set_primary_key 'user_id' alias user_id= id= def self.get_sequence_id self.find_by_sql "select TEST_USER_ID_SEQ.nextval as contact_id from dual" end end 

and in the class that you are referring to the user model,

 @users = User.get_sequence_id user = users[0] 

Answer 4

Typically, the identifier is populated from the database sequence automatically.

In rails, you can use the after_create event, which gives you access to the object immediately after it is saved (and therefore has an identifier). This will cover most cases.

When using Oracle, I had a case when I wanted to create an identifier myself (and not use a sequence), and in this post I specify the details of how I did it. In short code:

 # a small patch as proposed by the author of OracleEnhancedAdapter: http://blog.rayapps.com/2008/05/13/activerecord-oracle-enhanced-adapter/#comment-240 # if a ActiveRecord model has a sequence with name "autogenerated", the id will not be filled in from any sequence ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.class_eval do alias_method :orig_next_sequence_value, :next_sequence_value def next_sequence_value(sequence_name) if sequence_name == 'autogenerated' # we assume id must have gotten a good value before insert! id else orig_next_sequence_value(sequence_name) end end end 

although this solution is specific to Oracle-Enhanced, I assume that other databases will have a similar method that you could override.

So, although this is definitely not recommended, and you want to be absolutely sure why you do not want to use the identifier generated by the sequence, if necessary, this is most definitely possible. That is why I love ruby ​​and Ruby on Rails! :)

Answer 5

In Oracle, you can get the current sequence value with this query:

 SELECT last_number FROM user_sequences where sequence_name='your_sequence_name'; 

So, in your model class, you can add something like this:

 class MyModel < ActiveRecord::Base self.sequence_name = 'your_sequence_name' def self.my_next_id_sequence get_data = self.find_by_sql "SELECT last_number FROM user_sequences where sequence_name='your_sequence_name'" get_data[0].last_number end end 

And finally, in the controller, you can get this value using this:

 my_sequence_number = MyModel.my_next_id_sequence 

So, there is no need to get the next value using NEXTVAL, and you will not lose your identifier.

Answer 6

What you can do is User.max (id). which will return the highest identifier in the database, you can add 1. This is not reliable, although it can satisfy your needs.