Count across a union of two or more queries

Args

• column_name - The column to count. Defaults to all (’*’)
• *args - Each additional argument is a hash map of options used by :find :all

including :conditions, :join, :group, :having, and :limit

In addition the following options are accepted

• :pre_sql inserts SQL before the SELECT statement of this protion of the union
• :post_sql appends additional SQL to the end of the statement
• :override_select is used to override the SELECT clause of eager loaded associations

Note that distinct is implied so a record that matches more than one portion of the union is counted only once.

Global Options

To specify global options that apply to the entire union, specify a hash as the first parameter with a key :union_options. Valid options include :group, :having, :order, and :limit

Examples

Count the number of people who live in Seattle and San Francisco

 Contact.count_union(:phone_number_id,
       {:conditions => ['zip_id = ?, 94010]'},
       {:conditions => ['zip_id = ?', 98102]})
 SQL> select count(*) from ((select phone_number_id from contacts ...) UNION (select phone_number_id from contacts ...)) as counter_tbl;

Creates a temporary table given the passed in options hash. The temporary table is created based off from another table the current model class. This method returns the constant for the new new model. This can also be used with block form (see below).

Parameters

• options - the options hash used to define the temporary table.

Options

• :table_name - the desired name of the temporary table. If not supplied # then a name of "temp_" + the current table_name of the current model # will be used.
• :like - the table model you want to base the temporary tables # structure off from. If this is not supplied then the table_name of the # current model will be used.
• :model_name - the name of the model you want to use for the temporary # table. This must be compliant with Ruby‘s naming conventions for # constants. If this is not supplied a rails-generated table name will # be created which is based off from the table_name of the temporary table. # IE: Account.create_temporary_table creates the TempAccount model class

Example 1, using defaults

 class Project < ActiveRecord::Base ; end

 Project.create_temporary_table

This creates a temporary table named ‘temp_projects’ and creates a constant name TempProject. The table structure is copied from the projects table.

Example 2, using :table_name and :model options

  Project.create_temporary_table :table_name=>'my_projects', :model=>'MyProject'

This creates a temporary table named ‘my_projects’ and creates a constant named MyProject. The table structure is copied from the projects table.

Example 3, using :like

  ActiveRecord::Base.create_temporary_table :like=>Project

This is the same as calling Project.create_temporary_table.

Example 4, using block form

  Project.create_temporary_table do |t|
    # ...
  end

Using the block form will automatically drop the temporary table when the block exits. t which is passed into the block is the temporary table class. In the above example t equals TempProject. The block form can be used with all of the available options.

See

• drop

Delete all specified records with options

Parameters

• conditions - the conditions normally specified to delete_all
• options - hash map of additional parameters

Options

• :limit - the maximum number of records to delete.
• :batch - delete in batches specified to avoid database contention

Multiple sql deletions are executed in order to avoid database contention This has no affect if used inside a transaction

Delete up to 65 red tags

 Tag.delete_all ['name like ?', '%red%'], :limit => 65

Delete up to 65 red tags in batches of 20. This will execute up to 4 delete statements: 3 batches of 20 and the final batch of 5.

 Tag.delete_all ['name like ?', '%red%'], :limit => 65, :batch => 20

Utility function to delete all but one of the duplicate records matching the fields specified. This method will make the records unique for the specified fields.

Options

• :fields - the fields to match on
• :conditions - additional conditions
• :winner_clause - the part of the query specifying what wins. Default winner is that with the greatest id.
• :query_field -> the field to use to determine the winner. Defaults to primary_key (id). The tables are aliased

 to c1 and c2 respectively

Examples

Make all the phone numbers of contacts unique by deleting the duplicates with the highest ids

  Contacts.delete_duplicates(:fields=>['phone_number_id'])

Delete all tags that are the same preserving the ones with the highest id

  Tag.delete_duplicates :fields => [:name], :winner_clause => "c1.id < c2.id"

Remove duplicate invitations (those that from the same person and to the same recipient) preseving the first ones inserted

 Invitation.delete_duplicates :fields=>[:event_id, :from_id, :recipient_id]

Find a union of two or more queries

Args

Each argument is a hash map of options sent to :find :all including :conditions, :join, :group, :having, and :limit

In addition the following options are accepted

• :pre_sql inserts SQL before the SELECT statement of this protion of the union
• :post_sql appends additional SQL to the end of the statement
• :override_select is used to override the SELECT clause of eager loaded associations

Examples

Find the union of a San Fran zipcode with a Seattle zipcode

   union_args1 = {:conditions => ['zip_id = ?', 94010], :select => :phone_number_id}
   union_args2 = {:conditions => ['zip_id = ?', 98102], :select => :phone_number_id}
   Contact.find_union(union_args1, union_args2, ...)

   SQL>  (SELECT phone_number_id FROM contacts WHERE zip_id = 94010) UNION
         (SELECT phone_number_id FROM contacts WHERE zip_id = 98102) UNION ...

Global Options

To specify global options that apply to the entire union, specify a hash as the first parameter with a key :union_options. Valid options include :group, :having, :order, and :limit

Example:

 Contact.find_union(:union_options => {:limit => 10, :order => 'created_on'},
 union_args1, union_args2, ...)

 SQL> ((select phone_number_id from contacts ...) UNION (select phone_number_id from contacts ...)) order by created_on limit 10

Adds fulltext searching capabilities to the current model for the given fulltext key and option hash.

Parameters

• fulltext_key - the key/attribute to be used to as the fulltext index
• options - the options hash.

Options

• fields - an array of field names to be used in the fulltext search

Example

 class Book < ActiveRecord::Base
   fulltext :title, :fields=>%W( title publisher author_name )
 end

 # To use the fulltext index
 Book.find :all, :conditions=>{ :match_title => 'Zach' }

Imports a collection of values to the database.

This is more efficient than using ActiveRecord::Base#create or ActiveRecord::Base#save multiple times. This method works well if you want to create more than one record at a time and do not care about having ActiveRecord objects returned for each record inserted.

This can be used with or without validations. It does not utilize the ActiveRecord::Callbacks during creation/modification while performing the import.

Usage

 Model.import array_of_models
 Model.import column_names, array_of_values
 Model.import column_names, array_of_values, options

Model.import array_of_models

With this form you can call import passing in an array of model objects that you want updated.

Model.import column_names, array_of_values

The first parameter column_names is an array of symbols or strings which specify the columns that you want to update.

The second parameter, array_of_values, is an array of arrays. Each subarray is a single set of values for a new record. The order of values in each subarray should match up to the order of the column_names.

Model.import column_names, array_of_values, options

The first two parameters are the same as the above form. The third parameter, options, is a hash. This is optional. Please see below for what options are available.

Options

• validate - true|false, tells import whether or not to use # ActiveRecord validations. Validations are enforced by default.
• on_duplicate_key_update - an Array or Hash, tells import to # use MySQL‘s ON DUPLICATE KEY UPDATE ability. See On Duplicate # Key Update below.
• synchronize - an array of ActiveRecord instances for the model that you are currently importing data into. This synchronizes existing model instances in memory with updates from the import.
• timestamps - true|false, tells import to not add timestamps # (if false) even if record timestamps is disabled in ActiveRecord::Base

Examples

 class BlogPost < ActiveRecord::Base ; end

 # Example using array of model objects
 posts = [ BlogPost.new :author_name=>'Zach Dennis', :title=>'AREXT',
           BlogPost.new :author_name=>'Zach Dennis', :title=>'AREXT2',
           BlogPost.new :author_name=>'Zach Dennis', :title=>'AREXT3' ]
 BlogPost.import posts

 # Example using column_names and array_of_values
 columns = [ :author_name, :title ]
 values = [ [ 'zdennis', 'test post' ], [ 'jdoe', 'another test post' ] ]
 BlogPost.import columns, values

 # Example using column_names, array_of_value and options
 columns = [ :author_name, :title ]
 values = [ [ 'zdennis', 'test post' ], [ 'jdoe', 'another test post' ] ]
 BlogPost.import( columns, values, :validate => false  )

 # Example synchronizing existing instances in memory
 post = BlogPost.find_by_author_name( 'zdennis' )
 puts post.author_name # => 'zdennis'
 columns = [ :author_name, :title ]
 values = [ [ 'yoda', 'test post' ] ]
 BlogPost.import posts, :synchronize=>[ post ]
 puts post.author_name # => 'yoda'

On Duplicate Key Update (MySQL only)

The :on_duplicate_key_update option can be either an Array or a Hash.

Using an Array

The :on_duplicate_key_update option can be an array of column names. The column names are the only fields that are updated if a duplicate record is found. Below is an example:

  BlogPost.import columns, values, :on_duplicate_key_update=>[ :date_modified, :content, :author ]

Using A Hash

The :on_duplicate_key_update option can be a hash of column name to model attribute name mappings. This gives you finer grained control over what fields are updated with what attributes on your model. Below is an example:

  BlogPost.import columns, attributes, :on_duplicate_key_update=>{ :title => :title }

Returns

This returns an object which responds to failed_instances and num_inserts.

• failed_instances - an array of objects that fails validation and were not committed to the database. An empty array if no validation is performed.
• num_inserts - the number of insert statements it took to import the data

Imports the passed in column_names and array_of_attributes given the passed in options Hash with validations. Returns an object with the methods failed_instances and num_inserts. failed_instances is an array of instances that failed validations. num_inserts is the number of inserts it took to import the data. See ActiveRecord::Base.import for more information on column_names, array_of_attributes and options.

Imports the passed in column_names and array_of_attributes given the passed in options Hash. This will return the number of insert operations it took to create these records without validations or callbacks. See ActiveRecord::Base.import for more information on column_names, +array_of_attributes_ and options.

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)

Returns an array of quoted column names

Returns true if the current connection adapter supports full text searching, otherwise returns false.

Returns true if the current database connection adapter supports import functionality, otherwise returns false.

Returns true if the current database connection adapter supports on duplicate key update functionality, otherwise returns false.

Returns true if the underlying database connection supports temporary tables

Synchronizes the passed in ActiveRecord instances with data from the database. This is like calling reload on an individual ActiveRecord instance but it is intended for use on multiple instances.

This uses one query for all instance updates and then updates existing instances rather sending one query for each instance

See ActiveRecord::ConnectionAdapters::AbstractAdapter.synchronize