[LRUG] Named Scope Problem

[Andrew Stewart]

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}}}}}},
       }
     }
   end

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
--------
http://airbladesoftware.com