rails 3 getting the count of the records that have more than one associate records (has_many)

Question

I can get the collection of accounts that have more than one user:

Account.group('accounts.id HAVING count(users.id) > 1').joins(:users)

But, as soon as I call .count on that object, I get a huge explosion:

(0.3ms) SELECT COUNT(*) AS count_all, accounts.id HAVING count(users.id) > 1 AS accounts_id_having_count_users_id_1 FROM "accounts" INNER JOIN "users" ON "users"."account_id" = "accounts"."id" GROUP BY accounts.id HAVING count(users.id) > 1 ActiveRecord::StatementInvalid: PG::Error: ERROR: syntax error at or near "AS" LINE 1: ...unt_all, accounts.id HAVING count(users.id) > 1 AS accounts...

It seems that in postgres, the actual query I want is:

select count(*) from (SELECT accounts.id FROM "accounts" INNER JOIN "users" ON "users"."account_id" = "accounts"."id" GROUP BY accounts.id HAVING count(users.id) > 1) as a;

How can I get activerecord to generate this (or a comparable) query?

Answer 1

active record supports 'having' as a method. So you could do your query this way:

Account.joins(:users).select('accounts.id').group('accounts.id').having('count(users.id) > 1')

Answer 2

Why dont you try this from the User model, where you group_by the account_id from user model

User.count(:group => :account_id) This might return a hash showing {:account_id => count_of_users } For example, {1 => 3, 2 => 5, 3 => 2}

Now pick the account_id's that have user count greater than 1.