Complex Join Queries in Rails

Question

Hi,

I have 3 tables - venues, users, and updates (which have a integer for rating) - and I want to write a query that will return a list of all my venues as well as their average ratings using only the most recent update for each person, venue pair. For example, if user 1 rates venue A once at 9 am with a 4, and then rates it again at 5 pm with a 3, I only want to use the rating of 3, since it's more recent. There are also some optional conditions, such as how recent the updates must be, and if there is an array of user ids the users must be within.

Does anybody have a suggestion on what the best way to write something like this is so that it is clean and efficient? I have written the following named_scope which should do the trick, but it is pretty ugly:

named_scope :with_avg_ratings,  lambda { |*params|
hash = params.first || {}
has_params = hash[:user_ids] || hash[:time_ago]
dir = hash[:dir] || 'DESC'
{
  :joins => %Q{
    LEFT JOIN (select user_id, venue_id, max(updated_at) as last_updated_at from updates 
    WHERE type = 'Review' GROUP BY user_id, venue_id) lu ON lu.venue_id = venues.id
    LEFT JOIN updates ON lu.last_updated_at = updates.updated_at 
    AND updates.venue_id = venues.id AND updates.user_id = lu.user_id
  },
  :select => "venues.*, ifnull(avg(rating),0) as avg_rating", 
  :group => "venues.id",
  :order => "avg_rating #{dir}",
  :conditions => Condition.block { |c|
    c.or { |a|
      a.and "updates.user_id", hash[:user_ids] if hash[:user_ids]
      a.and "updates.updated_at", '>', hash[:time_ago] if hash[:time_ago]
    } if has_params
    c.or "updates.id", 'is', nil if has_params
  }

}

}

I include the last "updates.id is null" condition because I still want the venues returned even if they don't have any updates associated with them.

Thanks, Eric

Answer 1

Yikes, that looks like a job for find_by_sql to me. When you're doing something that complex, I find it's best to take the job away from ActiveRecord and DIY.