Filter in Rails a table with information of another table that belongs

I have a report that belongs_to a machine, and the machine belongs_to a client. I have a page where I see all the reports.

#report_controller.rb

def index
    @reports = Report.all
end

#index.html.erb

 <% @reports.each do |report| %>
      <tr>
        <td><%= report.machine.client.name %></td>
        <td><%= report.machine.code %></td>
        <td><%= report.observations %></td>
      </tr>
    <% end %>

Now I need to show all the reports of a particular Client. If in the controller I do

@reports = Report.where(observations: "xxx")

works fine, but I need filter by the client name something like

@reports = Report.Machine.Client.where(name: "Joe")

I try similar things, read about scopes and joins but I Can't find something that work.

PS: I have Pundit install maybe there's an approach with policies

CodePudding user response：

ASSUMING (since your question leaves us guessing at table structure) that a Report belongs to a Machine (reports table has machine_id), and a Machine belongs to a Client (machines table has a client_id), and you want to get the client by something like :name you could do something like:

Report.joins(machine: :client).where(clients: {name: 'Joe'})

Will give you SQL like:

SELECT "reports".* 
FROM "reports" 
  INNER JOIN "machines" 
  ON "machines"."id" = "reports"."machine_id" 
    INNER JOIN "clients" 
    ON "clients"."id" = "machines"."client_id" # Note joins to the previous join
WHERE "clients"."name" = 'Joe'

The syntax of this query is different than

Report.joins(:machine, :client).where(clients: {name: 'Joe'})

which will give you:

SELECT "reports".* 
FROM "reports" 
  INNER JOIN "machines" 
  ON "machines"."id" = "reports"."machine_id" 
  INNER JOIN "clients" 
  ON "clients"."id" = "reports"."client_id" # Joins on the original table of reports
WHERE "clients"."name" = 'Joe'

Very similar rails code but the first one follows the query of client -> machines -> reports and the second one is expecting a client_id column on your reports, which you may or may not have. You probably don't. But if you had some OTHER table that had a foreign key in the reports table, you would use the second syntax to do inner joins on the two different tables.

Also note that in your original code you are doing:

<% @reports.each do |report| %>
  <tr>
    <td><%= report.machine.client.name %></td>
    <td><%= report.machine.code %></td>
    <td><%= report.observations %></td>
  </tr>
<% end %>

Every time you call each of the <%= report.machine.client.name %> you will execute three queries. And then the next line report.machine.code will execute two queries. So multiply that times a 1000 reports and you've got thousands of queries. If cache that table info in the original query you can get rid of all those extra queries:

@reports = Report.all.includes(machine: :client)

You will get 3 queries loaded at once and every time that loop processes no new queries will get fired. Will scale much better.

The same is true of your original question. The inner join query is going to return a list of reports and every time you call something like the loop above you'll be executing several queries. So you should switch to includes:

Report.includes(machine: :client).where(clients: {name: 'Joe'})

CodePudding user response：

The correct way to handle this is to setup indirect assocations so that you don't need to violate the Law of Demeter:

class Report < ApplicationRecord
  belongs_to :machine
  has_one :client, though: :machine
end

class Client < ApplicationRecord
  belongs_to :machine
  has_many :reports, through: :machine
end

You can then do a LEFT INNER JOIN to filter it down to reports that have a match in the joined table:

Report.joins(:client)
      .where(clients: { name: 'Joe' })

Now I need to show all the reports of a particular Client.

You're most likely approaching this wrong. Instead of identifying the client by name you should by passing around an id and use the association off the record.

The Rails way to do this unless you want to display the reports on the clients#show page is to create a nested route:

resources :clients do
  resources :reports, 
    module: :clients,
    only: [:index]
end

# app/controllers/clients/reports_controller.rb
module Clients
  # Handles reports for a specific client
  class ReportsController < ApplicationController
    before_action :set_client
    
    # Displays the clients reports
    # GET /clients/1/reports
    def index
      @reports = @client.reports
    end

    private

    def set_client
      @client = Client.eager_load(:reports)
                      .find(params[:client_id])
    end
  end
end

Using a separate controller and not just ::ReportsController is optional but helps separate the concerns if you also have a unnested /reports route which shows all the reports. You can then link to this with:

<%= link_to "Show Reports", [client, :reports] # this assumes that there is a 'client' local %>