How to insert multiple rows based on a query?

I am developing a system that has folders, groups and permissions. Permissions determine what the different groups can do in each folder. Therefore whenever I create a new group, I want to add a record to the permissions table for each folder, describing what the new group can do in that folder.

Currently I am just looping through all the folders in the system and adding a permissions record for each folder:

group = Group.create(params)

Folder.all.each do |folder|
  Permission.create! do |permission|
    permission.folder = folder
    permission.group = group
    permission.can_create = true
    permission.can_read = true
    permission.can_update = true
    permission.can_delete = true
  end
end

I don't like the fact that I have to loop through all the records everytime I create a new group. So basically I am looking for an elegant way to execute the following SQL using ActiveRecord.

INSERT INTO permissions (folder_id, group_id, can_creat, can_read, can_update, 开发者_开发知识库can_delete)
SELECT id, #{group.id}, true, true, true, true
FROM folders

I guess I could run the above query using find_by_sql, but that doesn't feel right, cause I am INSERTing, not SELECTing.

Or should I just forget about this and keep looping through my folder records like in the example above?

Thanks in advance.

What you are looking for is ar-extensions

Install the gem using

sudo gem install ar-extensions

Include the gem in your environment.rb (Or directly in the model you want to do inserts with)

require 'ar-extensions'

And insert multiple records in one INSERT query using

fields = [:first_name, :last_name, :email]
data = [["glenn", "gillen", "foo@bar.com"],
       ["john", "jones", "jim@bar.com"],
       ["steve", "smith", "bar@foo.com"]]

User.import fields, data

You can do it using ActiveRecord objects too.

data = [ 
         User.new(:first_name => 'glenn', :last_name => 'gillen', :email => 'foo@bar.com'),
         User.new(:first_name => 'john', :last_name => 'jones', :email => 'jim@bar.com'),
         User.new(:first_name => 'steve', :last_name => 'smith', :email => 'bar@foo.com')
       ]

User.import fields, data

3 new rows have been inserted into the users table, with just the single query!

More about it here, here and here.

This is how I deal with custom sql in rails/activerecord (after_create trick included!)

class Group < ActiveRecord::Base
  after_create :create_default_folder_permissions

  def create_default_folder_permissions
    sql = <<-SQL
     INSERT INTO permissions (folder_id, group_id, can_creat, can_read, can_update, can_delete)
        SELECT id, #{id}, true, true, true, true FROM folders
    SQL
    connection.execute(sql)
  end
end

However adding permission for each group and each folder can soon become a bottleneck since you get number_of_groups * number_of_folders rows in permissions table. But if you your queries are simple and indexes right you can easily scale to milions of rows.

Are your searching for the most "ruby'ish elegant" way of doing things, or the fastest? Why don't you directly call your SQL query from activerecord? Another solution would be to put your query inside a database function, and call that function from your web app. Maybe a little harder to maintain, but an incredible performance boost, especially when you start having a lot of folders in your web app.