Self-referential habtm with condition is broken

Here's a simplistic model class.

  class Person < ActiveRecord::Base     has_and_belongs_to_many :relatives,       :class_name => 'Person', :join_table => 'relatives',       :foreign_key => 'from_id', :association_foreign_key => 'to_id'

    has_and_belongs_to_many :brothers,       :class_name => 'Person', :join_table => 'relatives',       :foreign_key => 'from_id', :association_foreign_key => 'to_id',       :conditions => "gender = 'm'"     # ^ plain reference to column

    has_and_belongs_to_many :sisters,       :class_name => 'Person', :join_table => 'relatives',       :foreign_key => 'from_id', :association_foreign_key => 'to_id',       :conditions => "sisters_people.gender = 'f'"     # ^ qualified reference to column


The noteworthy things about it are that its habtm associations are self-referential and that two of them have conditions. Let's see how to use this. Also, the brothers and sisters associations use different ways to refer to the column on which their respective condition depends.

Lets put this class through its paces.

  >> Person.find(:first, :include => :sisters)   -> works   >> Person.find(:first, :include => :brothers)   -> exception; invalid SQL, reference to column 'gender' is ambiguous

  >> p = Person.find(:first)   >>   -> works   >>   -> exception; invalid SQL, no alias defined for sisters_people

I may be missing something, but from what it looks like, ActiveRecord doesn't handle this case as it should.


ActiveRecord::Schema.define(:version => 10) do   create_table "people", :force => true do |t|     t.column "first_name", :string, :limit => 40, :null => false     t.column "last_name", :string, :limit => 40, :null => false     t.column "gender", :string, :limit => 1, :null => end

  create_table "relatives", :id => false, :force => true do |t|     t.column "from_id", :integer, :null => false     t.column "to_id", :integer, :null => false   end end

In case it wasn't clear from my original message, the point I'm trying to make is that apparently it is not possible to specify a condition on a habtm association in such a way that both fetching the associated objects and finding with the association :include'd are possible.