s

Posts Tagged with "eager loading"

Rails eager loading of associations

Feb 18, 2007  -  Comments

I ran into an interesting problem with table associations at work the other day. Below is a simple data model of the tables I was working with. Basically, here's how it breaks down: A user is part of any given group (but only one group per user). A document (any type) can be uploaded and assigned to a folder. A group is used to assign viewing privileges to each document, so that every user in the group can see the document. See below:

simple data model

The problem came up because once a user is logged in, I need to get all of the folders that contain documents that the user's group has permission to see (a folder could contain documents not viewable by the group). I spent a few minutes scratching my head on how best to do a find to get the results I needed. I really couldn't come up with anything that would work.

So, of course, when this happens, I do a little leg-stretching and walk over to Adam and Shawn's space to ask them how they would do it. After explaining the situation and a little white board art, they reminded me of eager loading of associations (which I've never really had to use).

By using the :include option in my find method call, I can pre-load table associations to make my complex query a lot easier. My code ended up looking like this:

# find the logged-in user
user = User.find(session[:intranet_user])
# get all of the folders that contain documents they have access to
@folders = Folder.find(:all, :include => {:documents => {:groups => :users}}, :conditions => "users.id = #{user.id}" )

This worked perfectly!

A note about eager loading of associations: they can save a lot of resources when used correctly. Say you have a table called books and a table called authors, and each book has one author. To get all of the books for display, you might put:

@books = Book.find(:all)

Then, in your page, you might have:

<% for book in @books -%>
	Title: <%= book.title %>
	Written by: <%= book.author.name %>
<% end -%>

Not only do you run a SQL query to get all of the books, you then run an additional query for each book to find it's author. You can simplify this down to only one query, by using eager loading, like so:

@books = Book.find(:all, :include => :author)

This pre-loads all of the data with only one SQL query.

Tagged: railseager loadingactiverecorddbtutorial