4

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!

Lubomir Herko
  • 354
  • 3
  • 11
  • 1
    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 Jun 10 '17 at 06:52

1 Answers1

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.

Steve Lovell
  • 2,564
  • 2
  • 13
  • 16