Rails - Sep 2006 - Eager loading using find_by_sql

Here''s the problem I have the following models

User - is a person
Team - is a team
Wherenote - snipet of info about a user''s whereabouts on a certain day

Team has_and_belongs_to_many :users
User has_and_belongs_to_many :teams
     has_many :wherenotes
Wherenote belongs_to :user

each Wherenote has a note_date

On one page I want to pull back all the Users from a particular Team and 
then display all their Wherenotes for a given day.

Currently this is done by getting team.users and then doing a separate 
find for
each of the users looking for Wherenotes with a particular note date. 
The dreaded n+1 problem.

Teams are only quite small 10-30 users, and on any given day each user 
only has a maximum of around 5 Wherenotes, however, as you can imagine 
firing this number of database queries isn''t ideal.

I have tried to get round this by eager loading Wherenotes with each 
user, but this causes a separate problem, as, over time, each user has 
built up quite a stack of Wherenotes and eager loading them all takes up 
too much time and memory.

Inside my team model I have written the following function.
<code>
def find_users_and_notes_from_date(date)
  sql = ""
  sql += "SELECT distinct u.*, w.* "
  sql += "FROM whereusers u, wherenotes w, teams t, teams_users tu "
  sql += "WHERE u.id = tu.user_id "
  sql += "AND w.user_id = u.id "
  sql += "AND w.note_date = " +
date.strftime(''\''%d-%b-%y\'''') + "
"
  sql += "AND tu.team_id = " + self.id.to_s
  User.find_by_sql sql
end
</code>

From my controller I call
<code>
  @users = @team.find_users_and_notes_from_date(@date)
</code>

If I run the SQL code on it''s own then it brings back all the users and
and the notes for that Date and Team

Somewhere along the lines it''s all going wrong and I''m a
little far out
of my depth to work out where, or even if what I''m doing is possible.

If this is the wrong way to approach this problem or you can suggest a 
way of making it working, I''d appreciate any help.

Thanks
Tom Styles

-- 
Posted via http://www.ruby-forum.com/.

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

I''m not sure what "goes wrong" but this is another way to
write the query.
I didn''t see where your teams table comes into play in your query, so I
left
it out:

SELECT DISTINCT u.*, w.*
FROM whereusers u
INNER JOIN teams_users tu
  ON tu.user_id = u.id
  AND tu.team_id = #{self.id.to_s}
INNER JOIN wherenotes w
  ON w.user_id = u.id
  AND w.note_date = yourdate

You could also try

User.find( :all, :conditions => "users.id = #{self.id} AND
teams_users.note_date = ''YOURDATE''", :include =>
[:teams, :wherenotes] )

or something similar...

ed


On 9/28/06, Tom Styles
<rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:
>
>
> Here''s the problem I have the following models
>
> User - is a person
> Team - is a team
> Wherenote - snipet of info about a user''s whereabouts on a certain
day
>
> Team has_and_belongs_to_many :users
> User has_and_belongs_to_many :teams
>      has_many :wherenotes
> Wherenote belongs_to :user
>
> each Wherenote has a note_date
>
> On one page I want to pull back all the Users from a particular Team and
> then display all their Wherenotes for a given day.
>
> Currently this is done by getting team.users and then doing a separate
> find for
> each of the users looking for Wherenotes with a particular note date.
> The dreaded n+1 problem.
>
> Teams are only quite small 10-30 users, and on any given day each user
> only has a maximum of around 5 Wherenotes, however, as you can imagine
> firing this number of database queries isn''t ideal.
>
> I have tried to get round this by eager loading Wherenotes with each
> user, but this causes a separate problem, as, over time, each user has
> built up quite a stack of Wherenotes and eager loading them all takes up
> too much time and memory.
>
> Inside my team model I have written the following function.
> <code>
> def find_users_and_notes_from_date(date)
>   sql = ""
>   sql += "SELECT distinct u.*, w.* "
>   sql += "FROM whereusers u, wherenotes w, teams t, teams_users tu
"
>   sql += "WHERE u.id = tu.user_id "
>   sql += "AND w.user_id = u.id "
>   sql += "AND w.note_date = " +
date.strftime(''\''%d-%b-%y\'''') + "
"
>   sql += "AND tu.team_id = " + self.id.to_s
>   User.find_by_sql sql
> end
> </code>
>
> From my controller I call
> <code>
>   @users = @team.find_users_and_notes_from_date(@date)
> </code>
>
> If I run the SQL code on it''s own then it brings back all the
users and
> and the notes for that Date and Team
>
> Somewhere along the lines it''s all going wrong and I''m a
little far out
> of my depth to work out where, or even if what I''m doing is
possible.
>
> If this is the wrong way to approach this problem or you can suggest a
> way of making it working, I''d appreciate any help.
>
> Thanks
> Tom Styles
>
> --
> Posted via http://www.ruby-forum.com/.
>
> >
>

