s

Posts Tagged with "db"

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

Ruby on Rails having problems with table column names

Jun 15, 2006  -  Comments

I was working on a project for work today that invloved the client be able to enter customer testimonials into a database to be displayed randomly on each site page. I named the table quotes and it had three columns: id, person, and quote. I made id an integer and quote and person were both varchars. Everything worked fine with this configuration.

After some testing, I discovered that a varchar wasn't the best type for the quote because it didn't have enough room for the longer testimonials. Like a good Rails developer, I wrote a migration script to change the quote column to be text instead of varchar. Well, after making this change, Rails bombed every time I tried to add another quote. For some reason, it didn't like the column to be of type text and have the same name as the Rails table object. I'm not exactly sure what or why specifically caused this problem, but it definitely didn't work. After some consultation with The Shawnami, the resident Rails guru, and some extensive frustration, we finally decided to try and just rename the column. Guess what? It worked perfectly after I renamed it content.

Tagged: railsdb