CODEWITHSUNDEEP
ruby-on-railspostgresqlsql-order-by
pk-n
pk-n

Reputation: 576

Sort records based on specific value postresql

I have three tables 1)fruits -- id, name 2)countries -- id, name 3)fruit_countries -- id, fruit_id(fk), country_id(fk)

In my fruits table I want to show fruits which are not present in a specific country on top.

I used

select
    distinct(fruits.*),case when fruit_countries.country_id = xx then 1 else 0 end
from
    "fruits"
left join fruit_countries on
    fruit_countries.fruit_id = fruits.id
order by case when fruit_countries.country_id = xx then 1 else 0 end

the problem is since a fruit can be linked to multiple countries I get few fruits records on top of the list when they are present in country xx and some-other countries.

How can I fix this?

Upvotes: 0

Views: 48

Answers (2)

S-Man
S-Man

Reputation: 23676

demo:db<>fiddle

SELECT
    *
FROM fruits f
LEFT JOIN fruit_countries fc ON f.id = fc.fruit_id
ORDER BY bool_or(fc.country_id = 1) OVER (PARTITION BY fc.fruit_id)

You can use the bool_or() window function in the ORDER BY clause. This checks if there is any records per fruit_id group (partition) with the requested country_id. If not, it will be ordered first.

However, the normal ORDER BY orders the NULL records to bottom. But, obviously they are not assigned to the specific country_id as well (they are NULL because there are assigned to no country), you could add NULLS FIRST to the ORDER BY clause, to order these fruits to top everytime.

Upvotes: 2

honey
honey

Reputation: 1077

If you are using rails 6.1 you can use

Fruit.where.missing(:countries) + Fruit.includes(:countries)

Upvotes: 0

Related Questions