has_many association_condition option

I would +1 for a :joins options, allowing to specify the joining conditions on a has_many assoc (or habtm).

I have a +/- complex request forcing me to use a finder_sql because of that. More info here : http://stackoverflow.com/questio... :

[copy/paste:] " Hi,

I have a relationship table :

create_table "animal_friends", :force => true do |t|
t.integer  "animal_id"
t.integer  "animal_friend_id"
t.datetime "created_at"
t.datetime "updated_at"
t.integer  "status_id",        :default => 1
end

linking animals to others. Best way to retreive associations in SQL is :

SELECT animals.*
from animals join animal_friends as af 
on animals.id = 
case when af.animal_id = #{id} then af.animal_friend_id else
af.animal_id end 
WHERE #{id} in (af.animal_id, af.animal_friend_id)

And I can't find a way to create a proper has_many relation in rails with this. Apparently, there's no way to provide joining conditions for has_many.

I'm currently using a finder_sql :

has_many :friends, :class_name => "Animal", :finder_sql => 'SELECT animals.* from animals join animal_friends as af on animals.id = case when af.animal_id = #{id} then af.animal_friend_id else af.animal_id end ' + 'WHERE #{id} in (af.animal_id, af.animal_friend_id) and status_id = #{Status::CONFIRMED.id}'

but this method has the great disadvantage of breaking activerecord magic. For instance :

@animal.friends.first

will execute the finder_sql without limit, fetching thousands of rows, then taking the first of the array (and loosing several precious seconds / req).

I guess it's a missing feature from AR, but I'd like to be sure first :) Thanks "