Ordering rows by joined tables in postgres

Question

I had difficulties to even come up with the correct question title. I'll take any advice to edit it.

My problem: I have an app (job board) with two models: User, Job. User can have many jobs and jobs can be premium or free. Jobs can be expired or active as well. They are active if valid_until parameter IS NOT NULL, otherwise they are expired (I have a background job to set this automatically every day).

I am trying to select Users, who have active jobs but I want users with premium jobs come before users with free jobs assuming users can have active both premium and free job postings at the same time.

SELECT DISTINCT users.id, users.email FROM users
LEFT OUTER JOIN jobs ON jobs.owner_id = users.id
WHERE jobs.valid_until IS NOT NULL;

Above query correctly selects users with at least one active job (either premium or free), I just need to order users according to jobs table. I will appreciate any help/hint. Thanks!

You don't want users that have no jobs at all so you should use (inner) join not left join. Here left join works out because it just so happens that your test for active/valid jobs eliminates those jobless users. But if you asked for jobs.valid_until IS NULL you would also get the jobless users. Left join returns what inner join returns plus unmatched left table rows extended by nulls. — philipxy
– philipxy, Commented Jun 10, 2017 at 6:52

Steve Lovell · Accepted Answer · 2017-06-08 10:06:34Z

6

I believe the following will do what you want:

SELECT users.id, users.email FROM users
JOIN jobs ON jobs.owner_id = users.id
WHERE jobs.valid_until IS NOT NULL
GROUP BY users.id, users.email
ORDER BY min(case when jobs.type = 'premium' then 1 else 2 end)

I couldn't see a name for the field which holds Premium vs Free, so I made one up! Also, I changed your JOIN to an INNER JOIN since the WHERE clause has that effect in any case.

edited Jun 8, 2017 at 10:06

answered Jun 8, 2017 at 10:03

Steve Lovell

2,5742 gold badges15 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Arkhena Over a year ago

I don't understand your use of a GROUP BYclause without any aggregation function...

Steve Lovell Over a year ago

@Arkhena, It's needed for the aggregation in the ORDER BY ... also removes duplicates so no need for DISTINCT.

Collectives™ on Stack Overflow

Ordering rows by joined tables in postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related