Rails efficient way to find user with only one shareholder

Question

In my method I want to check if user has only one shareholder, if it so it should return true (later on it's used in if block). Basically it should reflect something like User.find_by(id: user.id).shareholder.count < 1 because it overloads the database with unnecessary queries (I have db with ~30k users).

What I was thinking is to create queries with where so I have this one:

def one_shareholder?(shareholder)
  ShareholdersUser.where(user_id: shareholder.owner_id).
                   where.not(shareholder_id: shareholder.id).exists?
end

But I don't know how to implement query which will be counting if this user has only one shareholder. Should I use something like find_each ?

Edit:

user has_many :shareholder

shareholder belongs_to :owner

ShareholdersUser belongs_to :user and :shareholder

can you please explain the association between your tables so that we can understand properly? — Vishal, Apr 25 '19 at 06:36
Read more on counter cache https://guides.rubyonrails.org/v5.2/association_basics.html#options-for-belongs-to-counter-cache — Deepak Mahakale, Apr 25 '19 at 07:02
It works @Vishal but like I mentioned - I don't want to overloads the database with unnecessary queries. It should be really fast, this db has close to 30k users and your solution is pretty much the same as I mentioned in my example. — mr_muscle, Apr 25 '19 at 07:06

score 1 · Answer 1 · answered Apr 25 '19 at 07:42

Maybe this can give you an hint. I used something similar in a project where I have these models:

class Company < ApplicationRecord
  has_many :permits
end

and

class Permit < ApplicationRecord
  belongs_to :company
end

For fetching companies having just one permit I used:

Company.joins(:permits).group('companies.id').having('count(company_id) = 1')

Maybe you can pluck the ids and use the array to check wether the company is in the array. For example:

ids_of_companies_having_one_permit = Company.joins(:permits).group('companies.id').having('count(company_id) = 1').pluck(:id)

Then check:

if ids_of_companies_having_one_permit.include? company.id ....

This is the thread I followed: Find all records which have a count of an association greater than zero

Vishal · Answer 2 · 2019-04-25T06:53:47.140

0

Firstly, if you are finding user from his ID then can directly use User.find(user.id) instead of User.find_by(id: user.id)

Secondly, As you mentioned in your question that you want either true/false for your if condition.

And as per your query I think you have user has_many shareholder association implemented.

So you can directly use User.find(user.id).shareholders < 1 in your if condition like below,

if User.find(user.id).shareholders.count < 1
     #do something...
end

Note: I've used the plural of the shareholder in condition because we have has_many association

edited Apr 25 '19 at 06:53

answered Apr 25 '19 at 06:45

Vishal

818
8
20

Yes but if you have more than 20k or 30k users, I think this query is not very efficient. It does pretty much the same as `User.find_by(id: user.id).shareholder.count < 1` which I mentioned earlier. – mr_muscle Apr 25 '19 at 06:57

Rails efficient way to find user with only one shareholder

2 Answers2