Needs some help with eager loading issue on a has_many through relationship

Rails
1

I have the following:

  1. A model called Student, which is an STI sub-class of Person
  2. Student has the following associations
    has_many :homerooms
    has_many :classrooms, :through => :homerooms

has_one :current_classroom,
:through => :homerooms,
:source => :classroom,
:conditions => “classrooms.year = '” + $current_school_year +
“’”
3. in the view, i have
student.name + “(” + student.current_classroom.grade + “)”

I need to list all of the students, along with their classroom names
(and teacher, but need to get over this hurdle first), so had the
following Find in my index action of student_controller

@students = Student.find(:all,
                         :order => 'last_name, first_name',
                         :include => :current_classroom)

But in reviewing the log, I see that people, homerooms, and classroom
are all derived from separate queries.

Student Load (0.000000) SELECT * FROM people WHERE
( (people.type = ‘Student’ ) ) ORDER BY last_name, first_name
Homeroom Load (0.002000) SELECT homerooms.* FROM homerooms
WHERE (homerooms.student_id = 5122)
Homeroom Columns (0.003000) SHOW FIELDS FROM homerooms
Classroom Columns (0.003000) SHOW FIELDS FROM classrooms
Classroom Load (0.000000) SELECT * FROM classrooms WHERE
(classrooms.id = 16)

I have also tried the following

@students = Student.find(:all,
                         :order => 'last_name, first_name',
                         :include => {:homerooms => :classroom)

to no avail.

In either case, the page displays fine, with students and classrooms.

Any idea why Rails seems to be ignoring the :include?

Any help would be greatly appreciated.

Best,
Tom

2

Fred,

I appreciate your quick response.

I am confused though. I thought the point of includes was to get all
of the related objects in as few db calls as possible. From the log
file, it is making individual calls for each classroom and each
homeroom. So for every student, it finds that student’s homeroom with
one call, then finds that homeroom’s classroom with another call.

I supposed it will eventually cache all of the classrooms, but since
homerooms is a join table, (one record for each student) it won’t be
able to.

What about has_one :through in 2.1 is broken? I am running 2.1.2

Thanks for your help.
Tom

On Dec 3, 8:23 am, Frederick C. [email protected]

3

On Dec 2, 10:45 pm, hoenth [email protected] wrote:

Any idea why Rails seems to be ignoring the :include?

It’s not - that’s the way it does includes now, one select per
association type (unless it has to fallback to the old joins based
code because conditions/order reference the included tables). In
addition :include of has_one through was broken in 2.1.

Fred

4

On Dec 3, 1:55 pm, hoenth [email protected] wrote:

Fred,

I appreciate your quick response.

I am confused though. I thought the point of includes was to get all
of the related objects in as few db calls as possible. From the log
file, it is making individual calls for each classroom and each
homeroom. So for every student, it finds that student’s homeroom with
one call, then finds that homeroom’s classroom with another call.

When you’re only loading one then the joins based stuff doesn’t make
much sense.
Were you to load 100 students the current code would load those
students, then in query number 2 load all the homerooms and then the
classrooms in the 3rd one.

I supposed it will eventually cache all of the classrooms, but since
homerooms is a join table, (one record for each student) it won’t be
able to.

What about has_one :through in 2.1 is broken? I am running 2.1.2

It was just screwed. Looks like it was backported to 2.1.2 though so
you should be ok.

Fred

Fred

5

hoenth wrote:

I thought it should only do 3 queries.
Can you post your ActiveRecord statement here?

And if the :through is not working, can you replace :include =>
:students with
:include => { :classes => :students }? Tell the includer the actual
lookup path,
so it improves the odds of eagerness?

BTW if you use my inspect_sql system (gem install assert_efficient_sql),
you can
print out the exact statements, and you can assert their properties,
such as
their count.

–
Phlip

6

On Dec 4, 2:47 am, hoenth [email protected] wrote:

I am loading about 100 students. There is a single query to get them
“select * from students…”.

Then, it appears that it is doing an individual query for each
student, to find that students homeroom, then another for that
homeroom’s classroom. It is not loading all of the classrooms in a
single query, nor the homerooms. So for the 100 students, it does one
query for student, 100 queries for homerooms, and 100 queries for
classrooms (though some appear to be cached).

I’d double check that your app is using rails 2.1.2. Even if rails -v
reports 2.1.2 your app could still be using an earlier version if
RAILS_GEM_VERSION is set or rails is frozen into vendor.
It might also help to see slightly more of your code.

Fred

7

I am loading about 100 students. There is a single query to get them
“select * from students…”.

Then, it appears that it is doing an individual query for each
student, to find that students homeroom, then another for that
homeroom’s classroom. It is not loading all of the classrooms in a
single query, nor the homerooms. So for the 100 students, it does one
query for student, 100 queries for homerooms, and 100 queries for
classrooms (though some appear to be cached).

I thought it should only do 3 queries.

Thanks
Tom

On Dec 3, 9:02 am, Frederick C. [email protected]

8

Philip/Fred

thank you for your thoughts.

it never ceases to amaze me how some things manage to fix themselves.

After looking into this for a couple of hours over the last two days,
I reran the page today, and checking into the development log found
that I am now down to 3 queries.

The homerooms and classrooms query use large “where id in (…)”
statements. I understood them to be inefficient compared to joins, but
certainly better then individual calls.

No idea why the change, but I appreciate your thoughts and help.

best,
Tom