CODEWITHSUNDEEP
ruby-on-railsactiverecord
new2cpp
new2cpp

Reputation: 3667

Rails: ActiveRecord query where all association is/is not a value

Take Rails: ActiveRecord query based on association value for example,

If we want to find reports with any related servers' company_id is 5, we can write as Report.includes(:servers).where(servers: {company_id: 5})

Given a report has multiple related servers, some server company_id is 5, some company_id is 6.

let's say,

How to find reports with all related servers's company_id is 5?(only all related servers's company_id is 5, then the report is qualified)

in the above case, the report_b is the answer

And find reports with all related servers's company_id is not 5?(only all related servers's company_id is Not 5, then the report is qualified)

in the above case, the report_c is the answer

Can I achieve by use where only?

Upvotes: 1

Views: 1073

Answers (2)

Ilya Konyukhov
Ilya Konyukhov

Reputation: 2781

I'm very doubtful that it can be done using where ONLY. But your example also contains includes call, so I assume you meant "is it can be done using ActiveRecord methods, with as less raw SQL as possible". Well, it's achievable.

How to fetch reports having only servers with given company_id

It's a classical SELECT query with INNER JOIN between reports and servers. Since we need to look through all servers of a report, we GROUP reports by their id, and add HAVING to find max and min of servers.company_id for given report. The reports we're looking for have max(company_id) == min(company_id) == 5:

company_id = 5
Report.joins(:servers)
      .group('reports.id')
      .having("max(servers.company_id) = #{company_id} AND min(servers.company_id) = #{company_id}")

How to fetch only reports whose servers do not have given company_id

The best way to do so is to fetch those reports who do not match given condition (ie reports having servers with given company_id) and then exclude them. Thus we could fetch reports having servers with another company_ids, as well as reports without servers.

Report.where.not(
  id: Report.joins(:servers).where(servers: { company_id: 5 })
)

Upvotes: 1

Wizard of Ogz
Wizard of Ogz

Reputation: 12643

How to find reports with all related servers's company_id is 5?

This can be done by first finding the set of reports which have at least one server with company_id = 5. Then simply find the reports which are NOT in that set.

server_conditions = Server.where.not(company_id: 5)
  .or(Server.where(id: nil))  # Include reports which have NO servers.

reports_to_exclude = Report
  .left_joins(:servers)  # see notes about joins vs left_joins
  .merge(server_conditions)

Report.where.not(id: reports_to_exclude)  # ActiveRecord automatically selects `id` from reports_to_exclude, but it could be done explicitly, id: reports_to_exclude.select(:id)

joins vs left_joins

If there are reports records which have NO associated servers records then joins will yield a different result than left_joins. left_joins will exclude such reports while joins will include them.

It is up to you to determine which behavior is correct. If you know that you will never have a report which has no associated server, then either way will work (using joins is probably best, but you should always look at database-specific performance implications).

find reports with all related servers's company_id is not 5?

The principle is the same here. First, find the set of reports which have a server with company_id of 5. Then select all the other reports which are not in that set.

reports_to_exclude = Report.joins(:servers).where(servers: { company_id: 5 })
Report.where.not(id: reports_to_exclude)

Can I achieve by use where only?

This is as close as I can get you.

Upvotes: 0

Related Questions