如何插入基于查询的多个行？ [英] 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

我不喜欢的事实，我必须遍历所有的记录，每次我创建一个新组。所以基本上我要寻找使用的ActiveRecord执行下面的SQL一种优雅的方式。

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

我想我可以使用运行上面的查询的find_by_sql ，但感觉不对，因为我插入，不是选择。

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?

在此先感谢。

推荐答案

这是我如何处理在轨自定义的SQL / ActiveRecord的（包括after_create绝招！）

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

但是添加权限每个组和每个文件夹可很快成为一个瓶颈，因为你得到 number_of_groups *在权限表number_of_folders 行。但是，如果你的查询都是简单和索引的权利，你可以很容易地扩展到行milions。

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.

这篇关于如何插入基于查询的多个行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！