-- 
Ed Hickey
Developer
Litmus Media
816-533-0409
ehickey-A4HEbNdjHgMmlAP/+Wk3EA@public.gmane.org
A Member of Think Partnership, Inc
www.ThinkPartnership.com
Amex ticker symbol: THK


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

> I didn''t see where your teams table comes into play in your query,
so I
> left
Nice one ed I wasn''t using the team table as you rightly pointed out
> You could also try
> 
> User.find( :all, :conditions => "users.id = #{self.id} AND
> teams_users.note_date = ''YOURDATE''", :include =>
[:teams, :wherenotes] )
> 
This seemed more like it, not quite right, but much closer, so I''ve 
refiddled it into this

user_ids = @team.users.collect{|u| u.id}

@users = User.find(:all, :conditions => ["whereusers.id IN (?) AND 
wherenotes.note_date = ?", user_ids,
@date.strftime("%d-%b-%y").upcase],
:include => [:wherenotes])

This produces some SQL which, when executed, brings back exactly what 
I''m looking for, thus giving the impression that Rails is on top of the
situation. However when it comes to returning an array of user objects I 
just get an empty array.

This page 
http://api.rubyonrails.com/classes/ActiveRecord/Associations/ClassMethods.html
Says that what I''m trying to do is impossible using activerecord, so
How
can it be done?

Any additional help is really appreciated,
Thanks
Tom


-- 
Posted via http://www.ruby-forum.com/.

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

Maybe I''m not quite understanding the problem.....but it seems to me
that
associations can go a long way to help you out


@team = Team.find :first, :include=>[{:users => :wherenotes}],
   :conditions =>["wherenotes.note_date = ?", @date.strftime
("%d-%b-%y").upcase]


Untested, but includes can be nested which should get you want you want.

@team.users gives you all users
@team.user[0].wherenotes gives you the notes for that user
@team.users.each do |u|
   u.wherenote.note_date
end


If I''m missing something, let me know and I can take another stab at
it. :)

Good luck!


On 10/11/06, Tom Styles
<rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:
>
>
>
> > I didn''t see where your teams table comes into play in your
query, so I
> > left
>
> Nice one ed I wasn''t using the team table as you rightly pointed
out
>
> > You could also try
> >
> > User.find( :all, :conditions => "users.id = #{self.id} AND
> > teams_users.note_date = ''YOURDATE''", :include
=> [:teams, :wherenotes] )
> >
>
> This seemed more like it, not quite right, but much closer, so
I''ve
> refiddled it into this
>
> user_ids = @team.users.collect{|u| u.id}
>
> @users = User.find(:all, :conditions => ["whereusers.id IN (?) AND
> wherenotes.note_date = ?", user_ids,
@date.strftime("%d-%b-%y").upcase],
> :include => [:wherenotes])
>
> This produces some SQL which, when executed, brings back exactly what
> I''m looking for, thus giving the impression that Rails is on top
of the
> situation. However when it comes to returning an array of user objects I
> just get an empty array.
>
> This page
>
>
http://api.rubyonrails.com/classes/ActiveRecord/Associations/ClassMethods.html
> Says that what I''m trying to do is impossible using activerecord,
so How
> can it be done?
>
> Any additional help is really appreciated,
> Thanks
> Tom
>
>
> --
> Posted via http://www.ruby-forum.com/.
>
> >
>

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

