PostgreSQL视图开发：基于当前用户生成reviewed列并列出所有事件

目标 #

• 列出所有唯一事件
• 新增名为reviewed的专属列，取值基于当前用户
• 即使当前用户未被邀请，也需列出所有事件

场景 #

• 用户已被邀请
• 用户未被邀请

核心规则 #

仅**状态为“已确认”**的用户可评审事件（并非所有邀请都已确认），评审完成的判定条件：

• 存在与该用户邀请关联的review记录
• 该review记录至少关联一个attachment

使用环境与表结构 #

使用PostgreSQL 14.7，涉及4张表，结构如下：

CREATE TABLE public.events (
    id bigint NOT NULL,
    name character varying NOT NULL,
    start_at timestamp without time zone NOT NULL,
    end_at timestamp without time zone NOT NULL,
    enable_review boolean NOT NULL -- 补充原视图中用到的字段
);

CREATE TABLE public.invitations (
    id bigint NOT NULL,
    status integer DEFAULT 0 NOT NULL, -- 假设1代表已确认状态
    user_id bigint NOT NULL,
    event_id bigint NOT NULL
);

CREATE TABLE public.reviews (
    id bigint NOT NULL,
    reviewable_id bigint NOT NULL,
    reviewable_type character varying NOT NULL,
    comment text
);

CREATE TABLE public.attachments (
    id bigint NOT NULL,
    attachable_id bigint NOT NULL,
    attachable_type character varying NOT NULL
);

现有问题 #

我编写的视图中reviewed列无法匹配当前用户的邀请，取值随机（依赖邀请创建顺序），现有视图代码：

CREATE OR REPLACE VIEW my_view AS
  SELECT
    ((e.enable_review = true AND r.id IS NOT NULL AND count(a) > 0 ) OR e.enable_review = false) AS reviewed,
    e.*
  FROM events e
  LEFT JOIN invitations i ON i.event_id = e.id
  LEFT JOIN reviews r ON (r.reviewable_type = 'Invitation' AND r.reviewable_id = i.id)
  LEFT JOIN attachments a ON (a.attachable_type = 'Review' AND a.attachable_id = r.id)
  GROUP BY e.id, r.id;

尝试过的方法 #

我试过用row_number()分区，但rn取值不唯一，筛选无效，代码如下：

CREATE OR REPLACE VIEW my_view AS
  SELECT
    ((e.enable_review = true AND r.id IS NOT NULL AND count(a) > 0 ) OR e.enable_review = false) AS reviewed,
    e.*,
    row_number() OVER (PARTITION BY i.event_id ORDER BY i.id DESC) AS rn,
    i.id as manual_invitation_id
  FROM events e
  LEFT JOIN invitations i ON i.event_id = e.id
  LEFT JOIN reviews r ON (r.reviewable_type = 'Invitation' AND r.reviewable_id = i.id)
  LEFT JOIN attachments a ON (a.attachable_type = 'Review' AND a.attachable_id = r.id)
  GROUP BY e.id, i.id, r.id;

希望用单查询实现，尽量避免子查询（因为是高频查询），且应用基于Rails，不确定ActiveRecord能否轻松处理子查询。

解决方案 #

方案1：带参数的函数视图（推荐高频场景） #

创建返回表的函数，接收current_user_id参数，精准关联当前用户的邀请：

CREATE OR REPLACE FUNCTION get_events_with_review_status(current_user_id bigint)
RETURNS TABLE (
    reviewed boolean,
    id bigint,
    name character varying,
    start_at timestamp without time zone,
    end_at timestamp without time zone,
    enable_review boolean
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        -- 判定逻辑：事件无需评审则直接为true；否则检查当前用户的已确认邀请是否有带附件的评审
        CASE
            WHEN e.enable_review = false THEN true
            ELSE EXISTS (
                SELECT 1
                FROM invitations i
                JOIN reviews r ON r.reviewable_type = 'Invitation' AND r.reviewable_id = i.id
                WHERE i.user_id = current_user_id
                  AND i.event_id = e.id
                  AND i.status = 1 -- 替换为实际的已确认状态值
                  AND EXISTS (
                      SELECT 1 FROM attachments a
                      WHERE a.attachable_type = 'Review' AND a.attachable_id = r.id
                  )
            )
        END AS reviewed,
        e.*
    FROM events e;
END;
$$ LANGUAGE plpgsql STABLE;

调用方式：

SELECT * FROM get_events_with_review_status(123); -- 替换为当前用户ID

方案2：直接查询（适配Rails ActiveRecord） #

如果不想用函数，可直接编写查询，在Rails中通过绑定参数传入用户ID：

SELECT
    CASE
        WHEN e.enable_review = false THEN true
        ELSE EXISTS (
            SELECT 1
            FROM invitations i
            JOIN reviews r ON r.reviewable_type = 'Invitation' AND r.reviewable_id = i.id
            WHERE i.user_id = :current_user_id
              AND i.event_id = e.id
              AND i.status = 1 -- 替换为实际的已确认状态值
              AND EXISTS (SELECT 1 FROM attachments a WHERE a.attachable_type = 'Review' AND a.attachable_id = r.id)
        )
    END AS reviewed,
    e.*
FROM events e;

Rails中安全调用方式（避免SQL注入）：

current_user_id = current_user.id
Event.select(
  "CASE WHEN events.enable_review = false THEN true ELSE EXISTS (
      SELECT 1 FROM invitations i
      JOIN reviews r ON r.reviewable_type = 'Invitation' AND r.reviewable_id = i.id
      WHERE i.user_id = ?
        AND i.event_id = events.id
        AND i.status = 1
        AND EXISTS (SELECT 1 FROM attachments a WHERE a.attachable_type = 'Review' AND a.attachable_id = r.id)
    END AS reviewed, events.*",
  current_user_id
)

方案说明 #

1. 核心逻辑通过EXISTS子查询精准匹配当前用户的已确认邀请及关联的带附件评审，解决了原视图关联所有邀请导致的状态混乱问题。
2. 函数方案适合高频查询，PostgreSQL可缓存执行计划；直接查询方案更贴合Rails的ActiveRecord使用习惯。
3. 确保reviewed状态仅对应当前用户，而非随机取其他用户的邀请状态。

内容的提问来源于stack exchange，提问作者brcebn