CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
Andrew
Andrew

Reputation: 800

Query nested count

I have two models, a User and an Exercise where a user has_many :exercises. There is a cache_counter on the User model, exercise_count

I want to make a leaderboard based on a user's exercise count in the current month. I want to display the 2 users with the highest exercise counts of the month along with the count. If there are other users with the same exercise count as the 2nd place user I want to display them too. (Display 2+ users)

The current query I have,

# User model
scope :exercises_this_month, -> {  includes(:exercises).references(:exercises)
  .where("exercise_count > ? AND exercises.exercise_time > ? AND exercises.exercise_time < ?", 
  0 , Time.now.beginning_of_month, Time.now.end_of_month) 
}

def User.leaders
  limit = User.exercises_this_month.where("exercise_count > ?", 0).order(exercise_count: :desc).second.exercise_count
  User.exercises_this_month.where("exercise_count > ? AND exercise_count >= ?", 0, limit)
end

will return the top 2+ user object for all time. I want to limit this to the current month.

Upvotes: 0

Views: 309

Answers (2)

Luie7
Luie7

Reputation: 89

gem 'groupdate'
   model 
   def self.by_month(month_num)
       self.where("cast(strftime('%m', created_at) as int) = #
       {Date.today.strftime("%m")}")
   end
   controller 
      def report
         @posts =Post.by_month(Date.today.strftime("%m"))
       end
   view
   some think like this 
      = @posts.group_by_month(:created_at).count

I am not sure what you want but this will be big help hopefully enjoy :)

Upvotes: 0

EJAg
EJAg

Reputation: 3298

You can't use the counter_cache here as it stores the number from all time. Another issue you have is that if you have multiple players having the same highest number of exercises, you'll miss the ones with the second highest number.

# Exercise.rb
scope :exercises_this_month, -> {  
  where("exercises.exercise_time > ? AND exercises.exercise_time <= ?", 
  Time.now.beginning_of_month, Time.now.end_of_month) 
}

#User.rb
players_this_month = User.joins(:exercises).group(:user_id).merge(Exercise.exercises_this_month).count ## 

sorted_counts = players_this_month.values.uniq.sort

if sorted_counts.present?
  second_place_counts = sorted_counts[-2] || sorted_counts[-1]  #in case all have the same number
  top_two_ids = players_this_month.map { |k,v| k if v >= second_place_counts }  ##user ids with top two numbers
end

Upvotes: 1

Related Questions