Finding the number of users with one of multiple associations

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:

( + + ...).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!

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

@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"


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.