CODEWITHSUNDEEP
sqlruby-on-rails-3activerecordincludeinner-join
John R
John R

Reputation: 301

Rails 3 ActiveRecord.. how to join or include tables and get a sum

Here is the relevant data structure and associations:

User (has_many completions)
- id

Quest (has_many completions)
- id
- points [amount that earned if completed]

Completions (belongs_to users and quests)
- user_id
- quest_id

Basically, Completions is a join table populated when a user completes a quest. The part I am struggling with is how to calculate the total points earned, because it has to reach back to the Quest table to see how much each was worth.

Example:

 Quests
  id 1
  points 25
  --
  id 2
  points 10
  --
  id 3
  points 50

 Completions
  user_id 1
  quest_id 1
  --
  user_id 1
  quest_id 2
  --
  user_id 2
  quest_id 3

In this example, user_id 1 has earned 35 total points (25+10), and user_id 2 has earned 50 total points.

So two questions:

1) How would I write a query in Rails to allow me to list all users and their total points?

2) Is this the best way to handle this type of functionality?

Thank you!

Upvotes: 1

Views: 843

Answers (1)

coreyward
coreyward

Reputation: 80051

Pretty simple to do with a quick reduce and a query. 

class User < ActiveRecord::Base
  has_many :completions
  has_many :quests, :through => :completions

  attr_accessor :total_points

  # calculates points for individual user
  def points
    @total_points ||= quests.reduce(0) { |sum, quest| sum + quest.points }
    # note this doesn't reload, so if you add a completion without reloading the
    # user object this will be stale
  end

  # when getting a list of users, make sure you use an efficient query to
  # fetch all of the associated quests
  # 
  # this is the same as using `scope :with_quests, include(:quests)`
  def self.with_quests
    include(:quests)
  end
end

# outside of your model:
@users = User.with_quests.all

# and in a view (e.g. _user.html.erb):
Points: <%= user.points %>

Cheers!

Upvotes: 1

Related Questions