CODEWITHSUNDEEP
ruby-on-railsrubyarrays
Brian McDonough
Brian McDonough

Reputation: 14509

Find the highest Score from each User from Submissions

I am trying to write a method, unsuccessfully so far.

I have three models interacting: User, Submission and Score

Score has a submission_id, Submission has a user_id.

Users submitted anywhere from 1-5 submissions and then they received a score on each of those submissions. Now I need to find the highest score of all the submissions per each user.

For example, if user 1 submitted 2 photos and the 1st submission scored 25 and the second scored 50, I need the method to return 50 for that user.

I need to improve my skillset when it comes to arrays and ranges, but here is the speedy response one of my team members passed to me before he ran out the door:

"The way I would have done it is to go over all Submissions and store each in a hash with the key being the user_id and the value an array with the score. So if you have 2+ submissions for the same user_id, it will point to an array of scores. Once you have the hash, just go over each element of the array and pick the highest score. Then you store the score in another hash, but this time the score is the key and the value is an array of all the user_id with that score."

If you can help me out, I will be much obliged. I'm not lazy, just exhausted and I can't think straight anymore.

Here is the relevant information:

User Model:

class User < ActiveRecord::Base
  has_many :submissions, :dependent => :destroy
end

Submission model:

class Submission < ActiveRecord::Base
  attr_accessible :contest_id, :description, :title, :user_id, :video, :image_attributes,
                  :comment_show
  default_scope order: 'submissions.created_at DESC'
  belongs_to :user
  has_one :score, :dependent => :destroy
end

Score model:

class Score < ActiveRecord::Base
  attr_accessible :effort, :innovation, :passion, :photo_contest_1, :reputation, 
                  :scorable_id, :scorable_type, :technical, :uniqueness, :submission_id,
                  :sub_total

  belongs_to :submission
end

[Edit]

So, I am using a helper_method :contest_score in the application controller, like this:

def contest_score
   User.joins(submissions: :score).maximum(:sub_total, group: 'users.id')  
end

Or: 

def contest_score
  hashed_scores = Submission.joins(:score)
                      .select('max(scores.sub_total) as max_score, submissions.user_id as user_id')
                      .group('submissions.user_id')
                      .map { |subm| Hash[subm.user_id, subm.max_score] }
                      .reduce(&:merge)
end

Then, I am rendering in the user show template as each members high score in the contest:

 <%= contest_score %> contest points<br/>

Upvotes: 0

Views: 785

Answers (2)

jvnill
jvnill

Reputation: 29599

This should do the trick

scores = User.joins(submissions: :score).maximum(:sub_total, group: 'users.id')

This will result in a hash with keys equal to the user_id and with values equal to the highest score out of all the submissions.

UPDATE:

Here is how to get the results joined by highest scores

scores.inject({}) do |hash, (user_id, score)|
  hash[score] ||= []
  hash[score]  << user_id
  hash
end

UPDATE: No need to go from User

Score.joins(:submission).maximum(:sub_total, group: 'submissions.user_id')

Upvotes: 1

Ismael
Ismael

Reputation: 16730

I believe you can do this. It will use one query and give you the data you need.

hashed_scores = Submission.joins(:score)
                          .select('max(scores.sub_total) as max_score, submissions.user_id as user_id')
                          .group('submissions.user_id')
                          .map { |subm| Hash[subm.user_id, subm.max_score] }
                          .reduce(&:merge)

This should be away more efficient then your solution since it only hits the database one time and also there is no need to sort the scores and select only the max ones since the database can do it for you.

Upvotes: 1

Related Questions