21 files changed, 1048 insertions, 154 deletions

diff --git a/README.md b/README.md
index fcb07e72..47290953 100644
--- a/README.md
+++ b/README.md
@@ -130,19 +130,19 @@ If you'd like to add translations, take a look at the [English translation file]
 
 lang | done | missing
 ---- | ---- | -------
-ca | 97% | cross_posted_to,old,support_on_liberapay,post_title_too_long,time,action
-de | 86% | cross_posted_to,create_private_message,send_secure_message,send_message,message,avatar,upload_avatar,show_avatars,old,docs,message_sent,messages,old_password,matrix_user_id,private_message_disclaimer,send_notifications_to_email,downvotes_disabled,enable_downvotes,open_registration,registration_closed,enable_nsfw,donate_to_lemmy,donate,from,logged_in,post_title_too_long,email_already_exists,couldnt_create_private_message,no_private_message_edit_allowed,couldnt_update_private_message,time,action
-fa | 71% | cross_post,cross_posted_to,subscribed_to_communities,trending_communities,create_private_message,send_secure_message,send_message,message,mod,mods,moderates,remove_as_mod,appoint_as_mod,modlog,stickied,ban,ban_from_site,unban,unban_from_site,banned,number_of_subscribers,subscribers,both,saved,unsubscribe,subscribe,subscribed,old,api,docs,inbox,inbox_for,message_sent,notifications_error,messages,no_email_setup,matrix_user_id,private_message_disclaimer,url,body,copy_suggested_title,community,expand_here,subscribe_to_communities,theme,sponsor_message,support_on_liberapay,general_sponsors,joined,by,to,from,landing_0,logged_in,community_moderator_already_exists,community_follower_already_exists,community_user_already_banned,post_title_too_long,no_slurs,admin_already_created,couldnt_create_private_message,no_private_message_edit_allowed,couldnt_update_private_message,time,action
-eo | 74% | cross_posted_to,number_of_communities,create_private_message,send_secure_message,send_message,message,preview,upload_image,avatar,upload_avatar,show_avatars,formatting_help,view_source,sticky,unsticky,archive_link,stickied,delete_account,delete_account_confirm,banned,creator,number_online,old,docs,replies,mentions,message_sent,messages,old_password,forgot_password,reset_password_mail_sent,password_change,new_password,no_email_setup,matrix_user_id,private_message_disclaimer,send_notifications_to_email,language,browser_default,downvotes_disabled,enable_downvotes,open_registration,registration_closed,enable_nsfw,theme,support_on_liberapay,donate_to_lemmy,donate,from,are_you_sure,yes,no,logged_in,post_title_too_long,email_already_exists,couldnt_create_private_message,no_private_message_edit_allowed,couldnt_update_private_message,time,action
-es | 99% | cross_posted_to,post_title_too_long
-fi | 97% | cross_posted_to,old,support_on_liberapay,post_title_too_long,time,action
-fr | 82% | cross_posted_to,create_private_message,send_secure_message,send_message,message,avatar,upload_avatar,show_avatars,archive_link,old,docs,replies,mentions,message_sent,messages,old_password,forgot_password,reset_password_mail_sent,password_change,new_password,no_email_setup,matrix_user_id,private_message_disclaimer,send_notifications_to_email,language,browser_default,downvotes_disabled,enable_downvotes,open_registration,registration_closed,enable_nsfw,donate_to_lemmy,donate,from,logged_in,post_title_too_long,email_already_exists,couldnt_create_private_message,no_private_message_edit_allowed,couldnt_update_private_message,time,action
-it | 82% | cross_posted_to,create_private_message,send_secure_message,send_message,message,avatar,upload_avatar,show_avatars,archive_link,old,docs,message_sent,messages,old_password,forgot_password,reset_password_mail_sent,password_change,new_password,no_email_setup,matrix_user_id,private_message_disclaimer,send_notifications_to_email,language,browser_default,downvotes_disabled,enable_downvotes,open_registration,registration_closed,enable_nsfw,donate_to_lemmy,donate,from,logged_in,post_title_too_long,email_already_exists,couldnt_create_private_message,no_private_message_edit_allowed,couldnt_update_private_message,time,action
-nl | 98% | cross_posted_to,post_title_too_long,time,action
-pt-br | 100% | post_title_too_long
-ru | 70% | cross_posts,cross_post,cross_posted_to,number_of_communities,create_private_message,send_secure_message,send_message,message,preview,upload_image,avatar,upload_avatar,show_avatars,formatting_help,view_source,sticky,unsticky,archive_link,stickied,delete_account,delete_account_confirm,banned,creator,number_online,old,docs,replies,mentions,message_sent,messages,old_password,forgot_password,reset_password_mail_sent,password_change,new_password,no_email_setup,matrix_user_id,private_message_disclaimer,send_notifications_to_email,language,browser_default,downvotes_disabled,enable_downvotes,open_registration,registration_closed,enable_nsfw,recent_comments,theme,support_on_liberapay,donate_to_lemmy,donate,monero,by,to,from,transfer_community,transfer_site,are_you_sure,yes,no,logged_in,post_title_too_long,email_already_exists,couldnt_create_private_message,no_private_message_edit_allowed,couldnt_update_private_message,time,action
-sv | 81% | cross_posted_to,create_private_message,send_secure_message,send_message,message,avatar,upload_avatar,show_avatars,archive_link,old,docs,replies,mentions,message_sent,messages,old_password,forgot_password,reset_password_mail_sent,password_change,new_password,no_email_setup,matrix_user_id,private_message_disclaimer,send_notifications_to_email,language,browser_default,downvotes_disabled,enable_downvotes,open_registration,registration_closed,enable_nsfw,support_on_liberapay,donate_to_lemmy,donate,from,logged_in,post_title_too_long,email_already_exists,couldnt_create_private_message,no_private_message_edit_allowed,couldnt_update_private_message,time,action
-zh | 69% | cross_posts,cross_post,cross_posted_to,users,number_of_communities,create_private_message,send_secure_message,send_message,message,preview,upload_image,avatar,upload_avatar,show_avatars,formatting_help,view_source,sticky,unsticky,archive_link,settings,stickied,delete_account,delete_account_confirm,banned,creator,number_online,old,docs,replies,mentions,message_sent,messages,old_password,forgot_password,reset_password_mail_sent,password_change,new_password,no_email_setup,matrix_user_id,private_message_disclaimer,send_notifications_to_email,language,browser_default,downvotes_disabled,enable_downvotes,open_registration,registration_closed,enable_nsfw,recent_comments,nsfw,show_nsfw,theme,donate_to_lemmy,donate,monero,by,to,from,transfer_community,transfer_site,are_you_sure,yes,no,logged_in,post_title_too_long,email_already_exists,couldnt_create_private_message,no_private_message_edit_allowed,couldnt_update_private_message,time,action
+ca | 97% | cross_posted_to,old,support_on_liberapay,couldnt_get_comments,post_title_too_long,time,action
+de | 86% | cross_posted_to,create_private_message,send_secure_message,send_message,message,avatar,upload_avatar,show_avatars,old,docs,message_sent,messages,old_password,matrix_user_id,private_message_disclaimer,send_notifications_to_email,downvotes_disabled,enable_downvotes,open_registration,registration_closed,enable_nsfw,donate_to_lemmy,donate,from,logged_in,couldnt_get_comments,post_title_too_long,email_already_exists,couldnt_create_private_message,no_private_message_edit_allowed,couldnt_update_private_message,time,action
+fa | 71% | cross_post,cross_posted_to,subscribed_to_communities,trending_communities,create_private_message,send_secure_message,send_message,message,mod,mods,moderates,remove_as_mod,appoint_as_mod,modlog,stickied,ban,ban_from_site,unban,unban_from_site,banned,number_of_subscribers,subscribers,both,saved,unsubscribe,subscribe,subscribed,old,api,docs,inbox,inbox_for,message_sent,notifications_error,messages,no_email_setup,matrix_user_id,private_message_disclaimer,url,body,copy_suggested_title,community,expand_here,subscribe_to_communities,theme,sponsor_message,support_on_liberapay,general_sponsors,joined,by,to,from,landing_0,logged_in,couldnt_get_comments,community_moderator_already_exists,community_follower_already_exists,community_user_already_banned,post_title_too_long,no_slurs,admin_already_created,couldnt_create_private_message,no_private_message_edit_allowed,couldnt_update_private_message,time,action
+eo | 73% | cross_posted_to,number_of_communities,create_private_message,send_secure_message,send_message,message,preview,upload_image,avatar,upload_avatar,show_avatars,formatting_help,view_source,sticky,unsticky,archive_link,stickied,delete_account,delete_account_confirm,banned,creator,number_online,old,docs,replies,mentions,message_sent,messages,old_password,forgot_password,reset_password_mail_sent,password_change,new_password,no_email_setup,matrix_user_id,private_message_disclaimer,send_notifications_to_email,language,browser_default,downvotes_disabled,enable_downvotes,open_registration,registration_closed,enable_nsfw,theme,support_on_liberapay,donate_to_lemmy,donate,from,are_you_sure,yes,no,logged_in,couldnt_get_comments,post_title_too_long,email_already_exists,couldnt_create_private_message,no_private_message_edit_allowed,couldnt_update_private_message,time,action
+es | 99% | cross_posted_to,couldnt_get_comments,post_title_too_long
+fi | 97% | cross_posted_to,old,support_on_liberapay,couldnt_get_comments,post_title_too_long,time,action
+fr | 81% | cross_posted_to,create_private_message,send_secure_message,send_message,message,avatar,upload_avatar,show_avatars,archive_link,old,docs,replies,mentions,message_sent,messages,old_password,forgot_password,reset_password_mail_sent,password_change,new_password,no_email_setup,matrix_user_id,private_message_disclaimer,send_notifications_to_email,language,browser_default,downvotes_disabled,enable_downvotes,open_registration,registration_closed,enable_nsfw,donate_to_lemmy,donate,from,logged_in,couldnt_get_comments,post_title_too_long,email_already_exists,couldnt_create_private_message,no_private_message_edit_allowed,couldnt_update_private_message,time,action
+it | 82% | cross_posted_to,create_private_message,send_secure_message,send_message,message,avatar,upload_avatar,show_avatars,archive_link,old,docs,message_sent,messages,old_password,forgot_password,reset_password_mail_sent,password_change,new_password,no_email_setup,matrix_user_id,private_message_disclaimer,send_notifications_to_email,language,browser_default,downvotes_disabled,enable_downvotes,open_registration,registration_closed,enable_nsfw,donate_to_lemmy,donate,from,logged_in,couldnt_get_comments,post_title_too_long,email_already_exists,couldnt_create_private_message,no_private_message_edit_allowed,couldnt_update_private_message,time,action
+nl | 98% | cross_posted_to,couldnt_get_comments,post_title_too_long,time,action
+pt-br | 99% | couldnt_get_comments,post_title_too_long
+ru | 70% | cross_posts,cross_post,cross_posted_to,number_of_communities,create_private_message,send_secure_message,send_message,message,preview,upload_image,avatar,upload_avatar,show_avatars,formatting_help,view_source,sticky,unsticky,archive_link,stickied,delete_account,delete_account_confirm,banned,creator,number_online,old,docs,replies,mentions,message_sent,messages,old_password,forgot_password,reset_password_mail_sent,password_change,new_password,no_email_setup,matrix_user_id,private_message_disclaimer,send_notifications_to_email,language,browser_default,downvotes_disabled,enable_downvotes,open_registration,registration_closed,enable_nsfw,recent_comments,theme,support_on_liberapay,donate_to_lemmy,donate,monero,by,to,from,transfer_community,transfer_site,are_you_sure,yes,no,logged_in,couldnt_get_comments,post_title_too_long,email_already_exists,couldnt_create_private_message,no_private_message_edit_allowed,couldnt_update_private_message,time,action
+sv | 81% | cross_posted_to,create_private_message,send_secure_message,send_message,message,avatar,upload_avatar,show_avatars,archive_link,old,docs,replies,mentions,message_sent,messages,old_password,forgot_password,reset_password_mail_sent,password_change,new_password,no_email_setup,matrix_user_id,private_message_disclaimer,send_notifications_to_email,language,browser_default,downvotes_disabled,enable_downvotes,open_registration,registration_closed,enable_nsfw,support_on_liberapay,donate_to_lemmy,donate,from,logged_in,couldnt_get_comments,post_title_too_long,email_already_exists,couldnt_create_private_message,no_private_message_edit_allowed,couldnt_update_private_message,time,action
+zh | 69% | cross_posts,cross_post,cross_posted_to,users,number_of_communities,create_private_message,send_secure_message,send_message,message,preview,upload_image,avatar,upload_avatar,show_avatars,formatting_help,view_source,sticky,unsticky,archive_link,settings,stickied,delete_account,delete_account_confirm,banned,creator,number_online,old,docs,replies,mentions,message_sent,messages,old_password,forgot_password,reset_password_mail_sent,password_change,new_password,no_email_setup,matrix_user_id,private_message_disclaimer,send_notifications_to_email,language,browser_default,downvotes_disabled,enable_downvotes,open_registration,registration_closed,enable_nsfw,recent_comments,nsfw,show_nsfw,theme,donate_to_lemmy,donate,monero,by,to,from,transfer_community,transfer_site,are_you_sure,yes,no,logged_in,couldnt_get_comments,post_title_too_long,email_already_exists,couldnt_create_private_message,no_private_message_edit_allowed,couldnt_update_private_message,time,action
 <!-- translationsstop -->
 
 If you'd like to update this report, run:
