Rails ActiveRecord: Getting the id of a raw insert
activerecord, octopus, ruby-on-rails, sql
Solution
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.
Problem
``` 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 ```