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

39 lines
818 B
MySQL
Raw Permalink Normal View History

-- https://github.com/dessalines/lemmy/issues/197
DROP VIEW reply_view;
-- Do the reply_view referencing the comment_mview
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_mview cv,
closereply
WHERE
closereply.id = cv.id;