diff --git a/server/migrations/2020-02-07-210055_add_comment_subscribed/down.sql b/server/migrations/2020-02-07-210055_add_comment_subscribed/down.sql
new file mode 100644
index 00000000..b6120d15
--- /dev/null
+++ b/server/migrations/2020-02-07-210055_add_comment_subscribed/down.sql
@@ -0,0 +1,206 @@
+
+drop view reply_view;
+drop view user_mention_view;
+drop view user_mention_mview;
+drop view comment_view;
+drop view comment_mview;
+drop materialized view comment_aggregates_mview;
+drop view comment_aggregates_view;
+
+-- reply and comment view
+create view comment_aggregates_view as
+select        
+c.*,
+(select community_id from post p where p.id = c.post_id),
+(select u.banned from user_ u where c.creator_id = u.id) as banned,
+(select cb.id::bool from community_user_ban cb, post p where c.creator_id = cb.user_id and p.id = c.post_id and p.community_id = cb.community_id) as banned_from_community,
+(select name from user_ where c.creator_id = user_.id) as creator_name,
+(select avatar from user_ where c.creator_id = user_.id) as creator_avatar,
+coalesce(sum(cl.score), 0) as score,
+count (case when cl.score = 1 then 1 else null end) as upvotes,
+count (case when cl.score = -1 then 1 else null end) as downvotes
+from comment c
+left join comment_like cl on c.id = cl.comment_id
+group by c.id;
+
+create materialized view comment_aggregates_mview as select * from comment_aggregates_view;
+
+create unique index idx_comment_aggregates_mview_id on comment_aggregates_mview (id);
+
+create view comment_view as
+with all_comment as
+(
+  select
+  ca.*
+  from comment_aggregates_view ca
+)
+
+select
+ac.*,
+u.id as user_id,
+coalesce(cl.score, 0) as my_vote,
+(select cs.id::bool from comment_saved cs where u.id = cs.user_id and cs.comment_id = ac.id) as saved
+from user_ u
+cross join all_comment ac
+left join comment_like cl on u.id = cl.user_id and ac.id = cl.comment_id
+
+union all
+
+select 
+    ac.*,
+    null as user_id, 
+    null as my_vote,
+    null as saved
+from all_comment ac
+;
+
+create view comment_mview as
+with all_comment as
+(
+  select
+  ca.*
+  from comment_aggregates_mview ca
+)
+
+select
+ac.*,
+u.id as user_id,
+coalesce(cl.score, 0) as my_vote,
+(select cs.id::bool from comment_saved cs where u.id = cs.user_id and cs.comment_id = ac.id) as saved
+from user_ u
+cross join all_comment ac
+left join comment_like cl on u.id = cl.user_id and ac.id = cl.comment_id
+
+union all
+
+select 
+    ac.*,
+    null as user_id, 
+    null as my_vote,
+    null as saved
+from all_comment ac
+;
+
+
+-- 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
+;
+
+-- user mention
+create view user_mention_view as
+select 
+    c.id,
+    um.id as user_mention_id,
+    c.creator_id,
+    c.post_id,
+    c.parent_id,
+    c.content,
+    c.removed,
+    um.read,
+    c.published,
+    c.updated,
+    c.deleted,
+    c.community_id,
+    c.banned,
+    c.banned_from_community,
+    c.creator_name,
+    c.creator_avatar,
+    c.score,
+    c.upvotes,
+    c.downvotes,
+    c.user_id,
+    c.my_vote,
+    c.saved,
+    um.recipient_id
+from user_mention um, comment_view c
+where um.comment_id = c.id;
+
+
+create view user_mention_mview as 
+with all_comment as
+(
+  select
+  ca.*
+  from comment_aggregates_mview ca
+)
+
+select
+    ac.id,
+    um.id as user_mention_id,
+    ac.creator_id,
+    ac.post_id,
+    ac.parent_id,
+    ac.content,
+    ac.removed,
+    um.read,
+    ac.published,
+    ac.updated,
+    ac.deleted,
+    ac.community_id,
+    ac.banned,
+    ac.banned_from_community,
+    ac.creator_name,
+    ac.creator_avatar,
+    ac.score,
+    ac.upvotes,
+    ac.downvotes,
+    u.id as user_id,
+    coalesce(cl.score, 0) as my_vote,
+    (select cs.id::bool from comment_saved cs where u.id = cs.user_id and cs.comment_id = ac.id) as saved,
+    um.recipient_id
+from user_ u
+cross join all_comment ac
+left join comment_like cl on u.id = cl.user_id and ac.id = cl.comment_id
+left join user_mention um on um.comment_id = ac.id
+
+union all
+
+select 
+    ac.id,
+    um.id as user_mention_id,
+    ac.creator_id,
+    ac.post_id,
+    ac.parent_id,
+    ac.content,
+    ac.removed,
+    um.read,
+    ac.published,
+    ac.updated,
+    ac.deleted,
+    ac.community_id,
+    ac.banned,
+    ac.banned_from_community,
+    ac.creator_name,
+    ac.creator_avatar,
+    ac.score,
+    ac.upvotes,
+    ac.downvotes,
+    null as user_id, 
+    null as my_vote,
+    null as saved,
+    um.recipient_id
+from all_comment ac
+left join user_mention um on um.comment_id = ac.id
+;
+
diff --git a/server/migrations/2020-02-07-210055_add_comment_subscribed/up.sql b/server/migrations/2020-02-07-210055_add_comment_subscribed/up.sql
new file mode 100644
index 00000000..8836a571
--- /dev/null
+++ b/server/migrations/2020-02-07-210055_add_comment_subscribed/up.sql
@@ -0,0 +1,220 @@
+
+-- Adding community name, hot_rank, to comment_view, user_mention_view, and subscribed to comment_view
+
+-- Rebuild the comment view
+drop view reply_view;
+drop view user_mention_view;
+drop view user_mention_mview;
+drop view comment_view;
+drop view comment_mview;
+drop materialized view comment_aggregates_mview;
+drop view comment_aggregates_view;
+
+-- reply and comment view
+create view comment_aggregates_view as
+select        
+c.*,
+(select community_id from post p where p.id = c.post_id),
+(select co.name from post p, community co where p.id = c.post_id and p.community_id = co.id) as community_name,
+(select u.banned from user_ u where c.creator_id = u.id) as banned,
+(select cb.id::bool from community_user_ban cb, post p where c.creator_id = cb.user_id and p.id = c.post_id and p.community_id = cb.community_id) as banned_from_community,
+(select name from user_ where c.creator_id = user_.id) as creator_name,
+(select avatar from user_ where c.creator_id = user_.id) as creator_avatar,
+coalesce(sum(cl.score), 0) as score,
+count (case when cl.score = 1 then 1 else null end) as upvotes,
+count (case when cl.score = -1 then 1 else null end) as downvotes,
+hot_rank(coalesce(sum(cl.score) , 0), c.published) as hot_rank
+from comment c
+left join comment_like cl on c.id = cl.comment_id
+group by c.id;
+
+create materialized view comment_aggregates_mview as select * from comment_aggregates_view;
+
+create unique index idx_comment_aggregates_mview_id on comment_aggregates_mview (id);
+
+create view comment_view as
+with all_comment as
+(
+  select
+  ca.*
+  from comment_aggregates_view ca
+)
+
+select
+ac.*,
+u.id as user_id,
+coalesce(cl.score, 0) as my_vote,
+(select cf.id::boolean from community_follower cf where u.id = cf.user_id and ac.community_id = cf.community_id) as subscribed,
+(select cs.id::bool from comment_saved cs where u.id = cs.user_id and cs.comment_id = ac.id) as saved
+from user_ u
+cross join all_comment ac
+left join comment_like cl on u.id = cl.user_id and ac.id = cl.comment_id
+
+union all
+
+select 
+    ac.*,
+    null as user_id, 
+    null as my_vote,
+    null as subscribed,
+    null as saved
+from all_comment ac
+;
+
+create view comment_mview as
+with all_comment as
+(
+  select
+  ca.*
+  from comment_aggregates_mview ca
+)
+
+select
+ac.*,
+u.id as user_id,
+coalesce(cl.score, 0) as my_vote,
+(select cf.id::boolean from community_follower cf where u.id = cf.user_id and ac.community_id = cf.community_id) as subscribed,
+(select cs.id::bool from comment_saved cs where u.id = cs.user_id and cs.comment_id = ac.id) as saved
+from user_ u
+cross join all_comment ac
+left join comment_like cl on u.id = cl.user_id and ac.id = cl.comment_id
+
+union all
+
+select 
+    ac.*,
+    null as user_id, 
+    null as my_vote,
+    null as subscribed,
+    null as saved
+from all_comment ac
+;
+
+-- 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
+;
+
+-- user mention
+create view user_mention_view as
+select 
+    c.id,
+    um.id as user_mention_id,
+    c.creator_id,
+    c.post_id,
+    c.parent_id,
+    c.content,
+    c.removed,
+    um.read,
+    c.published,
+    c.updated,
+    c.deleted,
+    c.community_id,
+    c.community_name,
+    c.banned,
+    c.banned_from_community,
+    c.creator_name,
+    c.creator_avatar,
+    c.score,
+    c.upvotes,
+    c.downvotes,
+    c.hot_rank,
+    c.user_id,
+    c.my_vote,
+    c.saved,
+    um.recipient_id
+from user_mention um, comment_view c
+where um.comment_id = c.id;
+
+
+create view user_mention_mview as 
+with all_comment as
+(
+  select
+  ca.*
+  from comment_aggregates_mview ca
+)
+
+select
+    ac.id,
+    um.id as user_mention_id,
+    ac.creator_id,
+    ac.post_id,
+    ac.parent_id,
+    ac.content,
+    ac.removed,
+    um.read,
+    ac.published,
+    ac.updated,
+    ac.deleted,
+    ac.community_id,
+    ac.community_name,
+    ac.banned,
+    ac.banned_from_community,
+    ac.creator_name,
+    ac.creator_avatar,
+    ac.score,
+    ac.upvotes,
+    ac.downvotes,
+    ac.hot_rank,
+    u.id as user_id,
+    coalesce(cl.score, 0) as my_vote,
+    (select cs.id::bool from comment_saved cs where u.id = cs.user_id and cs.comment_id = ac.id) as saved,
+    um.recipient_id
+from user_ u
+cross join all_comment ac
+left join comment_like cl on u.id = cl.user_id and ac.id = cl.comment_id
+left join user_mention um on um.comment_id = ac.id
+
+union all
+
+select 
+    ac.id,
+    um.id as user_mention_id,
+    ac.creator_id,
+    ac.post_id,
+    ac.parent_id,
+    ac.content,
+    ac.removed,
+    um.read,
+    ac.published,
+    ac.updated,
+    ac.deleted,
+    ac.community_id,
+    ac.community_name,
+    ac.banned,
+    ac.banned_from_community,
+    ac.creator_name,
+    ac.creator_avatar,
+    ac.score,
+    ac.upvotes,
+    ac.downvotes,
+    ac.hot_rank,
+    null as user_id, 
+    null as my_vote,
+    null as saved,
+    um.recipient_id
+from all_comment ac
+left join user_mention um on um.comment_id = ac.id
+;
+
diff --git a/server/src/api/comment.rs b/server/src/api/comment.rs
index 775085e9..5c614966 100644
--- a/server/src/api/comment.rs
+++ b/server/src/api/comment.rs
@@ -2,6 +2,7 @@ use super::*;
 use crate::send_email;
 use crate::settings::Settings;
 use diesel::PgConnection;
