Efficient Join queries - Can it be done using 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 which is very obvious ...

<% A.B.each do |b| %>
  <%= b.number %>
  <% b.C.each do |c| %>
    <%= c.name %>
  <% end %>
<% end %>

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

I want to get all 'C' using order_by clause with "created_at" value instead of iterating through B.

May be I'm missing some ActiveRecord magic?

I appreciate any help.

Because of A's relation with C through B, I can also do A.C directly right? — Atarang, Jan 10 '12 at 04:36

score 6 · Accepted Answer · answered Jan 10 '12 at 04:45

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

class C needs an association to D

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

If you want to access A's D's, you need to specify this as well.

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

Now, to access all of A's 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"> 
-> a.C
 => [#<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">]

Notice how another query is not executed when you call a.C. This is because ActiveRecord knows you will want to access the found A's C's by the include call, and generates the minimum number of queries. 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's D's but wanted C's ordered:

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

Note you can also set this as a default on the association:

The :order option dictates the order in which associated objects will be received (in the syntax used by an SQL ORDER BY clause).
class Customer < ActiveRecord::Base
  has_many :orders, :order => "date_confirmed DESC"
end

@iWasRobbed Thanks! B has many smilies through a really sad guy. (Now I can't stop seeing them.) — Michelle Tilley, Jan 10 '12 at 05:51
Brandon Thanks. I'll try this, but I think having associations solves this problem. — Atarang, Jan 10 '12 at 06:16
You can access the child objects without using `includes`, but Rails will generate a lot of queries. You should use `includes` to avoid [the N+1 problem](http://guides.rubyonrails.org/active_record_querying.html#eager-loading-associations). — Michelle Tilley, Jan 10 '12 at 06:19
Brandon, This is indeed a better and faster solution. Thanks. — Atarang, Jan 13 '12 at 09:09

Efficient Join queries - Can it be done using ActiveRecord

1 Answers1