# Habtm problem

**URL:** <https://www.ruby-forum.com/t/habtm-problem/95199>\
**Category:** Rails\
**Created:** [December 5, 2005, 12:08am UTC](https://www.ruby-forum.com/t/habtm-problem/95199 "2005-12-05T00:08:52Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![ara.t.howard](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a8b319/32.png) [@ara.t.howard](https://www.ruby-forum.com/u/ara.t.howard)\
**Post date:** [December 5, 2005, 12:08am UTC](https://www.ruby-forum.com/t/habtm-problem/95199/1 "2005-12-05T00:08:52Z")

</div>

what am i doing wrong here?

```
 [ahoward@localhost rest]$ cat schema.sql
 create table people (
   id serial,
   first_name text,
   last_name text
 );
 create table students (
     id serial,
     person_id int
 );
 create table teachers (
     id serial,
     person_id int
 );
 create table courses (
   id serial,
   subject text
 );
 create table courses_students (
   id serial,
   course_id int,
   student_id int
 );
 create table courses_teachers (
   id serial,
   course_id int,
   teacher_id int
 );

 [ahoward@localhost rest]$ cat app/models/{p,s,t,c}*
 class Person < ActiveRecord::Base
   has_one :student
   has_one :teacher
 end
 class Student < ActiveRecord::Base
   belongs_to :person
 end
 class Teacher < ActiveRecord::Base
   belongs_to :person
 end
 class Course < ActiveRecord::Base
   has_many :course_student
   has_many :course_teacher
 end
 class CourseStudent < ActiveRecord::Base
   set_table_name "courses_students"
   has_and_belongs_to_many :course, :join_table => table_name
   has_and_belongs_to_many :student, :join_table => table_name
 end
 class CourseTeacher < ActiveRecord::Base
   set_table_name "courses_teachers"
   has_and_belongs_to_many :course, :join_table => table_name
   has_and_belongs_to_many :teacher, :join_table => table_name
 end

 [ahoward@localhost rest]$ ./script/console
 Loading development environment.
 >> ct = CourseTeacher::find 1
 => #<CourseTeacher:0xb781129c @attributes={"id"=>"1", 

```

“teacher\_id”=\>“1”, “course\_id”=\>“1”}\>  
\>\> c = Course::find 1  
=\> #\<Course:0xb780e8bc @attributes={“subject”=\>“latin”, “id”=\>“1”}\>  
\>\> c.course\_teacher  
=\> [#\<CourseTeacher:0xb780bf04 @attributes={“id”=\>“1”,  
“teacher\_id”=\>“1”, “course\_id”=\>“1”}\>, #\<CourseTeacher:0xb780bec8  
@attributes={“id”=\>“2”, “teacher\_id”=\>“2”, “course\_id”=\>“1”}\>]  
\>\> ct.course  
ActiveRecord::StatementInvalid: ERROR: column  
courses\_teachers.course\_teacher\_id does not exist  
: SELECT \* FROM courses LEFT JOIN courses\_teachers ON courses.id =  
courses\_teachers.course\_id WHERE (courses\_teachers.course\_teacher\_id = 1  
)  
from  
/usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.0/lib/active\_record/connection\_adapters/abstract\_adapter.rb:67:in  
`log' from /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.0/lib/active_record/connection_adapters/postgresql_adapter.rb:113:in`execute’  
from  
/usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.0/lib/active\_record/connection\_adapters/postgresql\_adapter.rb:290:in  
`select' from /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.0/lib/active_record/connection_adapters/postgresql_adapter.rb:94:in`select\_all’  
from  
/usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.0/lib/active\_record/base.rb:447:in  
`find_by_sql' from /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.0/lib/active_record/base.rb:411:in`find’  
from  
/usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.0/lib/active\_record/associations/has\_and\_belongs\_to\_many\_association.rb:58:in  
`find' from /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.0/lib/active_record/associations/has_and_belongs_to_many_association.rb:93:in`find\_target’  
from  
/usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.0/lib/active\_record/associations/association\_proxy.rb:81:in  
`load_target' from /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.0/lib/active_record/associations/association_proxy.rb:74:in`method\_missing’  
from  
/usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.0/lib/active\_record/associations/has\_and\_belongs\_to\_many\_association.rb:81:in  
`method_missing' from /usr/local/lib/ruby/1.8/irb.rb:296:in`output\_value’  
from /usr/local/lib/ruby/1.8/irb.rb:149:in `eval_input' from /usr/local/lib/ruby/1.8/irb.rb:145:in`signal\_status’  
from /usr/local/lib/ruby/1.8/irb.rb:145:in `eval_input' from /usr/local/lib/ruby/1.8/irb.rb:144:in`each\_top\_level\_statement’  
from /usr/local/lib/ruby/1.8/irb.rb:144:in `eval_input' from /usr/local/lib/ruby/1.8/irb.rb:70:in`start’  
from /usr/local/lib/ruby/1.8/irb.rb:69:in `catch' from /usr/local/lib/ruby/1.8/irb.rb:69:in`start’  
from /usr/local/bin/irb:13\>\>

