“Column does not exist” with INNER JOIN and MAX query in ORDER BY

Question

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?

Answer 1

I'm not sure if this is what are you looking for, but I've generated next example:

create table customers
(
    id int not null primary key, 
    name text not null
);

create table providers
(
    id int not null primary key, 
    name text not null
);

create table orders
(
    id int not null primary key,
    provider_id int not null,
    patient_id int not null,
    constraint fk_customers foreign key (patient_id) references customers (id),
    constraint fk_providers foreign key (provider_id) references providers (id)
);

And I've added some data:

insert into customers values
(1, 'cust 1'),
(2, 'cust 2'),
(3, 'cust 3');

insert into providers values
(1, 'prov 1'),
(2, 'prov 2'),
(3, 'prov 3');

insert into orders values
(1, 1, 1),
(2, 2, 1),
(3, 2, 2),
(4, 3, 2);

Now, simply replace order by @(2 - provider_id) by order by @(2 - max(o.provider_id)) and it works.

select   c.*, max(o.provider_id) as max_prov
from     orders o
join     customers c
on       o.patient_id = c.id
join     providers p
on       o.provider_id = p.id
group by c.id
order by @(2 - max(o.provider_id));

dbfiddle here

BTW: Why are you using this syntax: @(2 - provider_id) You are subtracting from a constant value.

Answer 2

To sort a given provider_id first, this is cheaper:

SELECT c.*, MAX(o.provider_id) AS provider_id
FROM   customers c
JOIN   orders    o ON o.patient_id = c.id
GROUP  BY c.id
ORDER  BY MAX(o.provider_id) <> 2        -- to list provider_id 2 first
        , MAX(o.provider_id);            -- to sort the rest ascending

Works because FALSE sorts before TRUE.
Add provider_id as 2nd ORDER BY item to sort the rest in ascending order.
(NULL sorts last in ascending order in any case.)

Note that your original expression @(2 - provider_id) also affects the sort of the rest since it folds values above and below the given provider_id. Makes it impossible to sort the rest in ascending order like you requested - and is therefore wrong on principal.

Assuming that referential integrity is enforced by a FOREIGN KEY constraint to providers (the typical setup), you don't need to include that table in the query at all. Faster.

Related:

• Select rows for which at least one row per set meets a condition
• Sorting null values after all others, except special

If that involves most or all of table orders it's faster to aggregate first and join later:

SELECT c.*, o.provider_id
FROM   customers c
JOIN  (
   SELECT patient_id, MAX(provider_id) AS provider_id
   FROM   orders
   GROUP  BY 1
   ) o ON o.patient_id = c.id
ORDER  BY o.provider_id <> 2, o.provider_id;

Why the error?

column "provider_id" doesn't exist

You tried to reference the output column name provider_id in the expression @(2 - provider_id) But expressions in the ORDER BY clause can only be formed from input columns. The manual:

Each expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression formed from input-column values.

Bold emphasis mine. Postgres couldn't find provider_id in the list of input column names and responded with the error you quoted.