# More habtm - complex join

**URL:** <https://www.ruby-forum.com/t/more-habtm-complex-join/61156>\
**Category:** Rails\
**Created:** [December 5, 2005, 12:33am UTC](https://www.ruby-forum.com/t/more-habtm-complex-join/61156 "2005-12-05T00:33:24Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![ara.t.howard](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a8b319/32.png) [@ara.t.howard](https://www.ruby-forum.com/u/ara.t.howard)\
**Post date:** [December 5, 2005, 12:33am UTC](https://www.ruby-forum.com/t/more-habtm-complex-join/61156/1 "2005-12-05T00:33:24Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ara.t.howard](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a8b319/32.png) [@ara.t.howard](https://www.ruby-forum.com/u/ara.t.howard)\
**Post date:** [December 5, 2005, 2:17am UTC](https://www.ruby-forum.com/t/more-habtm-complex-join/61156/2 "2005-12-05T02:17:28Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ara.t.howard](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a8b319/32.png) [@ara.t.howard](https://www.ruby-forum.com/u/ara.t.howard)\
**Post date:** [December 5, 2005, 2:58am UTC](https://www.ruby-forum.com/t/more-habtm-complex-join/61156/3 "2005-12-05T02:58:14Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ara.t.howard](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a8b319/32.png) [@ara.t.howard](https://www.ruby-forum.com/u/ara.t.howard)\
**Post date:** [December 5, 2005, 3:06am UTC](https://www.ruby-forum.com/t/more-habtm-complex-join/61156/4 "2005-12-05T03:06:20Z")

</div>

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