lemmy/migrations/2020-01-29-011901_create_reply_materialized_view/down.sql

38 lines
755 B
MySQL
Raw Permalink Normal View History

-- Drop the materialized / built views
DROP VIEW reply_view;
CREATE VIEW reply_view AS
with closereply AS (
SELECT
c2.id,
c2.creator_id AS sender_id,
c.creator_id AS recipient_id
FROM
comment c
INNER JOIN comment c2 ON c.id = c2.parent_id
WHERE
c2.creator_id != c.creator_id
-- Do union where post is null
UNION
SELECT
c.id,
c.creator_id AS sender_id,
p.creator_id AS recipient_id
FROM
comment c,
post p
WHERE
c.post_id = p.id
AND c.parent_id IS NULL
AND c.creator_id != p.creator_id
)
SELECT
cv.*,
closereply.recipient_id
FROM
comment_view cv,
closereply
WHERE
closereply.id = cv.id;