Brian Hogan wrote:
> Maybe I''m not quite understanding the problem.....but it seems to
me
> that
> associations can go a long way to help you out
> 
> 
> @team = Team.find :first, :include=>[{:users => :wherenotes}],
>    :conditions =>["wherenotes.note_date = ?", @date.strftime
> ("%d-%b-%y").upcase]
When I run this it just returns nil, I don''t think I can do this using 
associations and includes, because I need to include conditions on the 
included object. This is not where eager loading can be used.

So I''m now thinking that a better way of doing it would be:-

@users = @team.users
    user_ids = @users.collect{|u| u.id}
    wherenotes = Wherenote.find(:all, :conditions => ["user_id IN (?) 
AND note_date = ?", user_ids, @date], :order => ''user_id,
position'')
	for user in @users
	  for wherenote in wherenotes
	    if wherenote.user_id == user.id
	      user.wherenotes << wherenote
	    end
	  end
	end

This seems to be working and populating an array of users with the 
correct wherenotes. The work is being done on the server rather than 
with multiple calls to the database.

However now when I call user.wherenotes in the view instead of just 
bringing back the wherenotes that I''ve already loaded, it goes and does
a fresh call to the database and brings back all the wherenotes for that 
user.

Active record is being too helpful. I really need to cut the connection 
to the database at this point and force the object to work with what 
it''s got rather than do a fresh query.

Any tips anyone.

Tom

-- 
Posted via http://www.ruby-forum.com/.

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

Okay,

I''ve been watching this conversation play out for a while now.

Tom, I''d like to point out something you''re saying which is
completely
false (and most likely holding you back from reaching a solution):

"I need to include conditions on the included object.  This is not
where eager loading can be used"

Eager loading using the :include option *absolutely* allows you to
have conditions that refer to the relations specified in your
:include.

Not to put too fine a point on it, but what you''ve described so far is
a *trivial* use-case of ActiveRecord.  It''s time you started assuming
you are doing something wrong rather than assuming ActiveRecord is
incapable.

Assuming what you''ve explained so far is accurate, try this.  Tail the
development.log file to see the queries being executed:

x = Team.find(:all, :include => :users)
x.first.users.length # should not cause another query to hit the DB

y = Team.find(:all, :include => :users, :conditions =>''users.id
is not null'')
y.first.users.length # again, no extra hit to DB

That''s your starting point.

Regards,
Trevor

--
Trevor Squires
http://somethinglearned.com

On 10/12/06, Tom Styles
<rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:
>
> Brian Hogan wrote:
> > Maybe I''m not quite understanding the problem.....but it
seems to me
> > that
> > associations can go a long way to help you out
> >
> >
> > @team = Team.find :first, :include=>[{:users => :wherenotes}],
> >    :conditions =>["wherenotes.note_date = ?",
@date.strftime
> > ("%d-%b-%y").upcase]
>
> When I run this it just returns nil, I don''t think I can do this
using
> associations and includes, because I need to include conditions on the
> included object. This is not where eager loading can be used.
>
> So I''m now thinking that a better way of doing it would be:-
>
> @users = @team.users
>     user_ids = @users.collect{|u| u.id}
>     wherenotes = Wherenote.find(:all, :conditions => ["user_id IN
(?)
> AND note_date = ?", user_ids, @date], :order => ''user_id,
position'')
>         for user in @users
>           for wherenote in wherenotes
>             if wherenote.user_id == user.id
>               user.wherenotes << wherenote
>             end
>           end
>         end
>
> This seems to be working and populating an array of users with the
> correct wherenotes. The work is being done on the server rather than
> with multiple calls to the database.
>
> However now when I call user.wherenotes in the view instead of just
> bringing back the wherenotes that I''ve already loaded, it goes and
does
> a fresh call to the database and brings back all the wherenotes for that
> user.
>
> Active record is being too helpful. I really need to cut the connection
> to the database at this point and force the object to work with what
> it''s got rather than do a fresh query.
>
> Any tips anyone.
>
> Tom
>
> --
> Posted via http://www.ruby-forum.com/.
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---