Rails - Dec 2005 - more habtm - complex join

given

[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
   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 wanting to get a list of a particular teacher''s students.  is there
any
more elegant what than this?

   [ahoward@localhost rest]$ ./script/console
   Loading development environment.
   >> teacher = Teacher.find 1
   => #<Teacher:0xb782d028 @attributes={"id"=>"1",
"person_id"=>"1"}>
   >> students = teacher.courses.inject([]){|list, c| list.push
*c.students}
   => [#<Student:0xb7825314
@attributes={"student_id"=>"1",
"id"=>"1", "course_id"=>"1",
"person_id"=>"1"}>, #<Student:0xb78252d8
@attributes={"student_id"=>"2",
"id"=>"2", "course_id"=>"1",
"person_id"=>"2"}>]


i know i could contruct a single sql statement to do this - but can that be
done in rails with one statement?  this approach does one for each course.

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:
> 
> given
OK.. as you''ve seen from my last message your database tables and Rails
classes
are flawed. What I''m going to try to do is answer your question as if
you were
using the "Rails way" of doing things. I''m not saying that
this way is "right",
it''s just way less painful than what you are currently trying to do...
> 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 (
>   course_id int,
>   student_id int
> );
> create table courses_teachers (
>   course_id int,
>   teacher_id int
> );
Here''s the schema that you should be using if you want to follow the
Rails
conventions.
> 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
This is a little different than the model you had posted in your earlier 
message. This, with the join table classes removed, is actually what you should 
be using, if you get rid of the ID column from the join tables in the database 
schema.
> and wanting to get a list of a particular teacher''s students.  is
there any
> more elegant what than this?
teacher = Teacher.find( 1 )
courses = teacher.courses.find( :all, :include => [:students, :people] )

The second statement will bring back all the courses that the found teacher has 
taught, and automatically include all the students from each of those courses, 
along with their people column data, via a join statement. That''s what
the
:include option does.

Then you can access the name of the first students in the first course by:

courses[1].students[1].person.first_name

Or use just about any fancy loop you want to process the courses, the students 
and their associated people data.
> i know i could contruct a single sql statement to do this - but can that be
> done in rails with one statement?  this approach does one for each course.
Since you already have the relationship defined, between teachers and courses 
and students and people, it''s pretty easy to get back the data you are
looking
for with a fairly simple find statement. No messy SQL to worry about, unless you
need to make joins that don''t easily follow the Rails conventions.

I''m sure someone will correct me if I''ve gotten any of this
wrong, but I''m
pretty sure this is the proper Rails Way of doing what you are trying.

-Brian

On Sun, 4 Dec 2005, Brian V. Hughes wrote:
> Here''s the schema that you should be using if you want to follow
the Rails
> conventions.
>
>> 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
check:

   [ahoward@localhost rest]$ cat app/models/{person,teacher,student,course}.rb
   class Person < ActiveRecord::Base
     has_one :student
     has_one :teacher
   end
   class Teacher < ActiveRecord::Base
     belongs_to :person
     has_and_belongs_to_many :courses
   end
   class Student < 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

> This is a little different than the model you had posted in your earlier 
> message. This, with the join table classes removed, is actually what you 
> should be using, if you get rid of the ID column from the join tables in
the
> database schema.

huh.  so you would add a new course using something like

   course = Course::new "subject" => "cooking",
"teachers" => [teacher], "students" => [student_a,
student_b]
   course.save

??
>> and wanting to get a list of a particular teacher''s students. 
is there any
>> more elegant what than this?
>
> teacher = Teacher.find( 1 )
> courses = teacher.courses.find( :all, :include => [:students, :people] )
doesn''t seem to work:

   >> teacher = Teacher::find 1
   => #<Teacher:0xb778ec58 @attributes={"id"=>"1",
"person_id"=>"1"}>

   >> teacher.courses
   => [#<Course:0xb778ce80
@attributes={"subject"=>"gym",
"id"=>"3", "teacher_id"=>"1",
"course_id"=>"2"}>, #<Course:0xb778ce44
@attributes={"subject"=>"latin",
"id"=>"1", "teacher_id"=>"1",
"course_id"=>"1"}>]

   >> teacher.courses.find( :all, :include => [:students, :people] )
   ActiveRecord::ConfigurationError: Association was not found; perhaps you
misspelled it?  You specified :include => :students, :people
           from
/usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.0/lib/active_record/associations.rb:871:in
`guard_against_missing_reflections''
           from
/usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.0/lib/active_record/associations.rb:869:in
`each''
           from
/usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.0/lib/active_record/associations.rb:869:in
`guard_against_missing_reflections''
           from
/usr/local/lib/ruby/gems/1.8/gems/activerecord-1.13.0/lib/active_record/associations.rb:825:in
`find_with_associations''
           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 (irb):25


ideas??


thanks for the input.

-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:
> 
> huh.  so you would add a new course using something like
> 
>   course = Course::new "subject" => "cooking",
"teachers" => [teacher],
> "students" => [student_a, student_b]
>   course.save
I''ve never tried to add the join records at the same time as the
primary object.
What you should do is create the course, save it, then append the students to 
course.students. I show an example in one of my other messages.
>> teacher = Teacher.find( 1 )
>> courses = teacher.courses.find( :all, :include => [:students,
:people] )
> 
> doesn''t seem to work:
> 
>   >> teacher = Teacher::find 1
>   => #<Teacher:0xb778ec58
@attributes={"id"=>"1",
"person_id"=>"1"}>
> 
>   >> teacher.courses
>   => [#<Course:0xb778ce80
@attributes={"subject"=>"gym",
"id"=>"3",
> "teacher_id"=>"1",
"course_id"=>"2"}>, #<Course:0xb778ce44
> @attributes={"subject"=>"latin",
"id"=>"1", "teacher_id"=>"1",
> "course_id"=>"1"}>]
> 
>   >> teacher.courses.find( :all, :include => [:students, :people]
)
>   ActiveRecord::ConfigurationError: Association was not found; perhaps 
> you misspelled it?  You specified :include => :students, :people
> ideas??

OK... I tried to be a little fancy and have it pre-load the people records for 
the students. That''s not working, because people isn''t part of
the course model.
So change the include to :include => :students. That should work.

-Brian