Can't filter in a join table

I am creating an API in ruby on rails and I have 3 tables Invoice, Package and Additional_charge.

Invoice has many Packages and Packages has many Additional_charges

I have this query:

invoices = Invoice.includes(packages: :additional_charges)
                      .where(user_id: @current_user.id)
                      .order(created_at: :desc)

@invoices = invoices.paginate(page:, per_page:)

And I am using jbuilder to return the data. This is my jbuilder file.

json.invoices @invoices do |invoice|
  json.(invoice, :id, :number, :total, :status, :created_at)
  json.packages invoice.packages do |package|
    json.(package, :tracking, :calculated_weight, :price)
    json.type package.shipping_method
    json.additional_charges package.additional_charges do |additional_charge|
      json.(additional_charge, :name, :amount)
    end
  end
end

But the problems is when I tried to filter for a Packages column named Tracking.

I tried modifying the code like this:

invoices = Invoice.includes(packages: :additional_charges)
                      .where(user_id: @current_user.id)

invoices = invoices.where('packages.tracking ILIKE ?', "%#{tracking}%") if tracking.present?

@invoices = invoices.order(created_at: :desc).paginate(page:, per_page:)

And I got this error:

ActiveRecord::StatementInvalid (PG::UndefinedTable: ERROR: missing FROM-clause entry for table "packages" LINE 1: ...M "invoices" WHERE "invoices"."user_id" = $1 AND (packages.t...

Is there a way so I can filter by trackin column from Packages table?

I tried using joins too but the jbuilder returns me repeated data.

Solution

Your issue is how includes works.

When using includes Rails tries to infer whether or not to use eager_load or preload. This inference is based on the existence of query conditions provided to where, the problem is Rails no longer (hasn't for a very long time) tries to parse string conditions to determine if an associated table is referenced.

For example:

This will use preload - invoices = Invoice.includes(packages: :additional_charges) because neither packages nor additional_charges are referenced
This will also use preload - invoices = Invoice.includes(packages: :additional_charges).where('packages.tracking ILIKE ?', "%#{tracking}%") because Rails cannot determine that packages is referenced in the String condition
- preload will execute 2 queries the first to collect the objects from the primary table (invoices) and another query to collect all of the associated packages e.g. (in simplified form removing the additional_charges portion for demonstration purposes)

SELECT invoices.* FROM invoices 
SELECT packages.* FROM packages WHERE packages.invoice_id IN (1,2,3)

This will use eager_load - invoices = Invoice.includes(packages: :additional_charges).where(packages: {tracking: '123'}) because rails can infer the need to join the packages table based on the condition.
- eager_load will left join the "included" tables and collect all of the data at the same time in 1 query e.g.

SELECT invoices.id AS t0_r0, invoices.number AS t0_r1,...packages.id AS t1_r0, packages.tracking AS t1_r1,... FROM invoices LEFT OUTER JOIN packages ON packages.invoice_id = invoices.id

In your case the issue is that your implementation uses preload and therefor the packages table is not included in the main query; however, you are referencing it in your String condition in where, which causes the error you are seeing.

The simplest resolution would be to use ActiveRecord::QueryMethods#references

references removes the inference by Rails and instead explicitly tells Rails that this table is referenced, so your code would change to

invoices = invoices.references(packages: :additional_charges).where('packages.tracking ILIKE ?', "%#{tracking}%") if tracking.present?