ActiveRecord counting with conditions over two tables

Question

After watching, the latest RailsCasts with the include and joins information, I know I can do what I am doing in a much more efficient way. I have a very simple User model and a Status model. The User belongs to Status and the Status has many Users. I need to count how many users have a specific kind of status, this creates a new SQL count query for every single status and I know that this is not a good way to do it. It looks like this right now.

statuses = Status.all

statuses.each do |status|
  status.users.count
end

I end up with 4 queries of:

SELECT count(*) AS count_all FROM "users" WHERE ("users".status_id = 1)
SELECT count(*) AS count_all FROM "users" WHERE ("users".status_id = 2)

It goes on like that for as many different statuses as exist in the database. The big problem is that now I need to filter by another association as well which is Organization. So I need to find the count for all users who have a certain status in a certain organization. This ends up quadrupling the amount of queries I am making and feels horrible. I'm not sure what kind of join I could use to cut down on this or what I could possibly do to fix this. Thanks for the help :)

Answer 1

You can try plain SQL:

SELECT s.name,COUNT(u.id) AS users_count FROM statuses s, users u WHERE s.id=u.status_id GROUP BY s.id;

Answer 2

Ok so I am answering my own question, just in case anyone has the same issue.

Status.all(:joins => :users,
           :select => "statuses.*, count(users.id) as users_count",
           :group => "statuses.id")

This returns every status that has users and the count of the users as users_count. In order to further refine the query and only count users that belong to a certain organization the query changes to this.

Status.all(:joins => :users,
           :select => "statuses.*, count(users.id) as users_count",
           :conditions => {:users => {:organization_id => ORG_ID_HERE}},
           :group => "statuses.id")

I hope this helps anyone with the same issue and thanks to Eimantas, and Ryan Bates(RailCasts).

Answer 3

Out of interest, what does your Status model contain? Does it really need to be its own model? I'm guessing here, but you may want to consider implementing a finite state machine. There are a number of rails plugins that make it easy to implement an FSM e.g. acts_as_state_machine