CODEWITHSUNDEEP
sqlruby-on-railsruby-on-rails-3postgresqldistinct
keruilin
keruilin

Reputation: 17512

Rails - retrieve distinct records

I have the following has_many and belongs_to relationship:

class Trophy < ActiveRecord::Base
  has_many :awards
end

class Award < ActiveRecord::Base
  belongs_to :trophy
end

The Award model has a user_id field and a trophy_id field.

For an instance of a trophy, I want to return the 5 most recent, but distinct user awards.

I could do:

def recent_awards
  trophy_awards = awards.recent.includes(:user)
  trophy_awards.uniq! {|a| a.user_id}
  trophy_awards[0...4]
end

However, it's not efficient because I'm dealing with a lot of records.

Currently, I'm doing this:

def recent_awards
  trophy_awards = awards.limit(50).recent.includes(:user)
  trophy_awards.uniq! {|a| a.user_id}
  trophy_awards[0...4]
end

Problem is, it won't give me 5 distinct user awards if the last 48 awards were to the same user.

What's the best way to go about returning the 5 most recent, but distinct user awards?

Upvotes: 0

Views: 115

Answers (2)

RustyToms
RustyToms

Reputation: 7810

Try this:

Award.find_by_sql("SELECT *
  FROM awards a1 INNER JOIN (
    SELECT MAX(created_at) AS recent
    FROM awards
    GROUP BY user_id) a2
  ON (a1.created_at = a2.recent)
  ORDER BY a1.created_at DESC
  LIMIT 5;")

Upvotes: 0

Brad Werth
Brad Werth

Reputation: 17647

def recent_awards
  awards.limit(5).recent.includes(:user).uniq
end

http://apidock.com/rails/ActiveRecord/QueryMethods/uniq

Upvotes: 2

Related Questions