Forum: Ruby on Rails more habtm - complex join

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:33
(Received via mailing list)
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
===============================================================================
7c4087d053eb02d099a17d91ba5e33b5?d=identicon&s=25 brianvh (Guest)
on 2005-12-05 02:17
(Received via mailing list)
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...

>     id serial,
> 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.

>   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
Cb48ca5059faf7409a5ab3745a964696?d=identicon&s=25 ara.t.howard (Guest)
on 2005-12-05 02:58
(Received via mailing list)
On Sun, 4 Dec 2005, Brian V. Hughes wrote:

>> 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
===============================================================================
7c4087d053eb02d099a17d91ba5e33b5?d=identicon&s=25 brianvh (Guest)
on 2005-12-05 03:06
(Received via mailing list)
ara.t.howard@noaa.gov 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_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
This topic is locked and can not be replied to.