SQL 视图，其计数取决于当前用户

问：

目的：

1. 列出所有唯一身份事件
2. 有一个基于当前用户调用的专用列reviewed
3. 如果未邀请当前用户，则必须列出事件

可能的情况：

1. 用户被邀请
2. 未邀请用户

每个“已确认”用户都会审核活动（并非所有邀请都已确认） “审查”条件：

• 存在审查
• 至少有一个附件链接到本综述

我在 Postgresql 14.7 中有 4 个表。

• 事件
• 邀请（1-1/用户/事件）
• 评论 （0-1/邀请）
• 附件 （0-N/review）

他们来了：

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
);

CREATE TABLE public.invitations (
    id bigint NOT NULL,
    status integer DEFAULT 0 NOT NULL,
    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
);

我得到了一个SQL视图，但它是错误的，因为它与当前用户邀请不匹配（它是随机的，基于邀请创建）。 这是我拥有的当前代码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;)

我已经阅读了 Select first row in each GROUP BY group？ 几十遍了，但我不确定它是否适合我的情况

在我看来，这种观点是“暂时的”，直到我使用当前的用户邀请来限定结果的范围。

我有一部分解决方案使用row_number

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;)

然后，当我想确定当前用户的范围时，我会将其与 . 但可以是任何数字，而不仅仅是一个。所以范围不起作用。LEFT OUTER JOINmanual_invitation_idWHERE rn=1rn

我什至不知道在单个查询中是否可能。我想避免子查询，因为它是应用程序中最常用的查询之一。此外，它位于 Rails 应用程序中，因此我不确定使用 ActiveRecord 可以轻松处理子查询。