Select rows with no match in join table with where condition

Question

In a Rails app with Postgres I have a users, jobs and followers join table. I want to select jobs that are not followed by a specific user. But also jobs with no rows in the join table.

Tables:

users: 
  id: bigint (pk)

jobs:
  id: bigint (pk)

followings:
  id: bigint (pk)
  job_id: bigint (fk)
  user_id: bigint (fk)

Data:

sandbox_development=# SELECT id FROM jobs;
 id 
----
  1
  2
  3
(3 rows)

sandbox_development=# SELECT id FROM users;
 id 
----
  1
  2

sandbox_development=# 
SELECT id, user_id, job_id FROM followings;
 id | user_id | job_id 
----+---------+--------
  1 |       1 |      1
  2 |       2 |      2
(2 rows)

Expected result

# jobs
 id 
----
  2
  3
(2 rows)

Can I create a join query that is the equivalent of this?

sandbox_development=# 
SELECT j.id FROM jobs j 
WHERE NOT EXISTS(
  SELECT 1 FROM followings f 
  WHERE f.user_id = 1 AND f.job_id = j.id 
);

 id 
----
  2
  3
(2 rows)

Which does the job but is a PITA to create with ActiveRecord.

So far I have:

Job.joins(:followings).where(followings: { user_id: 1 })

SELECT "jobs".* FROM "jobs" 
INNER JOIN "followings" 
ON "followings"."job_id" = "jobs"."id" 
WHERE "followings"."user_id" != 1

But since its an inner join it does not include jobs with no followers (job id 3). I have also tried various attempts at outer joins that either give all the rows or no rows.

Answer 1

In Rails 5, You can use #left_outer_joins with where not to achieve the result. Left joins doesn't return null rows. So, We need to add nil conditions to fetch the rows.

Rails 5 Query:

Job.left_outer_joins(:followings).where.not(followings: {user_id: 1}).or(Job.left_outer_joins(:followings).where(followings: {user_id: nil}))

Alternate Query:

Job.left_outer_joins(:followings).where("followings.user_id != 1 OR followings.user_id is NULL")

Postgres Query:

SELECT "jobs".* FROM "jobs" LEFT OUTER JOIN "followings" ON "followings"."job_id" = "jobs"."id" WHERE "followings"."user_id" != 1 OR followings.user_id is NULL;

Answer 2

I'm not sure I understand, but this has the output you want and use outer join:

SELECT j.* 
FROM jobs j LEFT JOIN followings f ON f.job_id = j.id
LEFT JOIN users u ON u.id = f.user_id AND u.id = 1 
WHERE u.id IS NULL;