Rails ActiveRecord: Getting the id of a raw insert

Question

sql = DmozCategory.send(:sanitize_sql_array, ["INSERT INTO dmoz_categories (id, dmoz_category_title, dmoz_category_name, dmoz_category_description, created_at, updated_at, dmoz_category_lastupdate) VALUES (?, ?, ?, ?, NOW(), NOW(), ?)", result['catid'], result['title'], result['name'], result['description'], result['lastupdate']])

res = DmozCategory.connection.execute(sql)
$stderr.puts res.inspect

res is always nil, even though I can see the DmozCategory inserts into the database. How to get the id following my insert?

I realize that I could use another SQL query SELECT LAST_INSERT_ID() to get the ID, but I was wondering if there was a way to get the id through Rails. M

Background: using Rails 2.3.14

UPDATE: Hmm, I think the problem lays with a plugin I'm using called Octopus. Sorry for discounting some of your answers.. It looks like I need to find how to get the last id of an insert with this plugin. My complete coe:

desc "load all categories from dmoz" # With this one we're loading all the 'structure' table in, not the parent-child relationships.
  task :load_categories_from_dmoz, [ :offset, :limit ] => :environment do |t, args|
    offset = !args[:offset].blank? ? args[:offset].to_i : 0 # Take offset from args.  Default of 0
    limit = !args[:limit].blank? ? args[:limit].to_i : 1 # Take limit from args.  Default of 1
    ActiveRecord::Base.octopus_establish_connection(:adapter=> "mysql", :host=> "localhost", :database => "dmoz", :username => "dmoz", :password => "dmoz")

    results = ActiveRecord::Base.connection.select_all("SELECT * FROM structure LIMIT #{ offset }, #{ limit }") # Fetches it directly from the dmoz database.
    count = offset
    conn = ActiveRecord::Base.octopus_establish_connection(:adapter=> "mysql", :host=> "localhost", :database => "talon_development", :username => "rails_shadow", :password => "husky")
    results.each do |result|
      if count % 1000 == 0
        puts count
      end
      count +=1

      begin
        sql = DmozCategory.send(:sanitize_sql_array, ["INSERT INTO dmoz_categories (id, dmoz_category_title, dmoz_category_name, dmoz_category_description, created_at, updated_at, dmoz_category_lastupdate) VALUES (?, ?, ?, ?, NOW(), NOW(), ?)", result['catid'], result['title'], result['name'], result['description'], result['lastupdate']]) #We leave parent_id NULL for the next task to handle relationships

        DmozCategory.connection.execute(sql) #doesn't get the ID..

      end
    end
  end

Nellnella

try exec_query and last_inserted_id(res) – Loaded

@YuriBarbashov I don't think that's available in Rails 2.3.14? – Nellnella

Aren't you inserting the id yourself? INSERT INTO dmoz_categories (id, ...) VALUES (?, ...), result['catid'], ...? – Mahla

Yes, but I wanted to check that that's working later on. Not sure it has been. – Nellnella

Answer 1

In general instead of using connection.execute, use connection.insert

This will return the generated id of the last inserted row. How it does this is database dependant. On MySQL, the last insert id is a property of the connection. On Postgres a 'returning' clause is appended to the query (with old versions of postgres a fallback asks the sequence for its last id).

Using insert rather than execute also clears the rails query cache.

On MySQL at least, this will work even if you set the id yourself.

Gerome

How can I get the id? DmozCategory.connection.id and DmozCategory.connection.last_id don't seem to have the id i'm inserting – Nellnella

connection.insert "INSERT ..." should return the id (assuming that your id is a regular auto increment column) – Gerome

oh, so I don't need to use .send first? it does both send and execute in one? – Nellnella

See the question for .send, it just gets the SQL. DmozCategory.connection.insert(sql) returns 0, and DmozCategory.connection.id returns 2223123080 even though I can see that it's been inserted into the table with an id of 2. – Nellnella

If you happen to be using a Foreign Data Wrapper on PostgreSQL, then calling #insert will not automatically use RETURNING; however, you can add that the your INSERT command that is run on the remote server and it will return the remote id. See the docs for examples on INSERT ... RETURNING – Inspissate

Answer 2

I think insert_sql method should help you

DmozCategory.connection.insert_sql(sql) # => id

Sawn

Answer 3

try this, that is how AR defines id before insert in 2.3

id = DmozCategory.connection.next_sequence_value(DmozCategory.sequence_name)

Loaded

undefined method next_sequence_value' for #<ActiveRecord::ConnectionAdapters::MysqlAdapter:0x10d1de1e8>` – Nellnella

Answer 4

If this is the mysql2 gem, and DmozCategory.connection is an instance of the client, DmozCategory.connection.last_id will work.

Rattail

I'm using mysql, so undefined method last_id' for #<ActiveRecord::ConnectionAdapters::MysqlAdapter:0x106d82d48>` – Nellnella

Answer 5

Since you know the id already, can't you just do this?

dmoz_category = DmozCategory.find(result['id'])
$stderr.puts dmoz_category.inspect

Barnett

it's starting to look like this is the only way. no clue why, but everything else returns either 0 or a huge/wrong number – Nellnella

Answer 6

Assuming you are using MYSQL and the table auto increments, you could use:

SELECT LAST_INSERT_ID()

to do what it says, grab the last inserted ID into the table. This would add another query to your code, but it seems to stick with your using raw SQL.

Rumal

would prefer the railsy way of doing this if possible.. is that the only way? – Nellnella

You could do something like this: DmozCategory.find(:last).id – Rumal

I'm not sure if that's possible, sorry. – Rumal