why does ActiveRecord seem to think the pk is  
‘courses\_teachers.course\_teacher\_id’ and not ‘courses\_teachers.id’ ??

considering i told it nothing other than the table\_name why should it  
think  
the pk does not follow normal convention?

also, why on earth would the join table not be assumed to be the  
table\_name in  
general? am i missing something critical here?

kind regards.

## -a

# =============================================================================== | ara [dot] t [dot] howard [at] noaa [dot] gov | all happiness comes from the desire for others to be happy. all misery | comes from the desire for oneself to be happy. | – bodhicaryavatara

---

<div class="post-metadata">

**Author:** ![ara.t.howard](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a8b319/32.png) [@ara.t.howard](https://www.ruby-forum.com/u/ara.t.howard)\
**Post date:** [December 5, 2005, 2:01am UTC](https://www.ruby-forum.com/t/habtm-problem/95199/2 "2005-12-05T02:01:10Z")

</div>

Ara.T.Howard wrote:

> ```
> id serial,
> create table courses_students (
> id serial,
> course_id int,
> student_id int
> );
> create table courses_teachers (
> id serial,
> course_id int,
> teacher_id int
> );
> 
> ```

Rails convention says that you don’t put an id column in your join  
tables, for  
many to many relations. So you need to drop the id column from  
courses\_students  
and courses\_teachers.

> ```
> end
> class Course < ActiveRecord::Base
> has_many :course_student
> has_many :course_teacher
> end
> 
> ```

According to your SQL schema, a Course should  
has\_and\_belongs\_to\_many: students  
has\_and\_belongs\_to\_many: teachers

You don’t directly reference the join tables. Active Record handles that  
for you  
in the object-relational map.

> ```
> class CourseStudent < ActiveRecord::Base
> set_table_name "courses_students"
> has_and_belongs_to_many :course, :join_table => table_name
> has_and_belongs_to_many :student, :join_table => table_name
> end
> class CourseTeacher < ActiveRecord::Base
> set_table_name "courses_teachers"
> has_and_belongs_to_many :course, :join_table => table_name
> has_and_belongs_to_many :teacher, :join_table => table_name
> end
> 
> ```

Likewise, you don’t specify a class model for the join tables. So you  
should get  
rid of both of these classes.

> why does ActiveRecord seem to think the pk is  
> ‘courses\_teachers.course\_teacher\_id’ and not ‘courses\_teachers.id’ ??

Because a join table doesn’t need an independent ID for each record. If  
you put  
one in there you mess with a lot of the things that Active Record is  
trying to  
do for you.

> considering i told it nothing other than the table\_name why should it think  
> the pk does not follow normal convention?

Because the “normal convention” for a Rails join table is to not have an  
id  
column…

> also, why on earth would the join table not be assumed to be the  
> table\_name in general? am i missing something critical here?

I tried looking for an example on the Rails Wiki to explain the database  
table  
conventions, but I couldn’t find one. It’s explained perfectly in the  
Rails  
book, but I realize that not everyone has that. Maybe someone can point  
to a  
page that explains it better than I have.

-Brian

---

<div class="post-metadata">

**Author:** ![ara.t.howard](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a8b319/32.png) [@ara.t.howard](https://www.ruby-forum.com/u/ara.t.howard)\
**Post date:** [December 5, 2005, 2:25am UTC](https://www.ruby-forum.com/t/habtm-problem/95199/3 "2005-12-05T02:25:36Z")

</div>

On Sun, 4 Dec 2005, Brian V. Hughes wrote:

> Rails convention says that you don’t put an id column in your join tables,  
> for many to many relations. So you need to drop the id column from  
> courses\_students and courses\_teachers.

huh. good to know.

> According to your SQL schema, a Course should  
> has\_and\_belongs\_to\_many: students  
> has\_and\_belongs\_to\_many: teachers

yeah. figured that out later. models are as follows now:

[ahoward@localhost rest]$ cat app/models/{p,s,t,c}\*  
class Person \< ActiveRecord::Base  
has\_one :student  
has\_one :teacher  
end  
class Student \< ActiveRecord::Base  
belongs\_to :person  
has\_and\_belongs\_to\_many :courses  
end  
class Teacher \< ActiveRecord::Base  
belongs\_to :person  
has\_and\_belongs\_to\_many :courses  
end  
class Course \< ActiveRecord::Base  
has\_and\_belongs\_to\_many :teachers  
has\_and\_belongs\_to\_many :students  
end  
class CourseStudent \< ActiveRecord::Base  
set\_table\_name “courses\_students”  
belongs\_to :course  
belongs\_to :student  
end  
class CourseTeacher \< ActiveRecord::Base  
set\_table\_name “courses\_teachers”  
belongs\_to :course  
belongs\_to :teacher  
end

and working smoothly. even **with** the primary keys still in the join  
tables  
i might add…

> Likewise, you don’t specify a class model for the join tables. So you should  
> get rid of both of these classes.

well - in the case the relation model is totally normalized so this is  
the ony  
way to add, for instance, a student to a course. surely it’s the  
preferred way  
to do this via a rails model?

> Because a join table doesn’t need an independent ID for each record. If you  
> put one in there you mess with a lot of the things that Active Record is  
> trying to do for you.

hmmm. with the above model defs this works:

[ahoward@localhost rest]$ ./script/console  
Loading development environment.

> > teacher = Teacher.find 1  
> > =\> #\<Teacher:0xb7822050 @attributes={“id”=\>“1”, “person\_id”=\>“1”}\>
> 
> > teacher.courses  
> > =\> [#\<Course:0xb781ef68 @attributes={“subject”=\>“gym”, “id”=\>“3”,  
> > “teacher\_id”=\>“1”, “course\_id”=\>“2”}\>, #\<Course:0xb781ef2c  
> > @attributes={“subject”=\>“latin”, “id”=\>“1”, “teacher\_id”=\>“1”,  
> > “course\_id”=\>“1”}\>]
> 
> > course\_teacher = CourseTeacher.find 1  
> > =\> #\<CourseTeacher:0xb7819cac @attributes={“id”=\>“1”,  
> > “teacher\_id”=\>“1”, “course\_id”=\>“1”}\>
> 
> > course\_teacher.teacher  
> > =\> #\<Teacher:0xb7816f48 @attributes={“id”=\>“1”, “person\_id”=\>“1”}\>
> 
> > course\_teacher.course  
> > =\> #\<Course:0xb7815134 @attributes={“subject”=\>“latin”, “id”=\>“1”}\>
> 
> > course\_teacher.course.teachers  
> > =\> [#\<Teacher:0xb781042c @attributes={“id”=\>“1”, “teacher\_id”=\>“1”,  
> > “course\_id”=\>“1”, “person\_id”=\>“1”}\>, #\<Teacher:0xb78103f0  
> > @attributes={“id”=\>“2”, “teacher\_id”=\>“2”, “course\_id”=\>“1”,  
> > “person\_id”=\>“2”}\>]

looks o.k. doesn’t it?

> Because the “normal convention” for a Rails join table is to not have an id  
> column…

ok - is the above wrong in some subtle way then?

> I tried looking for an example on the Rails Wiki to explain the database  
> table conventions, but I couldn’t find one. It’s explained perfectly in the  
> Rails book, but I realize that not everyone has that. Maybe someone can point  
> to a page that explains it better than I have.

good to know - time to buy then…

cheers.

## -a

# =============================================================================== | ara [dot] t [dot] howard [at] noaa [dot] gov | all happiness comes from the desire for others to be happy. all misery | comes from the desire for oneself to be happy. | – bodhicaryavatara

---

<div class="post-metadata">

**Author:** ![ara.t.howard](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a8b319/32.png) [@ara.t.howard](https://www.ruby-forum.com/u/ara.t.howard)\
**Post date:** [December 5, 2005, 2:45am UTC](https://www.ruby-forum.com/t/habtm-problem/95199/4 "2005-12-05T02:45:58Z")

</div>

removed\_email\_address@domain.invalid wrote:

> end  
> belongs\_to :course  
> belongs\_to :student  
> end  
> class CourseTeacher \< ActiveRecord::Base  
> set\_table\_name “courses\_teachers”  
> belongs\_to :course  
> belongs\_to :teacher  
> end

You still need to drop the models for the join tables. They aren’t  
independent  
data items. They exist, or they should exist, only to make many-to-many  
relationships between 2 data tables. Rails automatically knows how to  
deal with  
the join tables, especially if you name them correctly (which you have).

> and working smoothly. even **with** the primary keys still in the join  
> tables  
> i might add…

I didn’t say it wouldn’t work, only that Rails convention is that you  
don’t use  
an ID column in join tables.

> well - in the case the relation model is totally normalized so this is the  
> ony way to add, for instance, a student to a course. surely it’s the  
> preferred way to do this via a rails model?

Nah… Just do:

student = Student.find 1  
course = Course.find 1  
student.courses \<\< course

And you’ve just added that student to the course, and vice-versa, since  
it’s a  
many-to-many. You can do the same thing with:

course.students \<\< student

> “course\_id”=\>“1”}\>]  
> @attributes={“id”=\>“2”, “teacher\_id”=\>“2”, “course\_id”=\>“1”,  
> “person\_id”=\>“2”}\>]
> 
> looks o.k. doesn’t it?

