insert_select.rb

Path: ar-extensions/lib/ar-extensions/insert_select.rb
Last Update: Fri Apr 17 13:45:54 -0600 2009

Insert records in bulk with a select statement

Parameters

  • options - the options used for the finder sql (select)

Options

Any valid finder options (options for ActiveRecord::Base.find(:all) )such as :joins, :conditions, :include, etc including:

  • :from - the symbol, class name or class used for the finder SQL (select)
  • :on_duplicate_key_update - an array of fields to update, or a custom string
  • :select - An array of fields to select or custom string. The SQL will be sanitized and ? replaced with values as with :conditions.
  • :ignore => true - will ignore any duplicates
  • :into - Specifies the columns for which data will be inserted. An array of fields to select or custom string.

Examples

Create cart items for all books for shopping cart <tt>@cart+ setting the copies field to 1, the updated_at field to Time.now and the created_at field to the database function now() 

 CartItem.insert_select(:from => :book,
                        :select => ['books.id, ?, ?, ?, now()', @cart.to_param, 1, Time.now],
                        :into => [:book_id, :shopping_cart_id, :copies, :updated_at, :created_at]})

GENERATED SQL example (MySQL): 

 INSERT INTO `cart_items` ( `book_id`, `shopping_cart_id`, `copies`, `updated_at`, `created_at` )
 SELECT books.id, '134', 1, '2009-03-02 18:28:25', now() FROM `books`

A similar example that

  • uses the class Book instead of symbol :book
  • a custom string (instead of an Array) for the :select of the insert_options
  • Updates the updated_at field of all existing cart item. This assumes there is a unique composite index on the book_id and shopping_cart_id fields 
 CartItem.insert_select(:from => Book,
                        :select => ['books.id, ?, ?, ?, now()', @cart.to_param, 1, Time.now],
                        :into => 'cart_items.book_id, shopping_cart_id, copies, updated_at, created_at',
                        :on_duplicate_key_update => [:updated_at])

GENERATED SQL example (MySQL): 

   INSERT INTO `cart_items` ( cart_items.book_id, shopping_cart_id, copies, updated_at, created_at )
   SELECT books.id, '138', 1, '2009-03-02 18:32:34', now() FROM `books`
          ON DUPLICATE KEY UPDATE `cart_items`.`updated_at`=VALUES(`updated_at`)

Similar example ignoring duplicates 

 CartItem.insert_select(:from => :book,
                        :select => ['books.id, ?, ?, ?, now()', @cart.to_param, 1, Time.now],
                        :into => [:book_id, :shopping_cart_id, :copies, :updated_at, :created_at],
                        :ignore => true)

Developers

Homepage

[Validate]