+use std::str::FromStr;
 
 #[derive(Serialize, Deserialize)]
 pub struct CreateComment {
@@ -47,6 +48,21 @@ pub struct CreateCommentLike {
   auth: String,
 }
 
+#[derive(Serialize, Deserialize)]
+pub struct GetComments {
+  type_: String,
+  sort: String,
+  page: Option<i64>,
+  limit: Option<i64>,
+  pub community_id: Option<i32>,
+  auth: Option<String>,
+}
+
+#[derive(Serialize, Deserialize)]
+pub struct GetCommentsResponse {
+  comments: Vec<CommentView>,
+}
+
 impl Perform<CommentResponse> for Oper<CreateComment> {
   fn perform(&self, conn: &PgConnection) -> Result<CommentResponse, Error> {
     let data: &CreateComment = &self.data;
@@ -456,3 +472,40 @@ impl Perform<CommentResponse> for Oper<CreateCommentLike> {
     })
   }
 }
+
+impl Perform<GetCommentsResponse> for Oper<GetComments> {
+  fn perform(&self, conn: &PgConnection) -> Result<GetCommentsResponse, Error> {
+    let data: &GetComments = &self.data;
+
+    let user_claims: Option<Claims> = match &data.auth {
+      Some(auth) => match Claims::decode(&auth) {
+        Ok(claims) => Some(claims.claims),
+        Err(_e) => None,
+      },
+      None => None,
+    };
+
+    let user_id = match &user_claims {
+      Some(claims) => Some(claims.id),
+      None => None,
+    };
+
+    let type_ = ListingType::from_str(&data.type_)?;
+    let sort = SortType::from_str(&data.sort)?;
+
+    let comments = match CommentQueryBuilder::create(&conn)
+      .listing_type(type_)
+      .sort(&sort)
+      .for_community_id(data.community_id)
+      .my_user_id(user_id)
+      .page(data.page)
+      .limit(data.limit)
+      .list()
+    {
+      Ok(comments) => comments,
+      Err(_e) => return Err(APIError::err("couldnt_get_comments").into()),
+    };
+
+    Ok(GetCommentsResponse { comments })
+  }
+}
diff --git a/server/src/db/comment_view.rs b/server/src/db/comment_view.rs
index febf18b7..01e1bbaf 100644
--- a/server/src/db/comment_view.rs
+++ b/server/src/db/comment_view.rs
@@ -15,6 +15,7 @@ table! {
     updated -> Nullable<Timestamp>,
     deleted -> Bool,
     community_id -> Int4,
+    community_name -> Varchar,
     banned -> Bool,
     banned_from_community -> Bool,
     creator_name -> Varchar,
@@ -22,8 +23,10 @@ table! {
     score -> BigInt,
     upvotes -> BigInt,
     downvotes -> BigInt,
+    hot_rank -> Int4,
     user_id -> Nullable<Int4>,
     my_vote -> Nullable<Int4>,
+    subscribed -> Nullable<Bool>,
     saved -> Nullable<Bool>,
   }
 }
