[LRUG] Named Scope Problem

[Matthew Rudy Jacobs]

If I were you I'd write the :joins as a string.
I don't think the nested hash conditions syntax is very clear anyway,
(especially with 5 levels of nesting)

2009/10/14 Andrew Stewart <boss at airbladesoftware.com>:
> 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
> _______________________________________________
> Chat mailing list
> Chat at lists.lrug.org
> http://lists.lrug.org/listinfo.cgi/chat-lrug.org
>