You’re just working way too hard to for it. There should never be a  
reason for  
you to do: course\_teacher = CourseTeacher.find 1

If you have a teacher and you want the first course that teacher taught:

course = teacher.courses.find 1

is all you need to do. If you have a course and you want all the  
teachers that  
have taught it:

teachers = course.teachers

does it.

> ok - is the above wrong in some subtle way then?

What you are doing isn’t wrong, specifically, but your extra models and  
the ID  
columns in the join tables, is just more than you need. You can clean up  
your  
code a lot by making the changes I’ve pointed out and trying to follow  
the Rails  
conventions. If you’ve been reading the list for a while, it’s usually  
called  
“Keeping DRY”.

-Brian

---

<div class="post-metadata">

**Author:** ![ara.t.howard](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a8b319/32.png) [@ara.t.howard](https://www.ruby-forum.com/u/ara.t.howard)\
**Post date:** [December 5, 2005, 3:34am UTC](https://www.ruby-forum.com/t/habtm-problem/95199/5 "2005-12-05T03:34:45Z")

</div>

removed\_email\_address@domain.invalid wrote:

> On Sun, 4 Dec 2005, Brian V. Hughes wrote:
> 
> > What you are doing isn’t wrong, specifically, but your extra models and the  
> > ID columns in the join tables, is just more than you need. You can clean  
> > up your code a lot by making the changes I’ve pointed out and trying to  
> > follow the Rails conventions. If you’ve been reading the list for a while,  
> > it’s usually called “Keeping DRY”.
> 
> as i said, my concern is in making **any** read/write to a table in the db  
> via a rest methodology correct regardless of what relationships exist. for  
> that to be true any, and all, relationships must be modeled via rails.

Hmm… I guess that makes some sense, but join tables don’t exist  
without the  
relationships on either side, so I’m not sure you can really use  
“regardless”  
when referring to them…

> thanks alot for the insight. it’s been quite helpful.

Happy to help out.

---

<div class="post-metadata">

**Author:** ![ara.t.howard](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a8b319/32.png) [@ara.t.howard](https://www.ruby-forum.com/u/ara.t.howard)\
**Post date:** [December 6, 2005, 5:34am UTC](https://www.ruby-forum.com/t/habtm-problem/95199/6 "2005-12-06T05:34:34Z")

</div>

Brian V. Hughes wrote:

> > > it’s usually called “Keeping DRY”.  
> > > without the relationships on either side, so I’m not sure you can really  
> > > use “regardless” when referring to them…
> 
> > thanks alot for the insight. it’s been quite helpful.
> 
> Happy to help out.  
> Thanks guys for this discussion - it’s made habtm a lot clearer  
> Kind Regards,  
> Eric.

---

<div class="post-metadata">

**Author:** ![ara.t.howard](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a8b319/32.png) [@ara.t.howard](https://www.ruby-forum.com/u/ara.t.howard)\
**Post date:** [December 6, 2005, 6:03am UTC](https://www.ruby-forum.com/t/habtm-problem/95199/7 "2005-12-06T06:03:04Z")

</div>

On Tue, 6 Dec 2005, Eric S. wrote:

> > Hmm… I guess that makes some sense, but join tables don’t exist without  
> > the relationships on either side, so I’m not sure you can really use  
> > “regardless” when referring to them…
> > 
> > > thanks alot for the insight. it’s been quite helpful.
> > 
> > Happy to help out.  
> > Thanks guys for this discussion - it’s made habtm a lot clearer

hey - that makes **one** of us! 😉

## -a

# =============================================================================== | ara [dot] t [dot] howard [at] noaa [dot] gov | all happiness comes from the desire for others to be happy. all misery | comes from the desire for oneself to be happy. | – bodhicaryavatara

---

<div class="post-metadata">

**Author:** ![ara.t.howard](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a8b319/32.png) [@ara.t.howard](https://www.ruby-forum.com/u/ara.t.howard)\
**Post date:** [December 5, 2005, 3:22am UTC](https://www.ruby-forum.com/t/habtm-problem/95199/8 "2005-12-05T03:22:37Z")

</div>

On Sun, 4 Dec 2005, Brian V. Hughes wrote:

> You still need to drop the models for the join tables.

**need**? or **should**?

> I didn’t say it wouldn’t work, only that Rails convention is that you don’t  
> use an ID column in join tables.

indeed you didn’t… bad assumption on my part.

> Nah… Just do:
> 
> student = Student.find 1  
> course = Course.find 1  
> student.courses \<\< course

ah. starting to make sense now.

> And you’ve just added that student to the course, and vice-versa, since it’s  
> a many-to-many. You can do the same thing with:
> 
> course.students \<\< student

okay - that’s quite nice.

> You’re just working way too hard to for it. There should never be a reason  
> for you to do: course\_teacher = CourseTeacher.find 1

well - in my case there is: i’m working on a generic rest method to  
expose  
every table in a db to a rest style architechture. it may be possible  
to  
‘write though’ using rails relationships, but it’s unclear at this point  
if  
that will work in every case…

> What you are doing isn’t wrong, specifically, but your extra models and the  
> ID columns in the join tables, is just more than you need. You can clean up  
> your code a lot by making the changes I’ve pointed out and trying to follow  
> the Rails conventions. If you’ve been reading the list for a while, it’s  
> usually called “Keeping DRY”.

as i said, my concern is in making **any** read/write to a table in the  
db via  
a rest methodology correct regardless of what relationships exist. for  
that  
to be true any, and all, relationships must be modeled via rails.

thanks alot for the insight. it’s been quite helpful.

## -a

# =============================================================================== | ara [dot] t [dot] howard [at] noaa [dot] gov | all happiness comes from the desire for others to be happy. all misery | comes from the desire for oneself to be happy. | – bodhicaryavatara