CODEWITHSUNDEEP
ruby-on-railsruby
Brennan
Brennan

Reputation: 5732

How to find a record that is associated to only certain other record?

I am searching for users in my system who are associated to only a specific list of reports.

Each user can be associated to many different reports. I have three reports that I need to treat differently than others. Let's say they have ids of 1, 2, and 3.

I tried this:

User.joins(:user_reports).where(active: true, user_reports: {report_id: [1,2,3]})

This is close, but it finds users who are associated to the reports I am looking for, but it also finds those who are associated to those reports and other reports as well.

How do I find those users who are associated to only those specific reports, or any combination thereof?

Upvotes: 0

Views: 48

Answers (1)

SHS
SHS

Reputation: 7744

The following will help:

disallowed_user_ids = UserReport.
                      where("report_id NOT IN (?)", report_ids).
                      pluck("DISTINCT user_id")

User.
joins(:reports).
where("users.id NOT IN (?)", disallowed_user_ids).
group("users.id")

Might also be possible with an ugly subquery:

User.
joins(:reports).
where("users.id NOT IN (
  SELECT user_id FROM user_reports WHERE report_id IN (?)
)", report_ids).
group("users.id")

If each user in your DB has at least one report, then you can drop the joins and group.

Update:

The following is also an option:

report_ids_str = report_ids.join(',')

User.
select("COUNT(r2.id) AS r2c, #{User.column_names.join(',')}").
joins("INNER JOIN reports AS r1 ON (
  r1.user_id = users.id AND r1.id IN (#{report_ids_str}))").
joins("LEFT OUTER JOIN reports AS r2 ON (
  r2.user_id = users.id AND r2.id NOT IN (#{report_ids_str}))").
group("users.id").
where("r2c = 0")

You might be able to replace the select and where with a having("COUNT(r2.id) = 0").

Upvotes: 1

Related Questions