4

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.

strivedi183
  • 4,749
  • 2
  • 31
  • 38
dimitry_n
  • 2,939
  • 1
  • 30
  • 53
  • You want `@report.includes(clients: [:addresses])` since addresses is an association of clients, not of Policy. That'll solve N+1 issues. If you want sorting, then you'll want to be doing joins. – mroach Feb 17 '17 at 03:32
  • ** `clients: [:address]` (singular) as Clients belong to Addresses. Still getting `missing FROM-clause entry for table "address"` for something like `Policy.where(id: (1..3)).includes(clients: [:address]).order('clients.address.address desc').ids` – dimitry_n Feb 17 '17 at 03:35
  • Try: `@report.joins(clients: [:address])` I recommend doing this in a rails console or tailing the development.log file so you can see the SQL being generated. – mroach Feb 17 '17 at 03:41
  • @mroach same issue, unfortunately. Here is the query: `SELECT "policies"."id" FROM "policies" INNER JOIN "clients" ON "clients"."policy_id" = "policies"."id" INNER JOIN "addresses" ON "addresses"."id" = "clients"."address_id" WHERE ("policies"."id" BETWEEN 1 AND 3) ORDER BY clients.address.state desc ` – dimitry_n Feb 17 '17 at 03:57
  • You need only order by `addresses.state`. In SQL when a table is not aliased, it's referenced by its name. – mroach Feb 17 '17 at 03:59

1 Answers1

9

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.

mroach
  • 2,403
  • 1
  • 22
  • 29