Rails - Jun 2006 - How can I minimize SQL queries

I have users, who have friends that are also users in mysql database.

I have a foreign key for both users, so that I can reference their data 
directly.  My problem is displaying friends.  My for statement would 
look something like this:

for i in(0..@user.friends.length)
	puts @user.friends[i].user.name
end

so @user is the user that i''m displaying friends for... .friends are
the
entries that he is in... then .user are the users connected to him in 
the database, and .name would be their name.  But if a user has 200 
friends then the for statement will loop through that statement 200 
times and do 200 queries to grab the name.  How can I grab all of the 
usernames in one SQL statement utilizing my fk relationships?

thanks,
Ben

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

Get all your users and their friends:

User.find(:all, :include => ["friends"])

Assuming that your relationships are setup correctly in your models.

~ Ben

On 6/26/06, Ben Lisbakken <lisbakke@gmail.com>
wrote:
>
> I have users, who have friends that are also users in mysql database.
>
> I have a foreign key for both users, so that I can reference their data
> directly.  My problem is displaying friends.  My for statement would
> look something like this:
>
> for i in(0..@user.friends.length)
>         puts @user.friends[i].user.name
> end
>
> so @user is the user that i''m displaying friends for... .friends
are the
> entries that he is in... then .user are the users connected to him in
> the database, and .name would be their name.  But if a user has 200
> friends then the for statement will loop through that statement 200
> times and do 200 queries to grab the name.  How can I grab all of the
> usernames in one SQL statement utilizing my fk relationships?
>
> thanks,
> Ben
>
> --
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>


-- 
Ben Reubenstein
303-947-0446
http://www.benr75.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060626/082510d8/attachment.html

Ben Reubenstein wrote:
> Get all your users and their friends:
> 
> User.find(:all, :include => ["friends"])
> 
> Assuming that your relationships are setup correctly in your models.
> 
> ~ Ben
Yep, that''s exactly what I was looking for.  Thanks Ben.  For anyone 
else who wants to know how to do a grab of 200 items in one statement 
using Rails, check these out:

Eager Loading of Associations:
http://rubyonrails.org/api/classes/ActiveRecord/Associations/ClassMethods.html

and a little documentation on include:
http://rubyonrails.org/api/classes/ActiveRecord/Base.html

-Ben Lisbakken

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