@@ -41,6 +44,7 @@ table! {
     updated -> Nullable<Timestamp>,
     deleted -> Bool,
     community_id -> Int4,
+    community_name -> Varchar,
     banned -> Bool,
     banned_from_community -> Bool,
     creator_name -> Varchar,
@@ -48,8 +52,10 @@ table! {
     score -> BigInt,
     upvotes -> BigInt,
     downvotes -> BigInt,
+    hot_rank -> Int4,
     user_id -> Nullable<Int4>,
     my_vote -> Nullable<Int4>,
+    subscribed -> Nullable<Bool>,
     saved -> Nullable<Bool>,
   }
 }
@@ -70,6 +76,7 @@ pub struct CommentView {
   pub updated: Option<chrono::NaiveDateTime>,
   pub deleted: bool,
   pub community_id: i32,
+  pub community_name: String,
   pub banned: bool,
   pub banned_from_community: bool,
   pub creator_name: String,
@@ -77,15 +84,19 @@ pub struct CommentView {
   pub score: i64,
   pub upvotes: i64,
   pub downvotes: i64,
+  pub hot_rank: i32,
   pub user_id: Option<i32>,
   pub my_vote: Option<i32>,
+  pub subscribed: Option<bool>,
   pub saved: Option<bool>,
 }
 
 pub struct CommentQueryBuilder<'a> {
   conn: &'a PgConnection,
   query: super::comment_view::comment_mview::BoxedQuery<'a, Pg>,
+  listing_type: ListingType,
   sort: &'a SortType,
+  for_community_id: Option<i32>,
   for_post_id: Option<i32>,
   for_creator_id: Option<i32>,
   search_term: Option<String>,
@@ -104,7 +115,9 @@ impl<'a> CommentQueryBuilder<'a> {
     CommentQueryBuilder {
       conn,
       query,
+      listing_type: ListingType::All,
       sort: &SortType::New,
+      for_community_id: None,
       for_post_id: None,
       for_creator_id: None,
       search_term: None,
@@ -115,6 +128,11 @@ impl<'a> CommentQueryBuilder<'a> {
     }
   }
 
+  pub fn listing_type(mut self, listing_type: ListingType) -> Self {
+    self.listing_type = listing_type;
+    self
+  }
+
   pub fn sort(mut self, sort: &'a SortType) -> Self {
     self.sort = sort;
     self
@@ -130,6 +148,11 @@ impl<'a> CommentQueryBuilder<'a> {
     self
   }
 
+  pub fn for_community_id<T: MaybeOptional<i32>>(mut self, for_community_id: T) -> Self {
+    self.for_community_id = for_community_id.get_optional();
+    self
+  }
+
   pub fn search_term<T: MaybeOptional<String>>(mut self, search_term: T) -> Self {
     self.search_term = search_term.get_optional();
     self
@@ -171,6 +194,10 @@ impl<'a> CommentQueryBuilder<'a> {
       query = query.filter(creator_id.eq(for_creator_id));
     };
 
+    if let Some(for_community_id) = self.for_community_id {
+      query = query.filter(community_id.eq(for_community_id));
+    }
+
     if let Some(for_post_id) = self.for_post_id {
       query = query.filter(post_id.eq(for_post_id));
     };
@@ -179,12 +206,18 @@ impl<'a> CommentQueryBuilder<'a> {
       query = query.filter(content.ilike(fuzzy_search(&search_term)));
     };
 
+    if let ListingType::Subscribed = self.listing_type {
+      query = query.filter(subscribed.eq(true));
+    }
+
     if self.saved_only {
       query = query.filter(saved.eq(true));
     }
 
     query = match self.sort {
-      // SortType::Hot => query.order(hot_rank.desc(), published.desc()),
+      SortType::Hot => query
+        .order_by(hot_rank.desc())
+        .then_order_by(published.desc()),
       SortType::New => query.order_by(published.desc()),
       SortType::TopAll => query.order_by(score.desc()),
       SortType::TopYear => query
@@ -199,7 +232,7 @@ impl<'a> CommentQueryBuilder<'a> {
       SortType::TopDay => query
         .filter(published.gt(now - 1.days()))
         .order_by(score.desc()),
-      _ => query.order_by(published.desc()),
+      // _ => query.order_by(published.desc()),
     };
 
     let (limit, offset) = limit_and_offset(self.page, self.limit);
@@ -251,6 +284,7 @@ table! {
     updated -> Nullable<Timestamp>,
     deleted -> Bool,
     community_id -> Int4,
+    community_name -> Varchar,
     banned -> Bool,
     banned_from_community -> Bool,
     creator_name -> Varchar,
@@ -258,8 +292,10 @@ table! {
     score -> BigInt,
     upvotes -> BigInt,
     downvotes -> BigInt,
+    hot_rank -> Int4,
     user_id -> Nullable<Int4>,
     my_vote -> Nullable<Int4>,
+    subscribed -> Nullable<Bool>,
     saved -> Nullable<Bool>,
     recipient_id -> Int4,
   }
@@ -281,6 +317,7 @@ pub struct ReplyView {
   pub updated: Option<chrono::NaiveDateTime>,
   pub deleted: bool,
   pub community_id: i32,
+  pub community_name: String,
   pub banned: bool,
   pub banned_from_community: bool,
   pub creator_name: String,
@@ -288,8 +325,10 @@ pub struct ReplyView {
   pub score: i64,
   pub upvotes: i64,
   pub downvotes: i64,
+  pub hot_rank: i32,
   pub user_id: Option<i32>,
   pub my_vote: Option<i32>,
+  pub subscribed: Option<bool>,
   pub saved: Option<bool>,
   pub recipient_id: i32,
 }
@@ -474,6 +513,7 @@ mod tests {
       creator_id: inserted_user.id,
       post_id: inserted_post.id,
       community_id: inserted_community.id,
+      community_name: inserted_community.name.to_owned(),
       parent_id: None,
       removed: false,
       deleted: false,
@@ -486,9 +526,11 @@ mod tests {
       creator_avatar: None,
       score: 1,
       downvotes: 0,
+      hot_rank: 0,
       upvotes: 1,
       user_id: None,
       my_vote: None,
+      subscribed: None,
       saved: None,
     };
 
@@ -498,6 +540,7 @@ mod tests {
       creator_id: inserted_user.id,
       post_id: inserted_post.id,
       community_id: inserted_community.id,
+      community_name: inserted_community.name.to_owned(),
       parent_id: None,
       removed: false,
       deleted: false,
@@ -510,21 +553,26 @@ mod tests {
       creator_avatar: None,
       score: 1,
       downvotes: 0,
+      hot_rank: 0,
       upvotes: 1,
       user_id: Some(inserted_user.id),
       my_vote: Some(1),
+      subscribed: None,
       saved: None,
     };
 
-    let read_comment_views_no_user = CommentQueryBuilder::create(&conn)
+    let mut read_comment_views_no_user = CommentQueryBuilder::create(&conn)
       .for_post_id(inserted_post.id)
       .list()
       .unwrap();
-    let read_comment_views_with_user = CommentQueryBuilder::create(&conn)
+    read_comment_views_no_user[0].hot_rank = 0;
+
+    let mut read_comment_views_with_user = CommentQueryBuilder::create(&conn)
       .for_post_id(inserted_post.id)
       .my_user_id(inserted_user.id)
       .list()
       .unwrap();
+    read_comment_views_with_user[0].hot_rank = 0;
 
     let like_removed = CommentLike::remove(&conn, &comment_like_form).unwrap();
     let num_deleted = Comment::delete(&conn, inserted_comment.id).unwrap();
diff --git a/server/src/db/post_view.rs b/serv