时间戳

首页 > 解决方案 > 使用 where 条件在连接表中选择不匹配的行

ruby-on-rails - 使用 where 条件在连接表中选择不匹配的行

问题描述

在带有 Postgres 的 Rails 应用程序中，我有一个用户、工作和关注者加入表。我想选择特定用户未关注的作业。但也有连接表中没有行的作业。

表：

users: 
  id: bigint (pk)

jobs:
  id: bigint (pk)

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

数据：

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)

预期结果

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

我可以创建一个与此等效的连接查询吗？

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)

哪个可以完成这项工作，但它是使用 ActiveRecord 创建的 PITA。

到目前为止，我有：

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

但由于它是一个内部连接，它不包括没有追随者的工作（工作 id 3）。我还尝试了各种外部连接尝试，要么给出所有行，要么不给出行。

标签： ruby-on-railspostgresqlpostgresql-9.5

解决方案

在 Rails 5 中，您可以使用#left_outer_joins with where not 来实现结果。左连接不返回空行。因此，我们需要添加 nil 条件来获取行。

Rails 5 查询：

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

备用查询：

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

Postgres 查询：

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;

推荐阅读