Rails ActiveRecord：获取原始插入的ID [英] Rails ActiveRecord: Getting the id of a raw insert

问题描述

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 总是 nil ，即使我可以看到DmozCategory插入到数据库中。如何在插入后获取 id ？

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

我意识到我可以使用另一个SQL查询 SELECT LAST_INSERT_ID（）来获取ID，但是我想知道是否有办法通过Rails获取ID。 M

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

背景：使用Rails 2.3.14

Background: using Rails 2.3.14

更新：嗯，我认为问题出在我正在使用的名为八达通的插件中。很抱歉打折您的一些答案。.看来我需要找到如何使用此插件获取插入的最后一个ID。我完整的命令：

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

推荐答案

通常，使用<$代替使用 connection.execute c $ c> connection.insert

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

这将返回最后插入的行的生成ID。它如何执行取决于数据库。在MySQL上，最后一个插入ID是连接的属性。在Postgres上，查询中附加了一个 returning子句（对于旧版本的postgres，后退会询问序列的最后一个id）。

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).

使用插入而不是同时执行清除rails查询缓存。

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

至少在MySQL上，这将起作用即使您自己设置了ID 。

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

这篇关于Rails ActiveRecord：获取原始插入的ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！