SQL view with count depending on current user

Question

Aim:

1. List all unique events
2. Have a dedicated column called reviewed based on the current user
3. Events must be listed event if current user is not invited

Possible cases:

1. User is invited
2. User is not invited

An event is reviewed by each "confirmed" user (not all of the invitations are confirmed) Conditions to be "reviewed":

• review exists
• there is at least one attachments linked to this review

I have 4 tables in Postgresql 14.7.

• events
• invitations (1-1/user/event)
• review (0-1/invitation)
• attachments (0-n/review)

Here they are:

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

I got an SQL view for that but it is wrong because reviewed did not match the current user invitation (it is random, based on invitation creation). Here is current code I have

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

I've read Select first row in each GROUP BY group? dozens of times but I'm not sure it fits on my case

In my mind, this view was "temporary" until I scope the results with the current user invitations.

I have part of the solution using 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;)

Then I LEFT OUTER JOIN on manual_invitation_id when I want to scope with the current user, combining it with WHERE rn=1. But rn can be any number, not only one. So scope is not working.

I don't even know if it's possible in a single query. I want to avoid sub queries since it's one of the most used in the app. Additionally, it's inside a Rails app so I'm not sure subqueries can be handled that easily with ActiveRecord.

Answer 1

List all events, with a flag reviewed for the current user:

SELECT EXISTS (
         SELECT
         FROM   invitations i
         JOIN   reviews     r ON r.reviewable_id = i.id
         JOIN   attachments a ON a.attachable_id = r.id
         WHERE  i.user_id = $current_user_id             -- ???
         AND    i.event_id = e.id
         AND    r.reviewable_type = 'Invitation'
         AND    a.attachable_type = 'Review'
         ) AS reviewed
     , e.*
FROM   events e;

An EXISTS subquery expression is probably fastest.

Don't use a generic VIEW for this if you want the result for the current user. Use the given query, or wrap it into a set-returning FUNCTION. Example:

• PostgreSQL Function Returning Table or SETOF