mysql - How to insert multiple rows based on a query?

Question

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.

Answer 1

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", "[email protected]"],
       ["john", "jones", "[email protected]"],
       ["steve", "smith", "[email protected]"]]

User.import fields, data

You can do it using ActiveRecord objects too.

data = [ 
         User.new(:first_name => 'glenn', :last_name => 'gillen', :email => '[email protected]'),
         User.new(:first_name => 'john', :last_name => 'jones', :email => '[email protected]'),
         User.new(:first_name => 'steve', :last_name => 'smith', :email => '[email protected]')
       ]

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.