Efficient query aggregation - is it possible with ActiveRecord

Question

I have 4 models, A, B, C and D

class A < ActiveRecord::Base has_many :B has_many :C, :through => :B end class B < ActiveRecord::Base belongs_to :A has_many :C has_many :D, :through => :C end class C < ActiveRecord::Base belongs_to :B end class D < ActiveRecord::Base belongs_to :C end 

I have a very naive implementation that is very obvious ...

 <% ABeach do |b| %> <%= b.number %> <% bCeach do |c| %> <%= c.name %> <% end %> <% end %> 

What is the best way to get All C for A? What is the best way to get All D for A?

I want to get all “C” using the order_by clause with “created_at” instead of repeating through B.

Maybe I miss the magic of ActiveRecord?

I appreciate any help.

Answer 1

First of all, you need to make a couple of changes.

• class C needs an association with D

   class C < ActiveRecord::Base belongs_to :B has_one :D end 

• If you want to access A D , you must also indicate this.

   class A < ActiveRecord::Base has_many :B has_many :C, :through => :B has_many :D, :through => :C end 

Now, to access all A C 's:

 -> a = A.where(:id => 1).includes(:C).first A Load (0.2ms) SELECT "as".* FROM "as" WHERE "as"."id" = 1 LIMIT 1 B Load (0.1ms) SELECT "bs".* FROM "bs" WHERE "bs"."a_id" IN (1) C Load (0.1ms) SELECT "cs".* FROM "cs" WHERE "cs"."b_id" IN (1, 2) => #<A id: 1, created_at: "2012-01-10 04:28:42", updated_at: "2012-01-10 04:28:42"> -> aC => [#<C id: 1, b_id: 1, created_at: "2012-01-10 04:30:10", updated_at: "2012-01-10 04:30:10">, #<C id: 2, b_id: 1, created_at: "2012-01-10 04:30:11", updated_at: "2012-01-10 04:30:11">, #<C id: 3, b_id: 2, created_at: "2012-01-10 04:30:21", updated_at: "2012-01-10 04:30:21">, #<C id: 4, b_id: 2, created_at: "2012-01-10 04:30:21", updated_at: "2012-01-10 04:30:21">] 

Note that another request is not executed when you call aC . This is because ActiveRecord knows that you want to access the found A C calling include and generate the minimum number of queries. The same goes for D s:

 -> a = A.where(:id => 1).includes(:D).first A Load (0.1ms) SELECT "as".* FROM "as" WHERE "as"."id" = 1 LIMIT 1 B Load (0.1ms) SELECT "bs".* FROM "bs" WHERE "bs"."a_id" IN (1) C Load (0.1ms) SELECT "cs".* FROM "cs" WHERE "cs"."b_id" IN (1, 2) D Load (0.1ms) SELECT "ds".* FROM "ds" WHERE "ds"."c_id" IN (1, 2, 3, 4) 

Say you wanted all A D , but wanted C order:

 A.where(:id => 1).includes(:C).order('cs.created_at DESC').includes(:D) 

Note. You can also set this default value in the association:

The :order option determines the order in which related objects will be received (in the syntax used by the SQL ORDER BY ).

 class Customer < ActiveRecord::Base has_many :orders, :order => "date_confirmed DESC" end