Eager loading using find_by_sql

Rails
1

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.

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

From my controller I call

@users = @team.find_users_and_notes_from_date(@date)

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

2

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 S. [email protected] wrote:

 has_many :wherenotes

The dreaded n+1 problem.
Inside my team model I have written the following function.
end
Somewhere along the lines it’s all going wrong and I’m a little far out

–
Ed Hickey
Developer
Litmus Media
816-533-0409
[email protected]
A Member of Think Partnership, Inc
www.ThinkPartnership.com
Amex ticker symbol: THK

3

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

4

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.
:slight_smile:

Good luck!

5

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 S.
http://somethinglearned.com

6

Brian H. 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