Finding the number of users with one of multiple associations

Ruby on Rails
1

Hi there. I’m trying to find the best way to count the number of Users who have one (or many) instances of a has_many relation.

Here’s an example: User has_many :bank_accounts and :credit_accounts (and a few other relations). I want to find the number of unique Users who have at least one bank_account and at least one credit_account, and ideally implement this inside of a scope so I can run where queries on it.

At the moment I’m implementing it (very poorly) using the following code:

(BankAccount.select(:user_id).uniq + CreditAccount.select(:user_id) + ...).uniq.count

I’ve played around a lot with some joins, however I’m not getting any results. For example, I’ve tried User.joins(:bank_accounts, :credit_accounts).uniq.count however I don’t appear to be getting any results.

Any help would be greatly appreciated, thanks!

2

@zackfern, when you run the User.joins(:bank_accounts, :credit_accounts).uniq.count, what do you get in the console?

3

@pedromoreira I actually get some weird results. For the line above, I get:

User.joins(:bank_accounts, :credit_accounts).uniq.count
(3.9ms) SELECT DISTINCT COUNT(DISTINCT "users"."id") FROM "users" INNER JOIN "bank_accounts" ON "bank_accounts"."user_id" = "users"."id" INNER JOIN "credit_accounts" ON "credit_accounts"."user_id" = "users"."id"
=> 0

Now, if I try User.joins(:bank_accounts).uniq.count I get the desired result of 1 with the SQL query SELECT DISTINCT COUNT(DISTINCT "users"."id") FROM "users" INNER JOIN "bank_accounts" ON "bank_accounts"."user_id" = "users"."id"

Oddly enough, if I try User.joins(:credit_accounts).uniq.count I get 0 (where I should get 1) with the SQL SELECT DISTINCT COUNT(DISTINCT "users"."id") FROM "users" INNER JOIN "credit_accounts" ON "credit_accounts"."user_id" = "users"."id"

Thanks!

4

That’s strange, everything seems ok with SQL generated. I’d remove the .count from the expression and take a look at what is returned to try and figure it out.