Forum: Ruby on Rails habtm problem

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Cb48ca5059faf7409a5ab3745a964696?d=identicon&s=25 ara.t.howard (Guest)
on 2005-12-05 00:08
(Received via mailing list)
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
===============================================================================
7c4087d053eb02d099a17d91ba5e33b5?d=identicon&s=25 brianvh (Guest)
on 2005-12-05 02:01
(Received via mailing list)
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
Cb48ca5059faf7409a5ab3745a964696?d=identicon&s=25 ara.t.howard (Guest)
on 2005-12-05 02:25
(Received via mailing list)
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
===============================================================================
7c4087d053eb02d099a17d91ba5e33b5?d=identicon&s=25 brianvh (Guest)
on 2005-12-05 02:45
(Received via mailing list)
ara.t.howard@noaa.gov 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
Cb48ca5059faf7409a5ab3745a964696?d=identicon&s=25 ara.t.howard (Guest)
on 2005-12-05 03:22
(Received via mailing list)
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
===============================================================================
7c4087d053eb02d099a17d91ba5e33b5?d=identicon&s=25 brianvh (Guest)
on 2005-12-05 03:34
(Received via mailing list)
ara.t.howard@noaa.gov 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.
17d01ff4326c26911481c7a77283b343?d=identicon&s=25 esloane (Guest)
on 2005-12-06 05:34
(Received via mailing list)
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.
Cb48ca5059faf7409a5ab3745a964696?d=identicon&s=25 ara.t.howard (Guest)
on 2005-12-06 06:03
(Received via mailing list)
On Tue, 6 Dec 2005, Eric Sloane 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
===============================================================================
This topic is locked and can not be replied to.