Can't filter in a join table

Question

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.

Answer 1

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?

Answer 2

I think the problem is that includes doesn't always generate a join, sometimes it will perform an extra query for the included data. I believe it would normally be able to detect the use of a referenced table in a where, but because of your string query for the ILIKE it isn't picking that up.

I think you can just add a joins(:packages) to your query (with the includes) and that should solve it.

Or you could go for just the explicit joins but add a distinct to that to get rid of the duplicates, that's a quite common pattern.