how to modify complex find_by_sql query w/ union into rails 3

Question

here's the current query:

@feed = RatedActivity.find_by_sql(["(select *, null as queue_id, 3 as model_table_type from rated_activities where user_id in (?)) " +  
"UNION (select *, null as queue_id, null as rating, 2 as model_table_type from watched_activities where user_id in (?)) " +  
"UNION (select *, null as rating, 1 as model_table_type from queued_activities where user_id in (?)) " +"ORDER BY activity_datetime DESC limit 100", friend_ids, friend_ids, friend_ids])

Now, this is a bit of kludge, since there are actually models set up for:

class RatedActivity < ActiveRecord::Base
  belongs_to :user
  belongs_to :media
end

class QueuedActivity < ActiveRecord::Base
  belongs_to :user
  belongs_to :media
end

class WatchedActivity < ActiveRecord::Base
  belongs_to :user
  belongs_to :media
end

would love to know how to use activerecord in rails 3.0 to achieve basically the same thing as is done with the crazy union i have there.

Answer 1

It sounds like you should consolidate these three separate models into a single model. Statuses such as "watched", "queued", or "rated" are then all implicit based on attributes of that model.

class Activity < ActiveRecord::Base
  belongs_to :user
  belongs_to :media

  scope :for_users, lambda { |u|
    where("user_id IN (?)", u)
  }
  scope :rated, where("rating IS NOT NULL")
  scope :queued, where("queue_id IS NOT NULL")
  scope :watched, where("watched IS NOT NULL")
end

Then, you can call Activity.for_users(friend_ids) to get all three groups as you are trying to accomplish above... or you can call Activity.for_users(friend_ids).rated (or queued or watched) to get just one group. This way, all of your Activity logic is consolidated in one place. Your queries become simpler (and more efficient) and you don't have to maintain three different models.

Answer 2

I think that your current solution is OK in case of legacy DB. As native query it is also most efficient as your DBMS does all hard work (union, sort, limit).

If you really want to get rid of SQL UNION without changing schema then you can move union to Ruby array sum - but this may be slower.

result = RatedActivity.
             select("*, null as queue_id, 3 as model_table_type").
             where(:user_id=>friend_ids).
             limit(100).all +
         QueuedActivity...

Finally you need to sort and limit that product with

result.sort(&:activity_datetime)[0..99]

This is just proof of concept, as you see it is inefficient is some points (3 queries, sorting in Ruby, limit). I would stay with find_by_sql.