Trying to find a count of items from a two level deep association, any ideas?

Question

I am working on an application where I need to find the count of submitted items by users that have been referred by a user.

For Example -

User1 has referred 3 people (User2, User3, User4) and each of those users has submitted 5 articles.

I am trying to find a way to get the count of submitted items in User1's tree (should be 15 in this case).

My user model looks like the following (simplified)

class User < ActiveRecord::Base
  # Code for user referrals
  belongs_to :referrer, :class_name => "User"
  has_many :referrals, :class_name => "User", :foreign_key => "referrer_id"
  has_many :items
end

I can find out the count for each user easily (User.items.size), but I am having trouble finding a solution to get the referral counts as one sum.

Any ideas?

Answer 1

Get all items of User with id of 1

total = 0
Users.find(1).referrals.each do |refer|
   total += refer.items.size
end

Answer 2

Try this:

user = User.find(1)
total_items_size = user.referrals.map(&:items).flatten.size

Answer 3

You can use select_value to manually run the SQL query:

def referred_items_count
  select_value("select count(*) as referred_items 
  from items inner join users on users.id = items.user_id 
  where users.referrer_id = #{self.id};", "referred_items")
end

The benefit is that it is a lot more scalable than using Ruby to count.