# Self-referential habtm with condition is broken

**URL:** <https://www.ruby-forum.com/t/self-referential-habtm-with-condition-is-broken/81066>\
**Category:** Rails\
**Created:** [January 3, 2007, 3:40pm UTC](https://www.ruby-forum.com/t/self-referential-habtm-with-condition-is-broken/81066 "2007-01-03T15:40:35Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![wickie](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/ba9def/32.png) [@wickie](https://www.ruby-forum.com/u/wickie)\
**Post date:** [January 3, 2007, 3:40pm UTC](https://www.ruby-forum.com/t/self-referential-habtm-with-condition-is-broken/81066/1 "2007-01-03T15:40:35Z")

</div>

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:removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
[http://www.schuerig.de/michael/](http://www.schuerig.de/michael/)

---

<div class="post-metadata">

**Author:** ![wickie](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/ba9def/32.png) [@wickie](https://www.ruby-forum.com/u/wickie)\
**Post date:** [January 3, 2007, 4:35pm UTC](https://www.ruby-forum.com/t/self-referential-habtm-with-condition-is-broken/81066/2 "2007-01-03T16:35:27Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![wickie](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/ba9def/32.png) [@wickie](https://www.ruby-forum.com/u/wickie)\
**Post date:** [January 3, 2007, 5:51pm UTC](https://www.ruby-forum.com/t/self-referential-habtm-with-condition-is-broken/81066/3 "2007-01-03T17:51:59Z")

</div>

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:removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![wickie](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/ba9def/32.png) [@wickie](https://www.ruby-forum.com/u/wickie)\
**Post date:** [January 3, 2007, 6:55pm UTC](https://www.ruby-forum.com/t/self-referential-habtm-with-condition-is-broken/81066/4 "2007-01-03T18:55:50Z")

</div>

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:removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
[http://www.schuerig.de/michael/](http://www.schuerig.de/michael/)