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