Eager loading using find_by_sql

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

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!

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