CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
Oliver
Oliver

Reputation: 1181

Group and sum in Rails

I am developing an app which is a card game. Each card is stored in a table (cards) and has a certain number of points. If a user wins a card he earns the points of this card. In this case a record is saved in the user_card table holding card_id, user_id and success = true (if he loses the card it is success = false).

card.rb

class Card < ActiveRecord::Base
  has_many :user_cards
  has_many :users, through: :user_cards
end

user_card.rb

class UserCard < ActiveRecord::Base
  belongs_to :user
  belongs_to :card
end

What I want to do now is to create a leaderboard. Therefore I need to:

  1. Group all user_cards where success = true by user_id
  2. Sum all points (which are stored in the card table) for successful user_cards by user
  3. Display username (from user table) and sum of points for this user

I tried some approaches like group_by(&:something), joins etc. However, I cannot make it work. I don't have problems when I have to group and sum stuff from a single table. However, I don't know how to get the username from the one and the points from the other table summed up based on the user_card table.

How can I achieve this?

Upvotes: 2

Views: 187

Answers (1)

jimworm
jimworm

Reputation: 2741

users = User.joins(:cards).
             select(:username, 'sum(cards.score) as score').
             where(user_cards: {success: true}).
             group(:id).
             order('score desc')
#=> #<ActiveRecord::Relation [#<User username: 'bob'>]>

users.first.username
#=> "bob"

users.first.score
#=> 15

Upvotes: 2

Related Questions