[LRUG] Named Scope Problem

Andrew Stewart boss at airbladesoftware.com
Wed Oct 14 08:13:50 PDT 2009

Hi El Rug,

I have a typical subdomain-based Rails app where the subdomain  
corresponds to an account.  The rest of my models hang off the  
account; the longest chain is five models long:

   Account -> A -> B -> C -> D -> E      # "->" means has many.

I'm trying to write named_scopes for each class which restrict  
instances to the top-level account instance of the current user.  They  
seem to work but they:

(a) generate left outer joins instead of the inner joins I would  
expect; and
(b) generate two joins per relation instead of one.

For example, my E class looks like:

   class E < ActiveRecord::Base
     named_scope :silo, lambda { |account_id|
         :select => 'distinct e.*',
         :joins => {:d => {:c => {:b => {:a => :account}}}},
         :conditions => {:d => {:c => {:b => {:a => {:account => {:id  
=> account_id}}}}}},

And E.silo(6), for example, produces:

SELECT distinct e.* FROM `e`
LEFT OUTER JOIN `d` ON `d`.id = `e`.d_id
LEFT OUTER JOIN `c` ON `c`.id = `d`.c_id
LEFT OUTER JOIN `b` ON `b`.id = `c`.b_id
LEFT OUTER JOIN `a` ON `a`.id = `v`.a_id
LEFT OUTER JOIN `accounts` ON `accounts`.id = `a`.account_id
LEFT OUTER JOIN `d` d_e ON `d_e`.id = `e`.d_id
LEFT OUTER JOIN `c` c_d ON `c_d`.id = `d_e`.c_id
LEFT OUTER JOIN `b` b_c ON `b_c`.id = `c_d`.b_id
LEFT OUTER JOIN `a` a_b ON `a_b`.id = `b_c`.a_id
LEFT OUTER JOIN `accounts` a2 ON `a2`.id = `a_b`.account_id
WHERE (`accounts`.`id` = 6)

(I may have made a typo somewhere in anonymising this ;)

Anyway, how to get ActiveRecord to use the more efficient and correct  
inner join, and how to avoid the duplicating of joins?

Thanks in advance,

Andy Stewart

More information about the Chat mailing list