Constructing an ActiveRecord with Nested Joins - Rails 4

Question

I am trying to do something similar to this: Rails order by association field, only with an associated table. I have three models:

class Policy
  has_many :clients
end

class Client
  belongs_to :policy
  belongs_to :address
end

class Address
end

and want to create a report collection, which will contain Policies with associated clients and their addresses. Something similar to this:

@report = Policy.where(...)
@report.includes(:clients, :addresses).order('clients.address.state desc')

but, of course, there is no direct association between Policy and Model and I get missing FROM-clause entry for table "address". I really don't want to add Policy > Address association as there are other models that have addresses and belong to policies.

Answer 1

What you want to do is a SQL JOIN to the table and then order by that table's name and the field in table.field notation.

@reports.joins(clients: [:address]).order('addresses.state')

Here's what's going on:

In the joins method we're saying we want the clients relation and its address relation to be included in a single query.

Next, with order we use addresses since that's the name of the underlying table. If you're ever not sure, tail you log file (e.g. log/development.log) or use the Rails console to see the generated SQL.

This approach has the benefit of reducing N+1 queries as well since you're gulping all your data in one query.