Module: ActiveRecord::Extensions::ConnectionAdapters

Defined in:
lib/ar-extensions/insert_select.rb,
lib/ar-extensions/import.rb

Overview

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

Defined Under Namespace

Modules: MysqlAdapter, SQLiteAdapter