CODEWITHSUNDEEP
sqlruby-on-railsactiverecord
liquorishe
liquorishe

Reputation: 67

How do I chain multiple models to reduce the number of SQL queries

I'm trying to check whether a student has attempted an assigned test or not. I want to chain the relevant models to bring down the number of queries to just 1. The following are my models:

class Test < ActiveRecord::Base
  has_many   :assigns
  has_many   :attempts
  belongs_to :topic
end

class Topic < ActiveRecord::Base
  has_many    :tests
  has_many    :attempts
  has_many    :assigns, through: :test
end

class Assign < ActiveRecord::Base
  belongs_to  :test
  belongs_to  :student
  has_many    :attempts
end

class Attempt < ActiveRecord::Base
  belongs_to  :test
  belongs_to  :topic
  belongs_to  :assign
  belongs_to  :student
end

I want to check if a particular student (id: 100) has attempted an assigned test or not, and also retrieve other details such as the topic name of the test. So far I have something like this:

ta = Assign.includes(:test => {:topic => :attempts})

This allows me to retrieve details such as the test_name, topic_name, when it was assigned etc. in a single query. How do I also include the Attempt records of student_id: 100 in the same query? With what I have now, when I retrieve the student's attempt details a brand new query is being generated.

What I want is something like the follwoing without having to touch the database again:

ta.test.attempts.where(student_id: 100)

How do I do all this with just one query?

Upvotes: 2

Views: 170

Answers (1)

lusketeer
lusketeer

Reputation: 1930

Okay, since you want all kinds of information from all the joined tables, so you will have to join them up from the beginning.

Attempt.joins(:topic, :test, :assign)

Then you can filter it with the student_id

.where("attempts.student_id" => 100)

Finally, the fields you want

.select("attempts.id as attempt_id, tests.name as test_name, topics.name as topic_name, assigns.created_at as assigned_at")

In summary 

Attempt
    .joins(:topic, :test, :assign)
    .where("attempts.student_id" => 100)
    .select("attempts.id as attempt_id, tests.name as test_name, topics.name as topic_name, assigns.created_at as assigned_at")

Upvotes: 1

Related Questions