26 lines
650 B
MySQL
26 lines
650 B
MySQL
|
-- 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
|
||
|
|
;
|