Geek asks and answers

Rails 3 getting count of records containing more than one associated record (has_many)

I can get a collection of accounts with multiple users:

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

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

(0.3 ms) 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 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 request I want:

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 create this (or comparable) query?

+5
source share
2 answers

an active record supports "availability" as a method. So you can make your request this way:

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

Why aren't you trying to do this from the User model, where you are group_by_ account_id from the user model

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

Now select account_id with more than 1 users.

0
source

More articles:


All Articles