Complex Queries in Rails

Question

Rails and ActiveRecord do a perfectly nice job of dealing with what I would consider exceedingly simple queries that deal with a single model. I now have a situation where I need to do something slightly more complex - but still trivial - and I don't know how to do it.

I have a User that has_many :images. Each Image has_many :thumbnails.

On my /images index page, I actually want to display the small thumbnail for each image that belongs to the logged in user. An easy query, but because doing so involves conditions on multiple tables, I'm not sure how to approach it in true Rails fashion. I'd prefer to avoid writing SQL and dealing with the ensuing headaches.

I see that rails offers the :joins option, but that seems like a pretty inelegant afterthought. Is this the best way to meet this kind of requirement or is there a better way that I just haven't found?

Thanks.

UPDATE: I've been told about named scopes and have to admit to having some very inappropriate feelings for them right now. These allow fairly complex conditions to be applied very elegantly with lots of syntactic sugar. For example, I can create a named scope on my Image model for images owned by a given user and make it dynamic:

class Image < ActiveRecord::Base
  named_scope :owned_by, 
              lambda { 
                |user_id| { 
                  :conditions => { :user_id => user_id } 
                } 
              }
end

I can also apply a named scope on my Thumbnail model to specify the small thumbnail:

class Thumbnail < ActiveRecord::Base
  named_scope :small, :conditions => { :name => 'Small' }
end

Now I can chain those together to do some pretty powerful stuff that reads beautifully. In my controller, I can return all of the images for a given user:

@images = Image.owned_by( current_user )

In my view, though, I want to display the small thumbnail, so we chain mightily:

<% for image in @images %>
  <tr>
    <td><%= h( image.name ) %></td>
    <td><%= link_to( image_tag( image.thumbnails.small.first.binary.uri, :alt => h( image.name ), :title => h( image.description ) ), :action => :show, :id => image.id ) %></td>
  </tr>
<% end %>

Check out the image_tag. For each image, it's identifying the first small thumbnail then chaining that to retrieve its physical file location. It's not exactly what I was looking for since it requires an additional database hit for each image, but it's probably more accurate for my needs in this particular instance.

Answer 1

The RailsGuides are always handy for this, I've spent a good amount of time there.

Check out the has_many :through assocation which allows for the Images model to join these tables and allow for additional fields related to the Image.

Thumbnail
has_many :images
has_many :users, :through => :images

Image
belongs_to :user
belongs_to :thumbnail

User
has_many :images
has_many :thumbnails, :through => :images

Then when accessing the Thumbnails related to the User you could do;

@user.thumbnails

Or maybe a Thumbnail should have one Image and one User?

Thumbnail
belongs_to :image

Image
belongs_to :user
has_one :thumbnail

User
has_many :images
has_many :thumbnails, :through => :images

Here's the example I gleaned this from.

Answer 2

for a particular user you can find all the thumbnails by doing something like this

@user = User.find(:id, :include => :thumbnails)

:thumbnails must be defined in the user model as an association like revgum has shown in his answer.

Answer 3

To write complex queries beautifully you could also use something like squirrel. Squirrel is a plugin that allows you to write complex queries in a more natural fashion:

User.find(:all) do
  first_name == "Jon"         # first_name = 'Jon'
  email =~ "%@thoughtbot.com" # email LIKE '%@thoughtbot.com'
  created_at >= 4.years.ago   # created_at >= '2004-06-27 10:34:22'
  awesomeness <=> (1..10)     # awesomeness BETWEEN 1 AND 10
  banned_since == nil         # banned_since IS NULL
end

You can even make use of associations (which is what you need):

Post.find(:all) do
  user.first_name.contains? "Jon" 
end

More information at Squirrel - Natural Looking Queries for Rails. Also, be sure to check out their wiki page on Github.