I'm using PostgreSQL 9.6 with ActiveRecord. I'm trying to get all customers sorted by a specific provider. The relationship between models is: customer has many orders, which belong to a provider.
What I want to achieve is something like:
Example data:
customer1 <- order1 -> provider1
customer2 <- order2 -> provider2
customer3 <- order3 -> provider3
Expecting result:
query_for_provider(2) => customer2, customer1, customer3
query_for_provider(1) => customer1, customer2, customer3
query_for_provider(3) => customer3, customer1, customer2
I wrote the following query:
SELECT "customers".*, MAX(providers.id) AS provider_id FROM "customers"
INNER JOIN "orders" ON "orders"."patient_id" = "customers"."id"
INNER JOIN "providers" ON "providers"."id" = "orders"."provider_id"
GROUP BY customers.id
ORDER BY @(2 - provider_id);
Unfortunately, it returns an error:
column "provider_id" doesn't exist
When I wrap this with SELECT * FROM
(as below) it works, but in this case, can't use this with ActiveRecord, which requires SELECT ... FROM customers
construction.
SELECT * FROM (
SELECT "customers".*, MAX(providers.id) AS provider_id FROM "customers"
INNER JOIN "orders" ON "orders"."patient_id" = "customers"."id"
INNER JOIN "providers" ON "providers"."id" = "orders"."provider_id"
GROUP BY customers.id
) AS customers_with_provider
ORDER BY @(2 - provider_id);
Any thoughts how to achieve this?