Habtm problem


#1

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:inexecute’
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:inselect_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:infind’
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:infind_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:inmethod_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:inoutput_value’
from /usr/local/lib/ruby/1.8/irb.rb:149:in eval_input' from /usr/local/lib/ruby/1.8/irb.rb:145:insignal_status’
from /usr/local/lib/ruby/1.8/irb.rb:145:in eval_input' from /usr/local/lib/ruby/1.8/irb.rb:144:ineach_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:instart’
from /usr/local/lib/ruby/1.8/irb.rb:69:in catch' from /usr/local/lib/ruby/1.8/irb.rb:69:instart’
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


#2

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


#3

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


#4

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


#5

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.


#6

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.


#7

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! :wink:

-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


#8

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