More habtm - complex join


#1

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


#2

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


#3

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:ineach’
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:infind_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:infind’
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


#4

removed_email_address@domain.invalid 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