1

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,

  • report_a has 2 servers, with company_id, 5,6 separately
  • report_b has 2 servers, both with company_id, 5
  • report_c has 2 servers, both with company_id, 6

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?

new2cpp
  • 3,311
  • 5
  • 28
  • 39

2 Answers2

1

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 })
)
Ilya Konyukhov
  • 2,666
  • 1
  • 12
  • 21
  • Your 2nd request does not need to be two separate queries! You can remove `pluck(:id)` (ActiveRecord will implicitly do `select(:id)` instead). That results in a single query. – Wizard of Ogz Oct 19 '18 at 10:36
  • I love the solution using GROUP BY and HAVING, I wouldn't have thought of that. I will keep that in mind for the future (although I would need to research and benchmark performance). – Wizard of Ogz Oct 19 '18 at 10:38
  • 1
    Wow, I'm impressed that ActiveRecord joins these 2 relations in a single SQL call. Thanks for pointing this out to me. – Ilya Konyukhov Oct 19 '18 at 13:47
0

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.

Wizard of Ogz
  • 12,543
  • 2
  • 41
  • 43