1

There are these models:

Patient

Patient has_many MedicalOrders

MedicalOrder

MedicalOrder belongs_to Patient
MedicalOrder has_many Tasks

Task

Task belongs_to MedicalOrder
Task has_many ControlMedicines

ControlMedicine

ControlMedicine belongs_to Task

And there's this block of code to get the actual @patient's control_medicines:

def index
 @control_medicines = []

 @patient.medical_orders.each do |mo|
    mo.tasks.order(created_at: :desc).each do |t|
        t.control_medicines.each do |cm|
            @control_medicines << cm
        end
    end
  end
end

I know it's not the best way to query associated models but haven't figured out how to do it using .includes() method. Mostly because .includes() only works being called to a Class (eg, Patient.includes()) and they're not suitable for nested models, like in this situation.

I've read about preloading, eager_loading and includes but all the examples are limited to get data from two associated models.

Francisco Quintero
  • 730
  • 1
  • 13
  • 20
  • 2
    You might find [this post](http://stackoverflow.com/questions/2383479/ruby-on-rails-multiple-has-many-through-possible) useful regarding nesting of `has_many through`. – lurker Sep 02 '15 at 13:38
  • 1
    `ControlMedicine.joins(:task => :medical_order).order("tasks.created_at DESC").where("medical_orders.patient_id = ?",@patient.id)` should work. `includes` is a query method it is not tied to the class and could be called like `@patient.medical_orders.includes(:tasks => :control_medicines).order("tasks.created_at DESC")` but since you just want the `ControlMedicine`s this route will return `MedicalOrder`s with the tasks and control medicines eager loaded and you would still have to drill down to get them all. – engineersmnky Sep 02 '15 at 13:41
  • @engineersmnky I wouldn't do it that way -- this is a natural fit for the has_many through paradigm in GSPs answer, which allows control_medicines to be sent to a patient instance. – David Aldridge Sep 02 '15 at 13:55
  • 1
    @DavidAldridge the title is about Query optimization not code refactor and this query will actually require 1 less join than the has_many through because it will not need to join the patient table. I agree from a code readability standpoint that `has_many :through` is the cleanest solution but my suggestion still fully applies to query optimization. – engineersmnky Sep 02 '15 at 13:59
  • @engineersmnky Both GSPs and your code modify the original, so they're both refactoring. GSPs refactoring represents a better Rails idiom. (GSP's generated query is incorrect I think, and I edited it to remove the inclusion of "patients", so performance is not affected). – David Aldridge Sep 02 '15 at 14:16
  • @engineersmnky your way does the job. If you'd mind adding it as an answer, I would mark it. Both GSPs and your solution work. – Francisco Quintero Sep 02 '15 at 14:25
  • @Francisco If this was your code base, which method would you use? – David Aldridge Sep 02 '15 at 14:26
  • @Francisco I don't believe it is more optimised. GSPs inclusion of the patients table in the expected query was an error, I think. – David Aldridge Sep 02 '15 at 14:51
  • @DavidAldridge I know. I just double checked both queries and they're the same. They both do the job. – Francisco Quintero Sep 02 '15 at 19:13

1 Answers1

3

You can use the has_many through in Rails to allow ActiveRecord to make your joins for you.

class Patient < ActiveRecord::Base
  has_many :medical_orders
  has_many :tasks, through: :medical_orders
  has_many :control_medicines, through: :tasks
end

Writing your query like:

@patient.control_medicines

Generates SQL like: 

SELECT "control_medicines".* FROM "control_medicines" 
INNER JOIN "tasks" ON "tasks"."id" = "control_medicines"."task_id" 
INNER JOIN "medical_orders" ON "medical_orders"."id" = "tasks"."medical_order_id" 
WHERE "medical_orders.patient_id"  = $1  [["id", 12345]]
David Aldridge
  • 51,479
  • 8
  • 68
  • 96
GSP
  • 3,763
  • 3
  • 32
  • 54
  • 2
    Yes this is the correct way, except that you might also add scope(s) to order the classes, and merge those scopes into the relations, so that the order in which the control medicines are returned is deterministic. That might be important for the user experience. – David Aldridge Sep 02 '15 at 13:57
  • Oh your sample generated query should not include the patients table, I think. At least on Rails 4.2.1 it doesn't. – David Aldridge Sep 02 '15 at 14:13