Eager loading using find_by_sql

rubyonrails-talk
1

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

2

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!

3

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