Rails - Dec 2008 - Needs some help with eager loading issue on a has_many through relationship

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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Dec 2, 10:45 pm, hoenth
<hoe...-/E1597aS9LQAvxtiuMwx3w@public.gmane.org> 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
>
> Any help would be greatly appreciated.
>
> Best,
> Tom
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> On Dec 2, 10:45 pm, hoenth
<hoe...-/E1597aS9LQAvxtiuMwx3w@public.gmane.org> 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
>
>
>
> > Any help would be greatly appreciated.
>
> > Best,
> > Tom
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Dec 3, 1:55 pm, hoenth <hoe...-/E1597aS9LQAvxtiuMwx3w@public.gmane.org>
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
> Thanks for your help.
> Tom
>
> On Dec 3, 8:23 am, Frederick Cheung
<frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
> wrote:
>
> > On Dec 2, 10:45 pm, hoenth
<hoe...-/E1597aS9LQAvxtiuMwx3w@public.gmane.org> 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
>
> > > Any help would be greatly appreciated.
>
> > > Best,
> > > Tom
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> On Dec 3, 1:55 pm, hoenth
<hoe...-/E1597aS9LQAvxtiuMwx3w@public.gmane.org> 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 onlyloadingone 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
>
> > Thanks for your help.
> > Tom
>
> > On Dec 3, 8:23 am, Frederick Cheung
<frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
> > wrote:
>
> > > On Dec 2, 10:45 pm, hoenth
<hoe...-/E1597aS9LQAvxtiuMwx3w@public.gmane.org> 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
>
> > > > Any help would be greatly appreciated.
>
> > > > Best,
> > > > Tom
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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.

-- 
   Phlip


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Dec 4, 2:47 am, hoenth <hoe...-/E1597aS9LQAvxtiuMwx3w@public.gmane.org>
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
> I thought it should only do 3 queries.
>
> Thanks
> Tom
>
> On Dec 3, 9:02 am, Frederick Cheung
<frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
> wrote:
>
> > On Dec 3, 1:55 pm, hoenth
<hoe...-/E1597aS9LQAvxtiuMwx3w@public.gmane.org> 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 onlyloadingone 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
>
> > > Thanks for your help.
> > > Tom
>
> > > On Dec 3, 8:23 am, Frederick Cheung
<frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
> > > wrote:
>
> > > > On Dec 2, 10:45 pm, hoenth
<hoe...-/E1597aS9LQAvxtiuMwx3w@public.gmane.org> 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
>
> > > > > Any help would be greatly appreciated.
>
> > > > > Best,
> > > > > Tom
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---