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
===============================================================================
Ara.T.Howard wrote:> > 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 > );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.> [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 > endAccording 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 > endLikewise, 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
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: teachersyeah. 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 ===============================================================================
ara.t.howard-32lpuo7BZBA@public.gmane.org wrote:> On Sun, 4 Dec 2005, Brian V. Hughes wrote: > > [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 > endYou 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> 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?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
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 << courseah. 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 << studentokay - 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 1well - 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 ===============================================================================
ara.t.howard-32lpuo7BZBA@public.gmane.org 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.
Brian V. Hughes wrote:> ara.t.howard-32lpuo7BZBA@public.gmane.org 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.Thanks guys for this discussion - it''s made habtm a lot clearer Kind Regards, Eric.
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 clearerhey - 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 ===============================================================================