Rails beginner - Best practice for querying multiple tables to retrieve lots of info at once

Question

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?

Here's how the data is structured:

Company has_many clients

Client belongs_to company has_many projects

Project belongs_to client has_many tasks

Task belongs_to project has_many minutes

Minute belongs_to task

---

This data structure might be really bad. I don't know.

An example view of the data:

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

I guess the question can also be asked: Is there a good book or resource that fully describes Rails ActiveRecord best practices?

Answer 1

try something like

 Client.includes(
                      :company =>{:projects=>:tasks})

all of the above should be connected (via has_one, has_many, belongs_to) Hope this helps!

Answer 2

Use the includes method to eagerly load the associations.

Example from the guides

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.

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).

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.

Answer 3

This produces the same you have

@clients = current_user.company.clients
@clients.each do |client| 
    projects = client.projects

    # puts project.name # makes no sense here

    projects.each do |project|

        project.tasks.each do |task|
            puts task.name
        end
    end
end