Almaron Almaron - 7 months ago 24
SQL Question

selecting distinct first by one field

I have a

Work
model with a
video_id
, a
user_id
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
end
works
end


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

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)
   scope.
     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)
end

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.