如何根据查询插入多行?
我正在开发一个具有文件夹、组和权限的系统.权限决定了不同组可以在每个文件夹中执行的操作.因此，每当我创建一个新组时，我都想在每个文件夹的权限表中添加一条记录，描述新组在该文件夹中可以做什么.
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 运行上面的查询，但这感觉不对，因为我是 INSERTing，而不是 SELECTing.
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?
提前致谢.
推荐答案
这就是我在 rails/activerecord 中处理自定义 sql 的方式(包括 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 行.但是，如果您的查询简单且索引正确，则可以轻松扩展到数百万行.
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.
相关文章