diff options
Diffstat (limited to 'server/migrations')
-rw-r--r-- | server/migrations/2019-05-02-051656_community_view_hot_rank/down.sql | 28 | ||||
-rw-r--r-- | server/migrations/2019-05-02-051656_community_view_hot_rank/up.sql | 29 |
2 files changed, 57 insertions, 0 deletions
diff --git a/server/migrations/2019-05-02-051656_community_view_hot_rank/down.sql b/server/migrations/2019-05-02-051656_community_view_hot_rank/down.sql new file mode 100644 index 00000000..0f3a58a8 --- /dev/null +++ b/server/migrations/2019-05-02-051656_community_view_hot_rank/down.sql @@ -0,0 +1,28 @@ +drop view community_view; +create view community_view as +with all_community as +( + select *, + (select name from user_ u where c.creator_id = u.id) as creator_name, + (select name from category ct where c.category_id = ct.id) as category_name, + (select count(*) from community_follower cf where cf.community_id = c.id) as number_of_subscribers, + (select count(*) from post p where p.community_id = c.id) as number_of_posts, + (select count(*) from comment co, post p where c.id = p.community_id and p.id = co.post_id) as number_of_comments + from community c +) + +select +ac.*, +u.id as user_id, +(select cf.id::boolean from community_follower cf where u.id = cf.user_id and ac.id = cf.community_id) as subscribed +from user_ u +cross join all_community ac + +union all + +select +ac.*, +null as user_id, +null as subscribed +from all_community ac +; diff --git a/server/migrations/2019-05-02-051656_community_view_hot_rank/up.sql b/server/migrations/2019-05-02-051656_community_view_hot_rank/up.sql new file mode 100644 index 00000000..e7e75366 --- /dev/null +++ b/server/migrations/2019-05-02-051656_community_view_hot_rank/up.sql @@ -0,0 +1,29 @@ +drop view community_view; +create view community_view as +with all_community as +( + select *, + (select name from user_ u where c.creator_id = u.id) as creator_name, + (select name from category ct where c.category_id = ct.id) as category_name, + (select count(*) from community_follower cf where cf.community_id = c.id) as number_of_subscribers, + (select count(*) from post p where p.community_id = c.id) as number_of_posts, + (select count(*) from comment co, post p where c.id = p.community_id and p.id = co.post_id) as number_of_comments, + hot_rank((select count(*) from community_follower cf where cf.community_id = c.id), c.published) as hot_rank + from community c +) + +select +ac.*, +u.id as user_id, +(select cf.id::boolean from community_follower cf where u.id = cf.user_id and ac.id = cf.community_id) as subscribed +from user_ u +cross join all_community ac + +union all + +select +ac.*, +null as user_id, +null as subscribed +from all_community ac +; |