Most Efficient Way to Get Counts of Users with Certain Attributes in Ruby

Question

I have a collection of users with various statuses: active, disabled, or deleted (as an enum). I want a count of users with each status as well as a count of the total number of users. What is the most efficient way for me to do that?

I've read the questions on size vs. length vs. count in Ruby and that makes me think I should load all of the user records and then iterate over the collection multiple times to get the length of each status array.

This is what my code looks like currently:

# pagination code omitted...

all_users = User.all
total_count = all_users.length
active_count = all_users.select {|u| u.status == User.statuses['active']}.length
disabled_count = all_users.select {|u| u.status == User.statuses['disabled']}.length
deleted_count = all_users.select {|u| u.status == User.statuses['deleted']}.length

The requests from the client take about 1.25-1.5 seconds as written for 1,000 users.

I've also tried making multiple DB queries with code like this:

# pagination code omitted...

total_count = User.count
active_count = User.where(status: User.statuses['active']).count
disabled_count = User.where(status: User.statuses['disabled']).count
deleted_count = User.where(status: User.statuses['deleted']).count

That might be marginally faster by ~100ms. Is there a faster way to do this?

I'm not sure if it is relevant, but for background info: I am using Rails as an API in this context to an AngularJS frontend. I am using Kaminari to paginate the collection, but I still need counts of each status. I am in a B2B environment so it is unlikely that any instance will have more than 1,000 users. I don't need to scale higher than that.

Thanks in advance!

score 2 · Accepted Answer · answered Aug 17 '15 at 17:09

2

Do it all at once, in the database by grouping your count query.

User.group(:status).count

Then to get the total number of users just sum the result. Here's an example from one of my tables. Here I'm grouping on a boolean field, but you can group on whatever you want.

> Course.group(:is_enabled).count
=> {false=>46, true=>26524}

answered Aug 17 '15 at 17:09

Philip Hallstrom

19,673
2
42
46

Definitely the fastest. – David Aldridge Aug 17 '15 at 18:02

score 0 · Answer 2 · answered Aug 17 '15 at 17:09

That might be marginally faster by ~100ms.

Create an index on your 'status' column in your database:

# in your terminal
rails g migration AddIndexOnStatusOfUsers

# in db/migrate/xxxxx_add_index_on_status_of_users.rb
def change
  add_index :users, :status
end

score 0 · Answer 3 · answered Aug 17 '15 at 17:59

You should benchmark them all and let us know. Would be interesting. Pure SQL answers are always more scalable of course...

u = User.select('user.status')
active_count = 0
disabled_count = 0
deleted_count = 0
u.each do |u|
  if u.status = 'active'
    active_count += 1
  elsif u.status = 'deleted'
    deleted_count +=1
  else
    disabled_count +=1
  end
end

Most Efficient Way to Get Counts of Users with Certain Attributes in Ruby

3 Answers3