Forum: Ruby on Rails Needs some help with eager loading issue on a has_many through relationship

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.
A8be277b450f30e5ea8d5278b758d48b?d=identicon&s=25 hoenth (Guest)
on 2008-12-02 23:46
(Received via mailing list)
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
81b61875e41eaa58887543635d556fca?d=identicon&s=25 Frederick Cheung (Guest)
on 2008-12-03 14:24
(Received via mailing list)
On Dec 2, 10:45 pm, hoenth <hoe...@yahoo.com> 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
A8be277b450f30e5ea8d5278b758d48b?d=identicon&s=25 hoenth (Guest)
on 2008-12-03 14:55
(Received via mailing list)
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 Cheung <frederick.che...@gmail.com>
81b61875e41eaa58887543635d556fca?d=identicon&s=25 Frederick Cheung (Guest)
on 2008-12-03 15:03
(Received via mailing list)
On Dec 3, 1:55 pm, hoenth <hoe...@yahoo.com> 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
A8be277b450f30e5ea8d5278b758d48b?d=identicon&s=25 hoenth (Guest)
on 2008-12-04 03:47
(Received via mailing list)
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 Cheung <frederick.che...@gmail.com>
Aafa8848c4b764f080b1b31a51eab73d?d=identicon&s=25 Phlip (Guest)
on 2008-12-04 04:13
(Received via mailing list)
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
81b61875e41eaa58887543635d556fca?d=identicon&s=25 Frederick Cheung (Guest)
on 2008-12-04 10:03
(Received via mailing list)
On Dec 4, 2:47 am, hoenth <hoe...@yahoo.com> 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
A8be277b450f30e5ea8d5278b758d48b?d=identicon&s=25 hoenth (Guest)
on 2008-12-04 16:18
(Received via mailing list)
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
This topic is locked and can not be replied to.