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.

Community · Accepted Answer · 2020-06-20 09:12:55Z

9

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.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Dec 2, 2010 at 9:19

Chirantan

15.7k8 gold badges52 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mischa Over a year ago

Thanks, sounds like what I am looking for! I will look at it tomorrow and give you the bounty if it does what I need it to do.

johno Over a year ago

This is very good for mass insertion from ruby/rails. But, you basically want mass insert from another table.

Mischa Over a year ago

johno seems to be right. This doesn't seem to be able to what I have described above. Sorry, but looks like I have to go with johno's answer.

johno · Accepted Answer · 2010-12-01 18:41:06Z

3

+50

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.

answered Dec 1, 2010 at 18:41

johno

8521 gold badge7 silver badges9 bronze badges

1 Comment

Mischa Over a year ago

Thanks. I had a look at ar-extensions and you are right: it does not let me import records based on a query. I will accept your answer and give you the bounty, but there is one more thing question I have. I want this application to work with different kind of databases and Rails' boolean datatype becomes tinyint(1) in MySQL. So SELECT id, #{id}, 'true', 'true', 'true', 'true' FROM folders would fail, because MySQL expects 0 or 1, instead of true or false. Any suggestions?

plang · Accepted Answer · 2010-11-25 22:13:05Z

1

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.

answered Nov 25, 2010 at 22:13

plang

5,6643 gold badges29 silver badges37 bronze badges

1 Comment

Mischa Over a year ago

Thanks for your answer. I guess the simplest would be to call SQL from ActiveRecord. I have used find_by_sql before, but it doesn't seem appropriate since I am not SELECTING, but INSERTING. If you have any code samples I'd appreciate it. In the mean time I will start a small bounty to see if I can get some more suggestions.

Collectives™ on Stack Overflow

How to insert multiple rows based on a query?

3 Answers 3

What you are looking for is `ar-extensions`

3 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

What you are looking for is ar-extensions

3 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related

What you are looking for is `ar-extensions`