Has_Many :Through or :finder_sql

Question

I've nailed down what I want, but I can't seem to get it in a way that the rails designers are looking for. Basically, I have (please set aside pluralization/etc issues):

Human Relationships (Parent, Offspring)

I'm trying to get all the offsprings for a single parent, and the single parent for many offsprings (assume only one parent per offspring).

I can do this in the following way in the model:

has_one     :parent, :through => :relationships, :foreign_key => :human_id, :source => :source_human
has_many    :offsprings, :finder_sql =>
          'SELECT DISTINCT offsprings.* ' +
          'FROM humans offsprings INNER JOIN relationships r on ' +
          'r.human_id = offsprings.id where r.source_human_id = #{id}'

I had to do this, because the nicer way to do it:

 has_many    :offsprings, :through => :relationships, :foreign_key => :source_human_id, :source => :human

Is not possible because foreign keys are ignored in has_many (according to the docs here: http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html#method-i-has_many)

However, now I'm getting this error:

DEPRECATION WARNING: String-based interpolation of association conditions is deprecated. Please use a proc instead. So, for example, has_many :older_friends, :conditions => 'age > #{age}' should be changed to has_many :older_friends, :conditions => proc { "age > #{age}" }. (called from irb_binding at (irb):1)

However, no matter how I hack at :conditions here, it does not appear that :finder_sql wants to participate. Any thoughts?

score 35 · Accepted Answer · answered Mar 28 '11 at 22:30

35

What if you do

has_many    :offsprings, :finder_sql =>
          proc { "SELECT DISTINCT offsprings.* " +
          "FROM humans offsprings INNER JOIN relationships r on " +
          "r.human_id = offsprings.id where r.source_human_id = #{id}" }

answered Mar 28 '11 at 22:30

Vadim

17,897
4
38
62

2

Ladies and Germs, a winner. The docs are wrong here, you don't do a :conditions on :finder_sql, you just build it in. – aronchick Mar 29 '11 at 19:49

score 5 · Answer 2 · answered Dec 13 '13 at 16:37

Actually, I'd write it this way:

has_many :offsprings, :finder_sql => proc {OFFSPRING_SQL % {id: id}}

OFFSPRING_SQL = "SELECT DISTINCT offsprings.*
                   FROM humans offsprings
                  INNER JOIN relationships r
                        ON r.human_id = offsprings_id
                        WHERE r.source_human_id = %{id}"

I think it makes the association easier to understand, and it make the naked SQL easier to edit. It also takes advantage of string-based parameter interpolation.

Has_Many :Through or :finder_sql

2 Answers2

Linked