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

rubyonrails-talk
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

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

3

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

4

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

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

6

hoenth 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 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.

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'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

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