1

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?

2 Answers 2

0

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.

1
  • Thank you so much @McNets! That's I was looking for! @(2 - provider_id) - 2 here was a sample provider_id I wanted to order by.
    – konradr
    Apr 9, 2018 at 13:15
1

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:

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.

1
  • Thank you @Erwin, that's very instructive answer! It's awesome to learn something new and know what was the problem. Thanks :)
    – konradr
    Apr 10, 2018 at 11:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.