Rails 初学者 - 查询多个表以一次检索大量信息的最佳实践 [英] Rails beginner - Best practice for querying multiple tables to retrieve lots of info at once
问题描述
我是 Rails 初学者，为了学习它，我正在构建一个简单的时间跟踪应用程序.我想用嵌套信息的许多表中的大量信息填充管理员的仪表板.
I'm a Rails beginner and to learn it I'm building a simple time tracking app. I want to populate an administrator's dashboard with a ton of information from many tables with nested information.
查询数据库以请求一家公司查看所有客户、项目、任务、调整和会议记录的仪表板的所有数据的最佳做法是什么?
What would be the best practice for querying the database to request all of the data for one company to view a dashboard of all clients, projects, tasks, adjustments and minutes?
以下是数据的结构:
公司有_许多客户
客户所属公司有_许多项目
Client belongs_to company has_many projects
项目属于客户有_许多任务
Project belongs_to client has_many tasks
任务所属项目有_很多分钟
Task belongs_to project has_many minutes
分钟属于任务
这个数据结构可能真的很糟糕.我不知道.
This data structure might be really bad. I don't know.
数据的示例视图:
动视
-- 网站改版
--- 开发
---- 100 分钟
Activision
-- Website Redesign
--- Development
---- 100 Minutes
我从这个开始，但我很漂亮，但它可能完全倒退(用户属于公司):
I'm starting with this but I'm pretty but it could be totally backwards (Users belong to Companies):
@clients = Client.find_all_by_company_id(current_user.company_id)
@clients.each do |client|
project = Project.find_all_by_client_id(client.id)
puts project.name
project.each do |project|
task = Task.find_all_by_project_id(project.id)
puts task.name
end
end
我想这个问题也可以问:是否有一本好书或资源可以完整描述 Rails ActiveRecord 最佳实践?
I guess the question can also be asked: Is there a good book or resource that fully describes Rails ActiveRecord best practices?
推荐答案
使用 includes 方法来急切地加载关联.
Use the includes method to eagerly load the associations.
来自指南
Category.includes(:posts => [{:comments => :guest}, :tags]).find(1)
根据你所说的，应该是:
Based on what you said, that should be:
require 'active_record'
require 'logger'
# ===== Config =====
ActiveRecord::Base.establish_connection adapter: 'sqlite3', database: ':memory:'
ActiveRecord::Base.logger = Logger.new $stdout
ActiveSupport::LogSubscriber.colorize_logging = false
# ===== Schema =====
ActiveRecord::Schema.define do
self.verbose = false
create_table :clients do |t|
t.string :name
t.integer :company_id
end
create_table :companies do |t|
t.string :name
end
create_table :projects do |t|
t.string :name
t.integer :client_id
end
create_table :tasks do |t|
t.string :name
t.integer :project_id
end
create_table :minutes do |t|
t.integer :quantity
t.integer :task_id
end
end
# ===== Classes =====
class Company < ActiveRecord::Base
has_many :clients
end
class Client < ActiveRecord::Base
belongs_to :company
has_many :projects
end
class Project < ActiveRecord::Base
belongs_to :client
has_many :tasks
end
class Task < ActiveRecord::Base
belongs_to :project
has_many :minutes
end
class Minute < ActiveRecord::Base
belongs_to :task
end
# ===== Data =====
Company.create! name: 'Activision' do |company|
company.clients.build name: 'Robert Kotick' do |client|
client.projects.build name: 'Website Redesign' do |project|
project.tasks.build name: 'Development' do |task|
task.minutes.build quantity: 100
end
end
end
end
# ===== Querying and displaying =====
company = Company.find_by_name 'Activision'
clients = Client.includes(projects: {tasks: :minutes}).where(company_id: company.id)
print "\n----- The query makes four requests, regardless of how much data you have. -----\n\n"
clients.inspect # do this to force loading since AR queries are lazy
print "\n----- some representation of the data (notice no queries while iterating through) -----\n\n"
clients.each do |client|
puts client.name
client.projects.each do |project|
puts "-- #{project.name}"
project.tasks.each do |task|
puts "--- #{task.name}"
task.minutes.each do |minute|
puts "---- #{minute.quantity}"
end
end
end
end
# ===== Output =====
# >> D, [2012-09-12T00:01:42.755414 #72855] DEBUG -- : (0.7ms) select sqlite_version(*)
# >> D, [2012-09-12T00:01:42.755890 #72855] DEBUG -- : (0.2ms) CREATE TABLE "clients" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "name" varchar(255), "company_id" integer)
# >> D, [2012-09-12T00:01:42.756327 #72855] DEBUG -- : (0.1ms) CREATE TABLE "companies" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "name" varchar(255))
# >> D, [2012-09-12T00:01:42.756728 #72855] DEBUG -- : (0.1ms) CREATE TABLE "projects" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "name" varchar(255), "client_id" integer)
# >> D, [2012-09-12T00:01:42.757122 #72855] DEBUG -- : (0.1ms) CREATE TABLE "tasks" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "name" varchar(255), "project_id" integer)
# >> D, [2012-09-12T00:01:42.757531 #72855] DEBUG -- : (0.1ms) CREATE TABLE "minutes" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "quantity" integer, "task_id" integer)
# >> D, [2012-09-12T00:01:42.906877 #72855] DEBUG -- : (0.0ms) begin transaction
# >> D, [2012-09-12T00:01:42.909242 #72855] DEBUG -- : SQL (0.5ms) INSERT INTO "companies" ("name") VALUES (?) [["name", "Activision"]]
# >> D, [2012-09-12T00:01:42.934937 #72855] DEBUG -- : SQL (24.7ms) INSERT INTO "clients" ("company_id", "name") VALUES (?, ?) [["company_id", 1], ["name", "Robert Kotick"]]
# >> D, [2012-09-12T00:01:42.936110 #72855] DEBUG -- : SQL (0.1ms) INSERT INTO "projects" ("client_id", "name") VALUES (?, ?) [["client_id", 1], ["name", "Website Redesign"]]
# >> D, [2012-09-12T00:01:42.937001 #72855] DEBUG -- : SQL (0.1ms) INSERT INTO "tasks" ("name", "project_id") VALUES (?, ?) [["name", "Development"], ["project_id", 1]]
# >> D, [2012-09-12T00:01:42.937767 #72855] DEBUG -- : SQL (0.1ms) INSERT INTO "minutes" ("quantity", "task_id") VALUES (?, ?) [["quantity", 100], ["task_id", 1]]
# >> D, [2012-09-12T00:01:42.938005 #72855] DEBUG -- : (0.0ms) commit transaction
# >> D, [2012-09-12T00:01:42.939882 #72855] DEBUG -- : Company Load (0.1ms) SELECT "companies".* FROM "companies" WHERE "companies"."name" = 'Activision' LIMIT 1
# >>
# >> ----- The query makes four requests, regardless of how much data you have. -----
# >>
# >> D, [2012-09-12T00:01:42.940458 #72855] DEBUG -- : Client Load (0.1ms) SELECT "clients".* FROM "clients" WHERE "clients"."company_id" = 1
# >> D, [2012-09-12T00:01:42.943272 #72855] DEBUG -- : Project Load (0.1ms) SELECT "projects".* FROM "projects" WHERE "projects"."client_id" IN (1)
# >> D, [2012-09-12T00:01:42.943919 #72855] DEBUG -- : Task Load (0.1ms) SELECT "tasks".* FROM "tasks" WHERE "tasks"."project_id" IN (1)
# >> D, [2012-09-12T00:01:42.944520 #72855] DEBUG -- : Minute Load (0.1ms) SELECT "minutes".* FROM "minutes" WHERE "minutes"."task_id" IN (1)
# >>
# >> ----- some representation of the data (notice no queries while iterating through) -----
# >>
# >> Robert Kotick
# >> -- Website Redesign
# >> --- Development
# >> ---- 100
这是一个可怕的德米特法则违反，如果这些事情在任何时候发生变化，无论是在它们的结构还是命名上，我们都必须来修复此代码.我不太确定如何在不引入大量抽象的情况下处理这个问题.
This is a horrible Law of Demeter violation, if any of these things change at any point, whether in their structure or naming, we will have to come fix this code. I'm not really sure how to deal with that without introducing lots of abstractions.
关于一本书，已经有很多了，但老实说，我认为 Rails 世界还没有弄清楚什么是最好的 ActiveRecord 实践(事实上，社区中有很大一部分人认为几乎所有的 ActiveRecord 实践都只是太糟糕了——我大部分时间都在那个营地).
Regarding a book, there have been many, but I honestly don't think the Rails world has figured out yet what constitute best ActiveRecord practices (in fact, there's a large portion of the community that thinks almost all ActiveRecord practices are just terrible -- I'm mostly in that camp).
但是，如果您想要类似上述内容，即使用 #includes 来预先加载关联，那么指南是查找此类信息的好地方.我也非常喜欢这个博客和视频.
But if you want things like the above, which says to use #includes to eager load associations, then the guides are a great place to find out information like that. I also really enjoyed this blog and videos.
这篇关于Rails 初学者 - 查询多个表以一次检索大量信息的最佳实践的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
