Self-referential habtm with condition is broken

Rails
1

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

end

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)
p.brothers.map(&:first_name)
→ works
p.sisters.map(&:first_name)
→ 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.

Michael

–
Michael S.
mailto:[email protected]
http://www.schuerig.de/michael/

2

Michael S. wrote:

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’

Michael

Could you please provide the schema that this code is running against.
As a guess, it looks as though gender is defined in both the People?
table and it’s join (Relatives?) which would lead to an ambiguous query
unless it was explicitly qualified as it was in the sister’s case.

ilan

3

Michael S. wrote:

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.

Hi Michael

that’s exactly what I am trying to solve using a :through statement…
if you solve this problem, please let me know… I’ll appreciate cloning
your definitions…

Yves

mailto:[email protected]

4

On Wednesday 03 January 2007 16:35, Ilan B. wrote:

Michael S. wrote:

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'

Could you please provide the schema that this code is running
against. As a guess, it looks as though gender is defined in both the
People? table and it’s join (Relatives?) which would lead to an
ambiguous query unless it was explicitly qualified as it was in the
sister’s case.

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.

Michael

–
Michael S.
mailto:[email protected]
http://www.schuerig.de/michael/