Almaron Almaron - 2 years ago 49
SQL Question

How to select each model which has the maximum value of an attribute for any given value of another attribute?

I have a

model with a
, a
and some other simple fields. I need to display the last 12 works on the page, but only take 1 per user. Currently I'm trying to do it like this:

def self.latest_works_one_per_user(video_id=nil)
scope = self.includes(:user, :video)
scope = video_id ? scope.where(video_id: video_id) : scope.where.not(video_id: nil)
scope = scope.order(created_at: :desc)
user_ids = works = []
scope.each do |work|
next if user_ids.include? work.user_id
user_ids << work.user_id
works << work
break if works.size == 12

But I'm damn sure there is a more elegant and faster way of doing it especially when the number of works gets bigger.

Answer Source

Here's a solution that should work for any SQL database with minimal adjustment. Whether one thinks it's elegant or not depends on how much you enjoy SQL.

def self.latest_works_one_per_user(video_id=nil)
   scope = includes(:user, :video)
   scope = video_id ? scope.where(video_id: video_id) : scope.where.not(video_id: nil)
     joins("join (select user_id, max(created_at) created_at
                    from works group by created at) most_recent
                  on works.user_id = most_recent.user_id and
                    works.created_at = most_recent.created_at").
     order(created_at: :desc).limit(12)

It only works if the combination of user_id and created_at is unique, however. If that combination isn't unique you'll get more than 12 rows.

It can be done more simply in MySQL. The MySQL solution doesn't work in Postgres, and I don't know a better solution in Postgres, although I'm sure there is one.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download