Repository: LemmyNet/lemmy (language: rust)

Issue:
Return content of removed comments for mods
### Requirements

- [X] Is this a feature request? For questions or discussions use https://lemmy.ml/c/lemmy_support
- [X] Did you check to see if this issue already exists?
- [X] Is this only a feature request? Do not put multiple feature requests in one issue.
- [X] Is this a backend issue? Use the [lemmy-ui](https://github.com/LemmyNet/lemmy-ui) repo for UI / frontend issues.
- [X] Do you agree to follow the rules in our [Code of Conduct](https://join-lemmy.org/docs/code_of_conduct.html)?

### Is your proposal related to a problem?

This issue stems from https://github.com/LemmyNet/lemmy/issues/5230. Essentially, I want to allow mods to view removed comments in a thread view. Since Lemmy removed the comment content for mods, I have to fetch each comment view the mod log with a separate request. This is very inefficient, and it would be nice to provide a way to bulk fetch comments in the modlog by id.

This issue is important for mods because right now if a mod nukes a chain, it's really annoying to have to step through the chain and view the mod log for each comment just in order to view the comment content.

### Describe the solution you'd like.

Bulk fetch comments in modlog by id

### Describe alternatives you've considered.

The alternative, as per https://github.com/LemmyNet/lemmy/issues/5230 (and old lemmy behavior) is to provide the comment content when fetching a comment thread for mods.

### Additional context

_No response_


Discussion / hints:
Why not just have a `show removed comment` button for each one, and have it fetch on demand, and fill the comment box with the most recent removed content? Seems really inefficient to try to collect up all the removed ids for an entire thread, use a SQL in query to get them, then iterate through every response.

That's also not too ideal anyway, since its not going to show you the full history for that item.
One example: It allows mods to go through and selectively approve comments after nuking a comment thread.

0clicks vs 20+ for large nukes (and more if you accidentally refresh…)


Also it’s much easier for mods to audit another mods bulk comment removal actions
> That's also not too ideal anyway, since its not going to show you the full history for that item.

yah agreed. That’s why I prefer old Lemmy behavior, where comment content of removed comments is available for mods on original request. In lieu of that, though…
> Why not just have a `show removed comment` button for each one, and have it fetch on demand, and fill the comment box with the most recent removed content? Seems really inefficient to try to collect up all the removed ids for an entire thread, use a SQL in query to get them, then iterate through every response.
> 
> That's also not too ideal anyway, since its not going to show you the full history for that item.

I think that's a good idea, just being able to reveal removed comments is a good thing for being able to see context in a removed thread. Probably also a good idea for the ones which were removed silently without modlog posts, which is the case with ones removed automatically alongside a ban.
To be honest, this is one of the main issues we currently still have with upgrading.
Right now, I'm using a userscript to reveal deleted comments again, but it can also be done with photon without a userscript, which also makes it more accessible to community mods.
We will at the very least be applying a patch to revert this behavior for admins. We haven't decided on rolling this back entirely yet or looked at the complexity of checking whether the person is a moderator, but I know of several mods that are using this from time to time.
> Probably also a good idea for the ones which were removed silently without modlog posts, which is the case with ones removed automatically alongside a ban.

I thought that was fixed recently. https://github.com/LemmyNet/lemmy/issues/4699
> > Probably also a good idea for the ones which were removed silently without modlog posts, which is the case with ones removed automatically alongside a ban.
> 
> I thought that was fixed recently. #4699

I'm not sure, if it has I'm not seeing the effects of it. I don't think it has though since I'm still not able to see them on any of my servers.
It would actually be pretty easy to return the content of removed comments only for admins. I made a PR for that in #5245. It doesnt mean that lemmy-ui will support this, so you may have to use different frontends or user scripts. It would also be much more complicated to do the same thing for mods (as each returned comment may belong to a different community).
@Nutomic thanks that's awesome!

Regarding showing content for mods, would that be easier once #4365 is completed?
Yes it would be, although it will be a while before I can get to that, although it is a priority for `0.20.0`
@dessalines awesome. I can close this issue and open a new one to address showing removed comment content for mods, with a note that it is blocked until #4365 is done.

Edit: Or that could just be a subtask of #4365.

LMK what is preferred!
We can leave this open bc they're related, but I'll add it to the next milestone.

Fix patch:
diff --git a/crates/api_common/src/post.rs b/crates/api_common/src/post.rs
index 80ef65c971..c9fc33be10 100644
--- a/crates/api_common/src/post.rs
+++ b/crates/api_common/src/post.rs
@@ -4,13 +4,7 @@ use lemmy_db_schema::{
   PostFeatureType,
   PostSortType,
 };
-use lemmy_db_views::structs::{
-  CommunityModeratorView,
-  CommunityView,
-  PaginationCursor,
-  PostView,
-  VoteView,
-};
+use lemmy_db_views::structs::{CommunityView, PaginationCursor, PostView, VoteView};
 use serde::{Deserialize, Serialize};
 use serde_with::skip_serializing_none;
 #[cfg(feature = "full")]
@@ -77,7 +71,6 @@ pub struct GetPost {
 pub struct GetPostResponse {
   pub post_view: PostView,
   pub community_view: CommunityView,
-  pub moderators: Vec<CommunityModeratorView>,
   /// A list of cross-posts, or other times / communities this link has been posted to.
   pub cross_posts: Vec<PostView>,
 }
diff --git a/crates/api_crud/src/post/read.rs b/crates/api_crud/src/post/read.rs
index 043db4f40b..f63a29be91 100644
--- a/crates/api_crud/src/post/read.rs
+++ b/crates/api_crud/src/post/read.rs
@@ -13,7 +13,7 @@ use lemmy_db_schema::{
 };
 use lemmy_db_views::{
   post::post_view::PostQuery,
-  structs::{CommunityModeratorView, CommunityView, LocalUserView, PostView, SiteView},
+  structs::{CommunityView, LocalUserView, PostView, SiteView},
 };
 use lemmy_utils::error::{LemmyErrorType, LemmyResult};
 
@@ -84,8 +84,6 @@ pub async fn get_post(
   )
   .await?;
 
-  let moderators = CommunityModeratorView::for_community(&mut context.pool(), community_id).await?;
-
   // Fetch the cross_posts
   let cross_posts = if let Some(url) = &post_view.post.url {
     let mut x_posts = PostQuery {
@@ -108,7 +106,6 @@ pub async fn get_post(
   Ok(Json(GetPostResponse {
     post_view,
     community_view,
-    moderators,
     cross_posts,
   }))
 }
diff --git a/crates/db_schema/src/impls/comment.rs b/crates/db_schema/src/impls/comment.rs
index 4b298b21cb..0d6f0cb12d 100644
--- a/crates/db_schema/src/impls/comment.rs
+++ b/crates/db_schema/src/impls/comment.rs
@@ -1,5 +1,6 @@
 use crate::{
   diesel::{DecoratableTarget, OptionalExtension},
+  impls::local_user::local_user_can_mod,
   newtypes::{CommentId, DbUrl, PersonId},
   schema::{comment, comment_actions},
   source::comment::{
@@ -16,9 +17,10 @@ use crate::{
 };
 use chrono::{DateTime, Utc};
 use diesel::{
-  dsl::insert_into,
+  dsl::{case_when, insert_into, not},
   expression::SelectableHelper,
   result::Error,
+  BoolExpressionMethods,
   ExpressionMethods,
   NullableExpressionMethods,
   QueryDsl,
@@ -124,6 +126,33 @@ impl Comment {
   }
 }
 
+/// Selects the comment columns, but gives an empty string for content when
+/// deleted or removed, and you're not a mod/admin.
+#[diesel::dsl::auto_type]
+pub fn comment_select_remove_deletes() -> _ {
+  let deleted_or_removed = comment::deleted.or(comment::removed);
+
+  // You can only view the content if it hasn't been removed, or you can mod.
+  let can_view_content = not(deleted_or_removed).or(local_user_can_mod());
+  let content = case_when(can_view_content, comment::content).otherwise("");
+
+  (
+    comment::id,
+    comment::creator_id,
+    comment::post_id,
+    content,
+    comment::removed,
+    comment::published,
+    comment::updated,
+    comment::deleted,
+    comment::ap_id,
+    comment::local,
+    comment::path,
+    comment::distinguished,
+    comment::language_id,
+  )
+}
+
 #[async_trait]
 impl Crud for Comment {
   type InsertForm = CommentInsertForm;
diff --git a/crates/db_schema/src/impls/community.rs b/crates/db_schema/src/impls/community.rs
index 564669a948..12578d5991 100644
--- a/crates/db_schema/src/impls/community.rs
+++ b/crates/db_schema/src/impls/community.rs
@@ -33,7 +33,7 @@ use crate::{
 use chrono::{DateTime, Utc};
 use diesel::{
   deserialize,
-  dsl::{self, exists, insert_into, not},
+  dsl::{exists, insert_into, not},
   expression::SelectableHelper,
   pg::Pg,
   result::Error,
@@ -398,10 +398,6 @@ impl Bannable for CommunityPersonBan {
 }
 
 impl CommunityFollower {
-  pub fn select_subscribed_type() -> dsl::Nullable<community_actions::follow_state> {
-    community_actions::follow_state.nullable()
-  }
-
   /// Check if a remote instance has any followers on local instance. For this it is enough to check
   /// if any follow relation is stored. Dont use this for local community.
   pub async fn check_has_local_followers(
@@ -440,6 +436,14 @@ impl CommunityFollower {
   }
 }
 
+// TODO
+// I'd really like to have these on the impl, but unfortunately they have to be top level,
+// according to https://diesel.rs/guides/composing-applications.html
+#[diesel::dsl::auto_type]
+pub fn community_follower_select_subscribed_type() -> _ {
+  community_actions::follow_state.nullable()
+}
+
 impl Queryable<sql_types::Nullable<crate::schema::sql_types::CommunityFollowerState>, Pg>
   for SubscribedType
 {
diff --git a/crates/db_schema/src/impls/local_user.rs b/crates/db_schema/src/impls/local_user.rs
index fa3b821781..4309f8d52b 100644
--- a/crates/db_schema/src/impls/local_user.rs
+++ b/crates/db_schema/src/impls/local_user.rs
@@ -1,4 +1,5 @@
 use crate::{
+  aliases::creator_community_actions,
   newtypes::{CommunityId, DbUrl, LanguageId, LocalUserId, PersonId},
   schema::{community, community_actions, local_user, person, registration_application},
   source::{
@@ -19,9 +20,12 @@ use bcrypt::{hash, DEFAULT_COST};
 use diesel::{
   dsl::{insert_into, not, IntervalDsl},
   result::Error,
+  BoolExpressionMethods,
   CombineDsl,
   ExpressionMethods,
   JoinOnDsl,
+  NullableExpressionMethods,
+  PgExpressionMethods,
   QueryDsl,
 };
 use diesel_async::RunQueryDsl;
@@ -295,6 +299,29 @@ impl LocalUser {
   }
 }
 
+// TODO
+// I'd really like to have these on the impl, but unfortunately they have to be top level,
+// according to https://diesel.rs/guides/composing-applications.html
+/// Checks to see if you can mod an item.
+///
+/// Caveat: Since admin status isn't federated or ordered, it can't know whether
+/// item creator is a federated admin, or a higher admin.
+/// The back-end will reject an action for admin that is higher via
+/// LocalUser::is_higher_mod_or_admin_check
+#[diesel::dsl::auto_type]
+pub fn local_user_can_mod() -> _ {
+  let am_admin = local_user::admin.nullable();
+  let creator_became_moderator = creator_community_actions
+    .field(community_actions::became_moderator)
+    .nullable();
+
+  let am_higher_mod = community_actions::became_moderator
+    .nullable()
+    .le(creator_became_moderator);
+
+  am_admin.or(am_higher_mod).is_not_distinct_from(true)
+}
+
 /// Adds some helper functions for an optional LocalUser
 pub trait LocalUserOptionHelper {
   fn person_id(&self) -> Option<PersonId>;
diff --git a/crates/db_schema/src/lib.rs b/crates/db_schema/src/lib.rs
index e217c7a6e5..e72514fa9d 100644
--- a/crates/db_schema/src/lib.rs
+++ b/crates/db_schema/src/lib.rs
@@ -23,9 +23,10 @@ pub mod sensitive;
 pub mod schema;
 #[cfg(feature = "full")]
 pub mod aliases {
-  use crate::schema::{community_actions, person};
+  use crate::schema::{community_actions, local_user, person};
   diesel::alias!(
     community_actions as creator_community_actions: CreatorCommunityActions,
+    local_user as creator_local_user: CreatorLocalUser,
     person as person1: Person1,
     person as person2: Person2,
   );
diff --git a/crates/db_views/src/combined/inbox_combined_view.rs b/crates/db_views/src/combined/inbox_combined_view.rs
index 352aff687a..8c3f3ae112 100644
--- a/crates/db_views/src/combined/inbox_combined_view.rs
+++ b/crates/db_views/src/combined/inbox_combined_view.rs
@@ -20,7 +20,8 @@ use diesel::{
 use diesel_async::RunQueryDsl;
 use i_love_jesus::PaginatedQueryBuilder;
 use lemmy_db_schema::{
-  aliases::{self, creator_community_actions},
+  aliases::{self, creator_community_actions, creator_local_user},
+  impls::{community::community_follower_select_subscribed_type, local_user::local_user_can_mod},
   newtypes::PersonId,
   schema::{
     comment,
@@ -44,10 +45,7 @@ use lemmy_db_schema::{
     private_message,
     tag,
   },
-  source::{
-    combined::inbox::{inbox_combined_keys as key, InboxCombined},
-    community::CommunityFollower,
-  },
+  source::combined::inbox::{inbox_combined_keys as key, InboxCombined},
   traits::InternalToCombinedView,
   utils::{functions::coalesce, get_conn, DbPool},
   InboxDataType,
@@ -98,10 +96,12 @@ impl InboxCombinedViewInternal {
 
     let community_join = post::community_id.eq(community::id);
 
-    let local_user_join = local_user::table.on(
+    let local_user_join = local_user::table.on(local_user::person_id.nullable().eq(my_person_id));
+
+    let creator_local_user_join = creator_local_user.on(
       item_creator
-        .eq(local_user::person_id)
-        .and(local_user::admin.eq(true)),
+        .eq(creator_local_user.field(local_user::person_id))
+        .and(creator_local_user.field(local_user::admin).eq(true)),
     );
 
     let post_aggregates_join = post_aggregates::table.on(post::id.eq(post_aggregates::post_id));
@@ -167,6 +167,7 @@ impl InboxCombinedViewInternal {
       .left_join(comment_aggregates_join)
       .left_join(creator_community_actions_join)
       .left_join(local_user_join)
+      .left_join(creator_local_user_join)
       .left_join(community_actions_join)
       .left_join(instance_actions_join)
       .left_join(post_actions_join)
@@ -307,10 +308,13 @@ impl InboxCombinedQuery {
         comment_aggregates::all_columns.nullable(),
         comment_actions::saved.nullable(),
         comment_actions::like_score.nullable(),
-        CommunityFollower::select_subscribed_type(),
+        community_follower_select_subscribed_type(),
         person::all_columns,
         aliases::person1.fields(person::all_columns),
-        local_user::admin.nullable().is_not_null(),
+        creator_local_user
+          .field(local_user::admin)
+          .nullable()
+          .is_not_null(),
         creator_community_actions
           .field(community_actions::became_moderator)
           .nullable()
@@ -321,6 +325,7 @@ impl InboxCombinedQuery {
           .is_not_null(),
         person_actions::blocked.nullable().is_not_null(),
         community_actions::received_ban.nullable().is_not_null(),
+        local_user_can_mod(),
       ))
       .into_boxed();
 
@@ -447,6 +452,7 @@ impl InternalToCombinedView for InboxCombinedViewInternal {
         saved: v.comment_saved,
         my_vote: v.my_comment_vote,
         banned_from_community: v.banned_from_community,
+        can_mod: v.can_mod,
       }))
     } else if let (
       Some(person_comment_mention),
@@ -478,6 +484,7 @@ impl InternalToCombinedView for InboxCombinedViewInternal {
           saved: v.comment_saved,
           my_vote: v.my_comment_vote,
           banned_from_community: v.banned_from_community,
+          can_mod: v.can_mod,
         },
       ))
     } else if let (
@@ -513,6 +520,7 @@ impl InternalToCombinedView for InboxCombinedViewInternal {
         image_details: v.image_details,
         post_tags: v.post_tags,
         banned_from_community: v.banned_from_community,
+        can_mod: v.can_mod,
       }))
     } else if let Some(private_message) = v.private_message {
       Some(InboxCombinedView::PrivateMessage(PrivateMessageView {
diff --git a/crates/db_views/src/combined/person_content_combined_view.rs b/crates/db_views/src/combined/person_content_combined_view.rs
index 414f847542..9faf827ad9 100644
--- a/crates/db_views/src/combined/person_content_combined_view.rs
+++ b/crates/db_views/src/combined/person_content_combined_view.rs
@@ -18,7 +18,8 @@ use diesel::{
 use diesel_async::RunQueryDsl;
 use i_love_jesus::PaginatedQueryBuilder;
 use lemmy_db_schema::{
-  aliases::creator_community_actions,
+  aliases::{creator_community_actions, creator_local_user},
+  impls::{community::community_follower_select_subscribed_type, local_user::local_user_can_mod},
   newtypes::PersonId,
   schema::{
     comment,
@@ -38,10 +39,7 @@ use lemmy_db_schema::{
     post_tag,
     tag,
   },
-  source::{
-    combined::person_content::{person_content_combined_keys as key, PersonContentCombined},
-    community::CommunityFollower,
-  },
+  source::combined::person_content::{person_content_combined_keys as key, PersonContentCombined},
   traits::InternalToCombinedView,
   utils::{functions::coalesce, get_conn, DbPool},
   PersonContentType,
@@ -86,11 +84,12 @@ impl PersonContentCombinedViewInternal {
             .eq(item_creator),
         ),
     );
+    let local_user_join = local_user::table.on(local_user::person_id.nullable().eq(my_person_id));
 
-    let local_user_join = local_user::table.on(
+    let creator_local_user_join = creator_local_user.on(
       item_creator
-        .eq(local_user::person_id)
-        .and(local_user::admin.eq(true)),
+        .eq(creator_local_user.field(local_user::person_id))
+        .and(creator_local_user.field(local_user::admin).eq(true)),
     );
 
     let community_actions_join = community_actions::table.on(
@@ -132,6 +131,7 @@ impl PersonContentCombinedViewInternal {
       .inner_join(community_join)
       .left_join(creator_community_actions_join)
       .left_join(local_user_join)
+      .left_join(creator_local_user_join)
       .left_join(community_actions_join)
       .left_join(post_actions_join)
       .left_join(person_actions_join)
@@ -179,10 +179,12 @@ impl PersonContentCombinedViewInternal {
         ),
     );
 
-    let local_user_join = local_user::table.on(
+    let local_user_join = local_user::table.on(local_user::person_id.nullable().eq(my_person_id));
+
+    let creator_local_user_join = creator_local_user.on(
       item_creator
-        .eq(local_user::person_id)
-        .and(local_user::admin.eq(true)),
+        .eq(creator_local_user.field(local_user::person_id))
+        .and(creator_local_user.field(local_user::admin).eq(true)),
     );
 
     let community_actions_join = community_actions::table.on(
@@ -224,6 +226,7 @@ impl PersonContentCombinedViewInternal {
       .inner_join(community_join)
       .left_join(creator_community_actions_join)
       .left_join(local_user_join)
+      .left_join(creator_local_user_join)
       .left_join(community_actions_join)
       .left_join(post_actions_join)
       .left_join(person_actions_join)
@@ -327,8 +330,11 @@ impl PersonContentCombinedQuery {
         post::all_columns,
         community::all_columns,
         person::all_columns,
-        CommunityFollower::select_subscribed_type(),
-        local_user::admin.nullable().is_not_null(),
+        community_follower_select_subscribed_type(),
+        creator_local_user
+          .field(local_user::admin)
+          .nullable()
+          .is_not_null(),
         creator_community_actions
           .field(community_actions::became_moderator)
           .nullable()
@@ -339,6 +345,7 @@ impl PersonContentCombinedQuery {
           .is_not_null(),
         person_actions::blocked.nullable().is_not_null(),
         community_actions::received_ban.nullable().is_not_null(),
+        local_user_can_mod(),
       ))
       .into_boxed();
 
@@ -404,6 +411,7 @@ impl InternalToCombinedView for PersonContentCombinedViewInternal {
         saved: v.comment_saved,
         my_vote: v.my_comment_vote,
         banned_from_community: v.banned_from_community,
+        can_mod: v.can_mod,
       }))
     } else {
       Some(PersonContentCombinedView::Post(PostView {
@@ -424,6 +432,7 @@ impl InternalToCombinedView for PersonContentCombinedViewInternal {
         image_details: v.image_details,
         banned_from_community: v.banned_from_community,
         tags: v.post_tags,
+        can_mod: v.can_mod,
       }))
     }
   }
diff --git a/crates/db_views/src/combined/person_saved_combined_view.rs b/crates/db_views/src/combined/person_saved_combined_view.rs
index 11356f802a..830e12f035 100644
--- a/crates/db_views/src/combined/person_saved_combined_view.rs
+++ b/crates/db_views/src/combined/person_saved_combined_view.rs
@@ -14,7 +14,8 @@ use diesel::{
 use diesel_async::RunQueryDsl;
 use i_love_jesus::PaginatedQueryBuilder;
 use lemmy_db_schema::{
-  aliases::creator_community_actions,
+  aliases::{creator_community_actions, creator_local_user},
+  impls::{community::community_follower_select_subscribed_type, local_user::local_user_can_mod},
   schema::{
     comment,
     comment_actions,
@@ -32,10 +33,7 @@ use lemmy_db_schema::{
     post_tag,
     tag,
   },
-  source::{
-    combined::person_saved::{person_saved_combined_keys as key, PersonSavedCombined},
-    community::CommunityFollower,
-  },
+  source::combined::person_saved::{person_saved_combined_keys as key, PersonSavedCombined},
   traits::InternalToCombinedView,
   utils::{functions::coalesce, get_conn, DbPool},
   PersonContentType,
@@ -124,8 +122,11 @@ impl PersonSavedCombinedQuery {
         post::all_columns,
         community::all_columns,
         person::all_columns,
-        CommunityFollower::select_subscribed_type(),
-        local_user::admin.nullable().is_not_null(),
+        community_follower_select_subscribed_type(),
+        creator_local_user
+          .field(local_user::admin)
+          .nullable()
+          .is_not_null(),
         creator_community_actions
           .field(community_actions::became_moderator)
           .nullable()
@@ -136,6 +137,7 @@ impl PersonSavedCombinedQuery {
           .is_not_null(),
         person_actions::blocked.nullable().is_not_null(),
         community_actions::received_ban.nullable().is_not_null(),
+        local_user_can_mod(),
       ))
       .into_boxed();
 
diff --git a/crates/db_views/src/combined/report_combined_view.rs b/crates/db_views/src/combined/report_combined_view.rs
index 025d24f330..9a54395cbc 100644
--- a/crates/db_views/src/combined/report_combined_view.rs
+++ b/crates/db_views/src/combined/report_combined_view.rs
@@ -22,6 +22,7 @@ use diesel_async::RunQueryDsl;
 use i_love_jesus::PaginatedQueryBuilder;
 use lemmy_db_schema::{
   aliases::{self, creator_community_actions},
+  impls::community::community_follower_select_subscribed_type,
   newtypes::{CommunityId, PersonId, PostId},
   schema::{
     comment,
@@ -43,10 +44,7 @@ use lemmy_db_schema::{
     private_message_report,
     report_combined,
   },
-  source::{
-    combined::report::{report_combined_keys as key, ReportCombined},
-    community::CommunityFollower,
-  },
+  source::combined::report::{report_combined_keys as key, ReportCombined},
   traits::InternalToCombinedView,
   utils::{functions::coalesce, get_conn, DbPool, ReverseTimestampKey},
   ReportType,
@@ -301,7 +299,7 @@ impl ReportCombinedQuery {
         person::all_columns,
         aliases::person1.fields(person::all_columns.nullable()),
         community::all_columns.nullable(),
-        CommunityFollower::select_subscribed_type(),
+        community_follower_select_subscribed_type(),
         aliases::person2.fields(person::all_columns.nullable()),
         local_user::admin.nullable().is_not_null(),
         creator_community_actions
diff --git a/crates/db_views/src/combined/search_combined_view.rs b/crates/db_views/src/combined/search_combined_view.rs
index cd879351b8..d861f0687b 100644
--- a/crates/db_views/src/combined/search_combined_view.rs
+++ b/crates/db_views/src/combined/search_combined_view.rs
@@ -22,7 +22,8 @@ use diesel::{
 use diesel_async::RunQueryDsl;
 use i_love_jesus::PaginatedQueryBuilder;
 use lemmy_db_schema::{
-  aliases::creator_community_actions,
+  aliases::{creator_community_actions, creator_local_user},
+  impls::{community::community_follower_select_subscribed_type, local_user::local_user_can_mod},
   newtypes::{CommunityId, PersonId},
   schema::{
     comment,
@@ -43,10 +44,7 @@ use lemmy_db_schema::{
     search_combined,
     tag,
   },
-  source::{
-    combined::search::{search_combined_keys as key, SearchCombined},
-    community::CommunityFollower,
-  },
+  source::combined::search::{search_combined_keys as key, SearchCombined},
   traits::InternalToCombinedView,
   utils::{
     functions::coalesce,
@@ -118,10 +116,13 @@ impl SearchCombinedViewInternal {
             .eq(item_creator),
         ),
     );
-    let local_user_join = local_user::table.on(
+
+    let local_user_join = local_user::table.on(local_user::person_id.nullable().eq(my_person_id));
+
+    let creator_local_user_join = creator_local_user.on(
       item_creator
-        .eq(local_user::person_id)
-        .and(local_user::admin.eq(true)),
+        .eq(creator_local_user.field(local_user::person_id))
+        .and(creator_local_user.field(local_user::admin).eq(true)),
     );
 
     let community_actions_join = community_actions::table.on(
@@ -169,6 +170,7 @@ impl SearchCombinedViewInternal {
       .left_join(community_join)
       .left_join(creator_community_actions_join)
       .left_join(local_user_join)
+      .left_join(creator_local_user_join)
       .left_join(community_actions_join)
       .left_join(post_actions_join)
       .left_join(person_actions_join)
@@ -279,7 +281,7 @@ impl SearchCombinedQuery {
         community::all_columns.nullable(),
         community_aggregates::all_columns.nullable(),
         community_actions::blocked.nullable().is_not_null(),
-        CommunityFollower::select_subscribed_type(),
+        community_follower_select_subscribed_type(),
         // Person
         person_aggregates::all_columns.nullable(),
         // // Shared
@@ -295,6 +297,7 @@ impl SearchCombinedQuery {
           .is_not_null(),
         person_actions::blocked.nullable().is_not_null(),
         community_actions::received_ban.nullable().is_not_null(),
+        local_user_can_mod(),
       ))
       .into_boxed();
 
@@ -461,6 +464,7 @@ impl InternalToCombinedView for SearchCombinedViewInternal {
         saved: v.comment_saved,
         my_vote: v.my_comment_vote,
         banned_from_community: v.banned_from_community,
+        can_mod: v.can_mod,
       }))
     } else if let (
       Some(post),
@@ -493,6 +497,7 @@ impl InternalToCombinedView for SearchCombinedViewInternal {
         image_details: v.image_details,
         banned_from_community: v.banned_from_community,
         tags: v.post_tags,
+        can_mod: v.can_mod,
       }))
     } else if let (Some(community), Some(counts)) = (v.community, v.community_counts) {
       Some(SearchCombinedView::Community(CommunityView {
@@ -501,6 +506,7 @@ impl InternalToCombinedView for SearchCombinedViewInternal {
         subscribed: v.subscribed,
         blocked: v.community_blocked,
         banned_from_community: v.banned_from_community,
+        can_mod: v.can_mod,
       }))
     } else if let (Some(person), Some(counts)) = (v.item_creator, v.item_creator_counts) {
       Some(SearchCombinedView::Person(PersonView {
diff --git a/crates/db_views/src/comment/comment_view.rs b/crates/db_views/src/comment/comment_view.rs
index 04bc56c164..cd6c345f28 100644
--- a/crates/db_views/src/comment/comment_view.rs
+++ b/crates/db_views/src/comment/comment_view.rs
@@ -1,12 +1,11 @@
 use crate::structs::{CommentSlimView, CommentView};
 use diesel::{
-  dsl::{exists, not},
+  dsl::exists,
   result::Error,
   BoolExpressionMethods,
   ExpressionMethods,
   JoinOnDsl,
   NullableExpressionMethods,
-  PgTextExpressionMethods,
   QueryDsl,
   SelectableHelper,
 };
@@ -23,13 +22,14 @@ use lemmy_db_schema::{
     community,
     community_actions,
     instance_actions,
+    local_user,
     local_user_language,
     person,
     person_actions,
     post,
   },
   source::{community::CommunityFollowerState, local_user::LocalUser, site::Site},
-  utils::{fuzzy_search, get_conn, limit_and_offset, now, seconds_to_pg_interval, DbPool},
+  utils::{get_conn, limit_and_offset, now, seconds_to_pg_interval, DbPool},
   CommentSortType,
   CommunityVisibility,
   ListingType,
@@ -75,6 +75,8 @@ impl CommentView {
         ),
     );
 
+    let local_user_join = local_user::table.on(local_user::person_id.nullable().eq(my_person_id));
+
     comment::table
       .inner_join(person::table)
       .inner_join(post::table)
@@ -85,6 +87,7 @@ impl CommentView {
       .left_join(person_actions_join)
       .left_join(instance_actions_join)
       .left_join(comment_creator_community_actions_join)
+      .left_join(local_user_join)
   }
 
   pub async fn read(
@@ -121,8 +124,7 @@ impl CommentView {
       res.my_vote = Some(0);
     }
 
-    let is_admin = my_local_user.map(|u| u.admin).unwrap_or(false);
-    Ok(handle_deleted(res, is_admin))
+    Ok(res)
   }
 
   pub fn map_to_slim(self) -> CommentSlimView {
@@ -138,6 +140,7 @@ impl CommentView {
       saved: self.saved,
       creator_blocked: self.creator_blocked,
       my_vote: self.my_vote,
+      can_mod: self.can_mod,
     }
   }
 }
@@ -152,7 +155,6 @@ pub struct CommentQuery<'a> {
   pub parent_path: Option<Ltree>,
   pub creator_id: Option<PersonId>,
   pub local_user: Option<&'a LocalUser>,
-  pub search_term: Option<String>,
   pub liked_only: Option<bool>,
   pub disliked_only: Option<bool>,
   pub page: Option<i64>,
@@ -184,14 +186,6 @@ impl CommentQuery<'_> {
     if let Some(parent_path) = o.parent_path.as_ref() {
       query = query.filter(comment::path.contained_by(parent_path));
     };
-    //filtering out removed and deleted comments from search
-    if let Some(search_term) = o.search_term {
-      query = query.filter(
-        comment::content
-          .ilike(fuzzy_search(&search_term))
-          .and(not(comment::removed.or(comment::deleted))),
-      );
-    };
 
     if let Some(community_id) = o.community_id {
       query = query.filter(post::community_id.eq(community_id));
@@ -330,26 +324,10 @@ impl CommentQuery<'_> {
       .load::<CommentView>(conn)
       .await?;
 
-    let is_admin = o.local_user.map(|u| u.admin).unwrap_or(false);
-
-    // Note: deleted and removed comments are done on the front side
-    Ok(
-      res
-        .into_iter()
-        .map(|c| handle_deleted(c, is_admin))
-        .collect(),
-    )
+    Ok(res)
   }
 }
 
-/// Only show deleted / removed content for admins.
-fn handle_deleted(mut c: CommentView, is_admin: bool) -> CommentView {
-  if !is_admin && (c.comment.deleted || c.comment.removed) {
-    c.comment.content = String::new();
-  }
-  c
-}
-
 #[cfg(test)]
 #[expect(clippy::indexing_slicing)]
 mod tests {
@@ -380,7 +358,7 @@ mod tests {
       },
       instance::Instance,
       language::Language,
-      local_user::{LocalUser, LocalUserInsertForm},
+      local_user::{LocalUser, LocalUserInsertForm, LocalUserUpdateForm},
       local_user_vote_display_mode::LocalUserVoteDisplayMode,
       person::{Person, PersonInsertForm},
       person_block::{PersonBlock, PersonBlockForm},
@@ -565,6 +543,7 @@ mod tests {
 
     let mut expected_comment_view_with_person = expected_comment_view_no_person.clone();
     expected_comment_view_with_person.my_vote = Some(1);
+    expected_comment_view_with_person.can_mod = true;
 
     let read_comment_views_no_person = CommentQuery {
       sort: (Some(CommentSortType::Old)),
@@ -904,6 +883,7 @@ mod tests {
       subscribed: SubscribedType::NotSubscribed,
       saved: None,
       creator_blocked: false,
+      can_mod: false,
       comment: Comment {
         id: data.inserted_comment_0.id,
         content: "Comment 0".into(),
@@ -1253,6 +1233,16 @@ mod tests {
     Comment::update(pool, data.inserted_comment_0.id, &form).await?;
 
     // Read as normal user, content is cleared
+    // Timmy leaves admin
+    LocalUser::update(
+      pool,
+      data.timmy_local_user_view.local_user.id,
+      &LocalUserUpdateForm {
+        admin: Some(false),
+        ..Default::default()
+      },
+    )
+    .await?;
     data.timmy_local_user_view.local_user.admin = false;
     let comment_view = CommentView::read(
       pool,
@@ -1272,6 +1262,15 @@ mod tests {
     assert_eq!("", comment_listing[0].comment.content);
 
     // Read as admin, content is returned
+    LocalUser::update(
+      pool,
+      data.timmy_local_user_view.local_user.id,
+      &LocalUserUpdateForm {
+        admin: Some(true),
+        ..Default::default()
+      },
+    )
+    .await?;
     data.timmy_local_user_view.local_user.admin = true;
     let comment_view = CommentView::read(
       pool,
diff --git a/crates/db_views/src/community/community_follower_view.rs b/crates/db_views/src/community/community_follower_view.rs
index e439c14b99..60ec9d9609 100644
--- a/crates/db_views/src/community/community_follower_view.rs
+++ b/crates/db_views/src/community/community_follower_view.rs
@@ -12,10 +12,11 @@ use diesel::{
 };
 use diesel_async::RunQueryDsl;
 use lemmy_db_schema::{
+  impls::community::community_follower_select_subscribed_type,
   newtypes::{CommunityId, DbUrl, InstanceId, PersonId},
   schema::{community, community_actions, person},
   source::{
-    community::{Community, CommunityFollower, CommunityFollowerState},
+    community::{Community, CommunityFollowerState},
     person::Person,
   },
   utils::{get_conn, limit_and_offset, DbPool},
@@ -150,7 +151,7 @@ impl CommunityFollowerView {
         person::all_columns,
         community::all_columns,
         is_new_instance,
-        CommunityFollower::select_subscribed_type(),
+        community_follower_select_subscribed_type(),
       ))
       .into_boxed();
     if all_communities {
diff --git a/crates/db_views/src/community/community_view.rs b/crates/db_views/src/community/community_view.rs
index ada02ad727..b5061e927d 100644
--- a/crates/db_views/src/community/community_view.rs
+++ b/crates/db_views/src/community/community_view.rs
@@ -12,7 +12,7 @@ use diesel_async::RunQueryDsl;
 use lemmy_db_schema::{
   impls::local_user::LocalUserOptionHelper,
   newtypes::{CommunityId, PersonId},
-  schema::{community, community_actions, community_aggregates, instance_actions},
+  schema::{community, community_actions, community_aggregates, instance_actions, local_user},
   source::{
     community::{Community, CommunityFollowerState},
     local_user::LocalUser,
@@ -38,10 +38,13 @@ impl CommunityView {
         .and(instance_actions::person_id.nullable().eq(person_id)),
     );
 
+    let local_user_join = local_user::table.on(local_user::person_id.nullable().eq(person_id));
+
     community::table
       .inner_join(community_aggregates::table)
       .left_join(community_actions_join)
       .left_join(instance_actions_join)
+      .left_join(local_user_join)
   }
 
   pub async fn read(
@@ -209,6 +212,8 @@ mod tests {
         CommunityFollowerForm,
         CommunityFollowerState,
         CommunityInsertForm,
+        CommunityModerator,
+        CommunityModeratorForm,
         CommunityUpdateForm,
       },
       instance::Instance,
@@ -216,7 +221,7 @@ mod tests {
       person::{Person, PersonInsertForm},
       site::Site,
     },
-    traits::{Crud, Followable},
+    traits::{Crud, Followable, Joinable},
     utils::{build_db_pool_for_tests, DbPool},
     CommunityVisibility,
     SubscribedType,
@@ -446,4 +451,56 @@ mod tests {
 
     cleanup(data, pool).await
   }
+
+  #[tokio::test]
+  #[serial]
+  async fn can_mod() -> LemmyResult<()> {
+    let pool = &build_db_pool_for_tests();
+    let pool = &mut pool.into();
+    let data = init_data(pool).await?;
+
+    // Make sure can_mod is false for all of them.
+    CommunityQuery {
+      local_user: Some(&data.local_user),
+      ..Default::default()
+    }
+    .list(&data.site, pool)
+    .await?
+    .into_iter()
+    .for_each(|c| assert!(!c.can_mod));
+
+    let person_id = data.local_user.person_id;
+
+    // Now join the mod team of test community 1 and 2
+    let mod_form_1 = CommunityModeratorForm {
+      community_id: data.communities[0].id,
+      person_id,
+    };
+    CommunityModerator::join(pool, &mod_form_1).await?;
+
+    let mod_form_2 = CommunityModeratorForm {
+      community_id: data.communities[1].id,
+      person_id,
+    };
+    CommunityModerator::join(pool, &mod_form_2).await?;
+
+    let mod_query = CommunityQuery {
+      local_user: Some(&data.local_user),
+      ..Default::default()
+    }
+    .list(&data.site, pool)
+    .await?
+    .into_iter()
+    .map(|c| (c.community.name, c.can_mod))
+    .collect::<Vec<_>>();
+
+    let expected_communities = vec![
+      ("test_community_1".to_owned(), true),
+      ("test_community_2".to_owned(), true),
+      ("test_community_3".to_owned(), false),
+    ];
+    assert_eq!(expected_communities, mod_query);
+
+    cleanup(data, pool).await
+  }
 }
diff --git a/crates/db_views/src/post/post_view.rs b/crates/db_views/src/post/post_view.rs
index 212eed0570..8a5e07fc4d 100644
--- a/crates/db_views/src/post/post_view.rs
+++ b/crates/db_views/src/post/post_view.rs
@@ -17,8 +17,11 @@ use diesel::{
 use diesel_async::RunQueryDsl;
 use lemmy_db_schema::{
   aggregates::structs::{post_aggregates_keys as key, PostAggregates},
-  aliases::creator_community_actions,
-  impls::local_user::LocalUserOptionHelper,
+  aliases::{creator_community_actions, creator_local_user},
+  impls::{
+    community::community_follower_select_subscribed_type,
+    local_user::{local_user_can_mod, LocalUserOptionHelper},
+  },
   newtypes::{CommunityId, PersonId, PostId},
   schema::{
     community,
@@ -36,7 +39,7 @@ use lemmy_db_schema::{
     tag,
   },
   source::{
-    community::{CommunityFollower, CommunityFollowerState},
+    community::CommunityFollowerState,
     local_user::LocalUser,
     post::{post_actions_keys, PostActionsCursor},
     site::Site,
@@ -105,6 +108,8 @@ impl PostView {
     let image_details_join =
       image_details::table.on(post::thumbnail_url.eq(image_details::link.nullable()));
 
+    let local_user_join = local_user::table.on(local_user::person_id.nullable().eq(my_person_id));
+
     post_aggregates::table
       .inner_join(person::table)
       .inner_join(community::table)
@@ -115,15 +120,16 @@ impl PostView {
       .left_join(post_actions_join)
       .left_join(instance_actions_join)
       .left_join(post_creator_community_actions_join)
+      .left_join(local_user_join)
   }
 
   #[diesel::dsl::auto_type(no_type_alias)]
   fn creator_is_admin() -> _ {
     exists(
-      local_user::table.filter(
+      creator_local_user.filter(
         post_aggregates::creator_id
-          .eq(local_user::person_id)
-          .and(local_user::admin.eq(true)),
+          .eq(creator_local_user.field(local_user::person_id))
+          .and(creator_local_user.field(local_user::admin).eq(true)),
       ),
     )
   }
@@ -164,7 +170,7 @@ impl PostView {
           .is_not_null(),
         Self::creator_is_admin(),
         post_aggregates::all_columns,
-        CommunityFollower::select_subscribed_type(),
+        community_follower_select_subscribed_type(),
         post_actions::saved.nullable(),
         post_actions::read.nullable().is_not_null(),
         post_actions::hidden.nullable().is_not_null(),
@@ -175,6 +181,7 @@ impl PostView {
           post_aggregates::comments,
         ),
         post_tags,
+        local_user_can_mod(),
       ))
       .into_boxed();
 
@@ -412,7 +419,7 @@ impl<'a> PostQuery<'a> {
           .is_not_null(),
         PostView::creator_is_admin(),
         post_aggregates::all_columns,
-        CommunityFollower::select_subscribed_type(),
+        community_follower_select_subscribed_type(),
         post_actions::saved.nullable(),
         post_actions::read.nullable().is_not_null(),
         post_actions::hidden.nullable().is_not_null(),
@@ -423,6 +430,7 @@ impl<'a> PostQuery<'a> {
           post_aggregates::comments,
         ),
         post_tags,
+        local_user_can_mod(),
       ))
       .into_boxed();
 
@@ -719,14 +727,14 @@ mod tests {
 
   struct Data {
     pool: ActualDbPool,
-    inserted_instance: Instance,
-    local_user_view: LocalUserView,
-    blocked_local_user_view: LocalUserView,
-    inserted_bot: Person,
-    inserted_community: Community,
-    inserted_post: Post,
-    inserted_bot_post: Post,
-    inserted_post_with_tags: Post,
+    instance: Instance,
+    tegan_local_user_view: LocalUserView,
+    john_local_user_view: LocalUserView,
+    bot_local_user_view: LocalUserView,
+    community: Community,
+    post: Post,
+    bot_post: Post,
+    post_with_tags: Post,
     tag_1: Tag,
     tag_2: Tag,
     site: Site,
@@ -742,7 +750,7 @@ mod tests {
     fn default_post_query(&self) -> PostQuery<'_> {
       PostQuery {
         sort: Some(PostSortType::New),
-        local_user: Some(&self.local_user_view.local_user),
+        local_user: Some(&self.tegan_local_user_view.local_user),
         ..Default::default()
       }
     }
@@ -750,41 +758,43 @@ mod tests {
     async fn setup() -> LemmyResult<Data> {
       let actual_pool = build_db_pool()?;
       let pool = &mut (&actual_pool).into();
-      let inserted_instance = Instance::read_or_create(pool, "my_domain.tld".to_string()).await?;
-
-      let new_person = PersonInsertForm::test_form(inserted_instance.id, "tegan");
+      let instance = Instance::read_or_create(pool, "my_domain.tld".to_string()).await?;
 
-      let inserted_person = Person::create(pool, &new_person).await?;
-
-      let local_user_form = LocalUserInsertForm {
+      let tegan_person_form = PersonInsertForm::test_form(instance.id, "tegan");
+      let inserted_tegan_person = Person::create(pool, &tegan_person_form).await?;
+      let tegan_local_user_form = LocalUserInsertForm {
         admin: Some(true),
-        ..LocalUserInsertForm::test_form(inserted_person.id)
+        ..LocalUserInsertForm::test_form(inserted_tegan_person.id)
       };
-      let inserted_local_user = LocalUser::create(pool, &local_user_form, vec![]).await?;
+      let inserted_tegan_local_user =
+        LocalUser::create(pool, &tegan_local_user_form, vec![]).await?;
 
-      let new_bot = PersonInsertForm {
+      let bot_person_form = PersonInsertForm {
         bot_account: Some(true),
-        ..PersonInsertForm::test_form(inserted_instance.id, "mybot")
+        ..PersonInsertForm::test_form(instance.id, "mybot")
       };
-
-      let inserted_bot = Person::create(pool, &new_bot).await?;
+      let inserted_bot_person = Person::create(pool, &bot_person_form).await?;
+      let inserted_bot_local_user = LocalUser::create(
+        pool,
+        &LocalUserInsertForm::test_form(inserted_bot_person.id),
+        vec![],
+      )
+      .await?;
 
       let new_community = CommunityInsertForm::new(
-        inserted_instance.id,
+        instance.id,
         "test_community_3".to_string(),
         "nada".to_owned(),
         "pubkey".to_string(),
       );
-      let inserted_community = Community::create(pool, &new_community).await?;
+      let community = Community::create(pool, &new_community).await?;
 
       // Test a person block, make sure the post query doesn't include their post
-      let blocked_person = PersonInsertForm::test_form(inserted_instance.id, "john");
-
-      let inserted_blocked_person = Person::create(pool, &blocked_person).await?;
-
-      let inserted_blocked_local_user = LocalUser::create(
+      let john_person_form = PersonInsertForm::test_form(instance.id, "john");
+      let inserted_john_person = Person::create(pool, &john_person_form).await?;
+      let inserted_john_local_user = LocalUser::create(
         pool,
-        &LocalUserInsertForm::test_form(inserted_blocked_person.id),
+        &LocalUserInsertForm::test_form(inserted_john_person.id),
         vec![],
       )
       .await?;
@@ -793,16 +803,16 @@ mod tests {
         language_id: Some(LanguageId(1)),
         ..PostInsertForm::new(
           POST_BY_BLOCKED_PERSON.to_string(),
-          inserted_blocked_person.id,
-          inserted_community.id,
+          inserted_john_person.id,
+          community.id,
         )
       };
       Post::create(pool, &post_from_blocked_person).await?;
 
       // block that person
       let person_block = PersonBlockForm {
-        person_id: inserted_person.id,
-        target_id: inserted_blocked_person.id,
+        person_id: inserted_tegan_person.id,
+        target_id: inserted_john_person.id,
       };
 
       PersonBlock::block(pool, &person_block).await?;
@@ -811,9 +821,9 @@ mod tests {
       let tag_1 = Tag::create(
         pool,
         &TagInsertForm {
-          ap_id: Url::parse(&format!("{}/tags/test_tag1", inserted_community.ap_id))?.into(),
+          ap_id: Url::parse(&format!("{}/tags/test_tag1", community.ap_id))?.into(),
           name: "Test Tag 1".into(),
-          community_id: inserted_community.id,
+          community_id: community.id,
           published: None,
           updated: None,
           deleted: false,
@@ -823,9 +833,9 @@ mod tests {
       let tag_2 = Tag::create(
         pool,
         &TagInsertForm {
-          ap_id: Url::parse(&format!("{}/tags/test_tag2", inserted_community.ap_id))?.into(),
+          ap_id: Url::parse(&format!("{}/tags/test_tag2", community.ap_id))?.into(),
           name: "Test Tag 2".into(),
-          community_id: inserted_community.id,
+          community_id: community.id,
           published: None,
           updated: None,
           deleted: false,
@@ -836,51 +846,58 @@ mod tests {
       // A sample post
       let new_post = PostInsertForm {
         language_id: Some(LanguageId(47)),
-        ..PostInsertForm::new(POST.to_string(), inserted_person.id, inserted_community.id)
+        ..PostInsertForm::new(POST.to_string(), inserted_tegan_person.id, community.id)
       };
 
-      let inserted_post = Post::create(pool, &new_post).await?;
+      let post = Post::create(pool, &new_post).await?;
 
       let new_bot_post = PostInsertForm::new(
         POST_BY_BOT.to_string(),
-        inserted_bot.id,
-        inserted_community.id,
+        inserted_bot_person.id,
+        community.id,
       );
-      let inserted_bot_post = Post::create(pool, &new_bot_post).await?;
+      let bot_post = Post::create(pool, &new_bot_post).await?;
 
       // A sample post with tags
       let new_post = PostInsertForm {
         language_id: Some(LanguageId(47)),
         ..PostInsertForm::new(
           POST_WITH_TAGS.to_string(),
-          inserted_person.id,
-          inserted_community.id,
+          inserted_tegan_person.id,
+          community.id,
         )
       };
 
-      let inserted_post_with_tags = Post::create(pool, &new_post).await?;
+      let post_with_tags = Post::create(pool, &new_post).await?;
       let inserted_tags = vec![
         PostTagInsertForm {
-          post_id: inserted_post_with_tags.id,
+          post_id: post_with_tags.id,
           tag_id: tag_1.id,
         },
         PostTagInsertForm {
-          post_id: inserted_post_with_tags.id,
+          post_id: post_with_tags.id,
           tag_id: tag_2.id,
         },
       ];
       PostTagInsertForm::insert_tag_associations(pool, &inserted_tags).await?;
 
-      let local_user_view = LocalUserView {
-        local_user: inserted_local_user,
+      let tegan_local_user_view = LocalUserView {
+        local_user: inserted_tegan_local_user,
         local_user_vote_display_mode: LocalUserVoteDisplayMode::default(),
-        person: inserted_person,
+        person: inserted_tegan_person,
         counts: Default::default(),
       };
-      let blocked_local_user_view = LocalUserView {
-        local_user: inserted_blocked_local_user,
+      let john_local_user_view = LocalUserView {
+        local_user: inserted_john_local_user,
+        local_user_vote_display_mode: LocalUserVoteDisplayMode::default(),
+        person: inserted_john_person,
+        counts: Default::default(),
+      };
+
+      let bot_local_user_view = LocalUserView {
+        local_user: inserted_bot_local_user,
         local_user_vote_display_mode: LocalUserVoteDisplayMode::default(),
-        person: inserted_blocked_person,
+        person: inserted_bot_person,
         counts: Default::default(),
       };
 
@@ -904,14 +921,14 @@ mod tests {
 
       Ok(Data {
         pool: actual_pool,
-        inserted_instance,
-        local_user_view,
-        blocked_local_user_view,
-        inserted_bot,
-        inserted_community,
-        inserted_post,
-        inserted_bot_post,
-        inserted_post_with_tags,
+        instance,
+        tegan_local_user_view,
+        john_local_user_view,
+        bot_local_user_view,
+        community,
+        post,
+        bot_post,
+        post_with_tags,
         tag_1,
         tag_2,
         site,
@@ -920,12 +937,12 @@ mod tests {
     async fn teardown(data: Data) -> LemmyResult<()> {
       let pool = &mut data.pool2();
       // let pool = &mut (&pool).into();
-      let num_deleted = Post::delete(pool, data.inserted_post.id).await?;
-      Community::delete(pool, data.inserted_community.id).await?;
-      Person::delete(pool, data.local_user_view.person.id).await?;
-      Person::delete(pool, data.inserted_bot.id).await?;
-      Person::delete(pool, data.blocked_local_user_view.person.id).await?;
-      Instance::delete(pool, data.inserted_instance.id).await?;
+      let num_deleted = Post::delete(pool, data.post.id).await?;
+      Community::delete(pool, data.community.id).await?;
+      Person::delete(pool, data.tegan_local_user_view.person.id).await?;
+      Person::delete(pool, data.bot_local_user_view.person.id).await?;
+      Person::delete(pool, data.john_local_user_view.person.id).await?;
+      Instance::delete(pool, data.instance.id).await?;
       assert_eq!(1, num_deleted);
 
       Ok(())
@@ -951,11 +968,16 @@ mod tests {
       show_bot_accounts: Some(false),
       ..Default::default()
     };
-    LocalUser::update(pool, data.local_user_view.local_user.id, &local_user_form).await?;
-    data.local_user_view.local_user.show_bot_accounts = false;
+    LocalUser::update(
+      pool,
+      data.tegan_local_user_view.local_user.id,
+      &local_user_form,
+    )
+    .await?;
+    data.tegan_local_user_view.local_user.show_bot_accounts = false;
 
     let mut read_post_listing = PostQuery {
-      community_id: Some(data.inserted_community.id),
+      community_id: Some(data.community.id),
       ..data.default_post_query()
     }
     .list(&data.site, pool)
@@ -965,8 +987,8 @@ mod tests {
 
     let post_listing_single_with_person = PostView::read(
       pool,
-      data.inserted_post.id,
-      Some(&data.local_user_view.local_user),
+      data.post.id,
+      Some(&data.tegan_local_user_view.local_user),
       false,
     )
     .await?;
@@ -987,11 +1009,16 @@ mod tests {
       show_bot_accounts: Some(true),
       ..Default::default()
     };
-    LocalUser::update(pool, data.local_user_view.local_user.id, &local_user_form).await?;
-    data.local_user_view.local_user.show_bot_accounts = true;
+    LocalUser::update(
+      pool,
+      data.tegan_local_user_view.local_user.id,
+      &local_user_form,
+    )
+    .await?;
+    data.tegan_local_user_view.local_user.show_bot_accounts = true;
 
     let post_listings_with_bots = PostQuery {
-      community_id: Some(data.inserted_community.id),
+      community_id: Some(data.community.id),
       ..data.default_post_query()
     }
     .list(&data.site, pool)
@@ -1012,7 +1039,7 @@ mod tests {
     let pool = &mut pool.into();
 
     let read_post_listing_multiple_no_person = PostQuery {
-      community_id: Some(data.inserted_community.id),
+      community_id: Some(data.community.id),
       local_user: None,
       ..data.default_post_query()
     }
@@ -1020,9 +1047,10 @@ mod tests {
     .await?;
 
     let read_post_listing_single_no_person =
-      PostView::read(pool, data.inserted_post.id, None, false).await?;
+      PostView::read(pool, data.post.id, None, false).await?;
 
-    let expected_post_listing_no_person = expected_post_view(data, pool).await?;
+    let mut expected_post_listing_no_person = expected_post_view(data, pool).await?;
+    expected_post_listing_no_person.can_mod = false;
 
     // Should be 2 posts, with the bot post, and the blocked
     assert_eq!(
@@ -1054,15 +1082,15 @@ mod tests {
       body: Some("Post".to_string()),
       ..PostInsertForm::new(
         POST_WITH_ANOTHER_TITLE.to_string(),
-        data.local_user_view.person.id,
-        data.inserted_community.id,
+        data.tegan_local_user_view.person.id,
+        data.community.id,
       )
     };
 
     let inserted_post = Post::create(pool, &new_post).await?;
 
     let read_post_listing_by_title_only = PostQuery {
-      community_id: Some(data.inserted_community.id),
+      community_id: Some(data.community.id),
       local_user: None,
       search_term: Some("Post".to_string()),
       title_only: Some(true),
@@ -1072,7 +1100,7 @@ mod tests {
     .await?;
 
     let read_post_listing = PostQuery {
-      community_id: Some(data.inserted_community.id),
+      community_id: Some(data.community.id),
       local_user: None,
       search_term: Some("Post".to_string()),
       ..data.default_post_query()
@@ -1109,13 +1137,13 @@ mod tests {
     let pool = &mut pool.into();
 
     let community_block = CommunityBlockForm {
-      person_id: data.local_user_view.person.id,
-      community_id: data.inserted_community.id,
+      person_id: data.tegan_local_user_view.person.id,
+      community_id: data.community.id,
     };
     CommunityBlock::block(pool, &community_block).await?;
 
     let read_post_listings_with_person_after_block = PostQuery {
-      community_id: Some(data.inserted_community.id),
+      community_id: Some(data.community.id),
       ..data.default_post_query()
     }
     .list(&data.site, pool)
@@ -1134,14 +1162,13 @@ mod tests {
     let pool = &data.pool();
     let pool = &mut pool.into();
 
-    let post_like_form =
-      PostLikeForm::new(data.inserted_post.id, data.local_user_view.person.id, 1);
+    let post_like_form = PostLikeForm::new(data.post.id, data.tegan_local_user_view.person.id, 1);
 
     let inserted_post_like = PostLike::like(pool, &post_like_form).await?;
 
     let expected_post_like = PostLike {
-      post_id: data.inserted_post.id,
-      person_id: data.local_user_view.person.id,
+      post_id: data.post.id,
+      person_id: data.tegan_local_user_view.person.id,
       published: inserted_post_like.published,
       score: 1,
     };
@@ -1149,8 +1176,8 @@ mod tests {
 
     let post_listing_single_with_person = PostView::read(
       pool,
-      data.inserted_post.id,
-      Some(&data.local_user_view.local_user),
+      data.post.id,
+      Some(&data.tegan_local_user_view.local_user),
       false,
     )
     .await?;
@@ -1165,11 +1192,16 @@ mod tests {
       show_bot_accounts: Some(false),
       ..Default::default()
     };
-    LocalUser::update(pool, data.local_user_view.local_user.id, &local_user_form).await?;
-    data.local_user_view.local_user.show_bot_accounts = false;
+    LocalUser::update(
+      pool,
+      data.tegan_local_user_view.local_user.id,
+      &local_user_form,
+    )
+    .await?;
+    data.tegan_local_user_view.local_user.show_bot_accounts = false;
 
     let mut read_post_listing = PostQuery {
-      community_id: Some(data.inserted_community.id),
+      community_id: Some(data.community.id),
       ..data.default_post_query()
     }
     .list(&data.site, pool)
@@ -1178,7 +1210,7 @@ mod tests {
     assert_eq!(vec![expected_post_with_upvote], read_post_listing);
 
     let like_removed =
-      PostLike::remove(pool, data.local_user_view.person.id, data.inserted_post.id).await?;
+      PostLike::remove(pool, data.tegan_local_user_view.person.id, data.post.id).await?;
     assert_eq!(uplete::Count::only_deleted(1), like_removed);
     Ok(())
   }
@@ -1192,17 +1224,16 @@ mod tests {
 
     // Like both the bot post, and your own
     // The liked_only should not show your own post
-    let post_like_form =
-      PostLikeForm::new(data.inserted_post.id, data.local_user_view.person.id, 1);
+    let post_like_form = PostLikeForm::new(data.post.id, data.tegan_local_user_view.person.id, 1);
     PostLike::like(pool, &post_like_form).await?;
 
     let bot_post_like_form =
-      PostLikeForm::new(data.inserted_bot_post.id, data.local_user_view.person.id, 1);
+      PostLikeForm::new(data.bot_post.id, data.tegan_local_user_view.person.id, 1);
     PostLike::like(pool, &bot_post_like_form).await?;
 
     // Read the liked only
     let read_liked_post_listing = PostQuery {
-      community_id: Some(data.inserted_community.id),
+      community_id: Some(data.community.id),
       liked_only: Some(true),
       ..data.default_post_query()
     }
@@ -1213,7 +1244,7 @@ mod tests {
     assert_eq!(vec![POST_BY_BOT], names(&read_liked_post_listing));
 
     let read_disliked_post_listing = PostQuery {
-      community_id: Some(data.inserted_community.id),
+      community_id: Some(data.community.id),
       disliked_only: Some(true),
       ..data.default_post_query()
     }
@@ -1235,13 +1266,12 @@ mod tests {
 
     // Only mark the bot post as read
     // The read_only should only show the bot post
-    let post_read_form =
-      PostReadForm::new(data.inserted_bot_post.id, data.local_user_view.person.id);
+    let post_read_form = PostReadForm::new(data.bot_post.id, data.tegan_local_user_view.person.id);
     PostRead::mark_as_read(pool, &post_read_form).await?;
 
     // Only read the post marked as read
     let read_read_post_listing = PostQuery {
-      community_id: Some(data.inserted_community.id),
+      community_id: Some(data.community.id),
       read_only: Some(true),
       ..data.default_post_query()
     }
@@ -1260,33 +1290,128 @@ mod tests {
   async fn creator_info(data: &mut Data) -> LemmyResult<()> {
     let pool = &data.pool();
     let pool = &mut pool.into();
+    let community_id = data.community.id;
 
-    // Make one of the inserted persons a moderator
-    let person_id = data.local_user_view.person.id;
-    let community_id = data.inserted_community.id;
-    let form = CommunityModeratorForm {
+    let tegan_listings = PostQuery {
+      community_id: Some(community_id),
+      ..data.default_post_query()
+    }
+    .list(&data.site, pool)
+    .await?
+    .into_iter()
+    .map(|p| {
+      (
+        p.creator.name,
+        p.creator_is_moderator,
+        p.creator_is_admin,
+        p.can_mod,
+      )
+    })
+    .collect::<Vec<_>>();
+
+    // Tegan is an admin, so can_mod should be always true
+    let expected_post_listing = vec![
+      ("tegan".to_owned(), false, true, true),
+      ("mybot".to_owned(), false, false, true),
+      ("tegan".to_owned(), false, true, true),
+    ];
+    assert_eq!(expected_post_listing, tegan_listings);
+
+    // Have john become a moderator, then the bot
+    let john_mod_form = CommunityModeratorForm {
       community_id,
-      person_id,
+      person_id: data.john_local_user_view.person.id,
     };
-    CommunityModerator::join(pool, &form).await?;
+    CommunityModerator::join(pool, &john_mod_form).await?;
 
-    let post_listing = PostQuery {
-      community_id: Some(data.inserted_community.id),
-      ..data.default_post_query()
+    let bot_mod_form = CommunityModeratorForm {
+      community_id,
+      person_id: data.bot_local_user_view.person.id,
+    };
+    CommunityModerator::join(pool, &bot_mod_form).await?;
+
+    let john_listings = PostQuery {
+      sort: Some(PostSortType::New),
+      local_user: Some(&data.john_local_user_view.local_user),
+      ..Default::default()
     }
     .list(&data.site, pool)
     .await?
     .into_iter()
-    .map(|p| (p.creator.name, p.creator_is_moderator, p.creator_is_admin))
+    .map(|p| {
+      (
+        p.creator.name,
+        p.creator_is_moderator,
+        p.creator_is_admin,
+        p.can_mod,
+      )
+    })
     .collect::<Vec<_>>();
 
+    // John is a mod, so he can_mod the bots (and his own) posts, but not tegans.
     let expected_post_listing = vec![
-      ("tegan".to_owned(), true, true),
-      ("mybot".to_owned(), false, false),
-      ("tegan".to_owned(), true, true),
+      ("tegan".to_owned(), false, true, false),
+      ("mybot".to_owned(), true, false, true),
+      ("tegan".to_owned(), false, true, false),
+      ("john".to_owned(), true, false, true),
     ];
+    assert_eq!(expected_post_listing, john_listings);
+
+    // Bot is also a mod, but was added after john, so can't mod anything
+    let bot_listings = PostQuery {
+      sort: Some(PostSortType::New),
+      local_user: Some(&data.bot_local_user_view.local_user),
+      ..Default::default()
+    }
+    .list(&data.site, pool)
+    .await?
+    .into_iter()
+    .map(|p| {
+      (
+        p.creator.name,
+        p.creator_is_moderator,
+        p.creator_is_admin,
+        p.can_mod,
+      )
+    })
+    .collect::<Vec<_>>();
+
+    let expected_post_listing = vec![
+      ("tegan".to_owned(), false, true, false),
+      ("mybot".to_owned(), true, false, true),
+      ("tegan".to_owned(), false, true, false),
+      ("john".to_owned(), true, false, false),
+    ];
+    assert_eq!(expected_post_listing, bot_listings);
+
+    // Make the bot leave the mod team, and make sure it can_mod is false.
+    CommunityModerator::leave(pool, &bot_mod_form).await?;
+
+    let bot_listings = PostQuery {
+      sort: Some(PostSortType::New),
+      local_user: Some(&data.bot_local_user_view.local_user),
+      ..Default::default()
+    }
+    .list(&data.site, pool)
+    .await?
+    .into_iter()
+    .map(|p| {
+      (
+        p.creator.name,
+        p.creator_is_moderator,
+        p.creator_is_admin,
+        p.can_mod,
+      )
+    })
+    .collect::<Vec<_>>();
 
-    assert_eq!(expected_post_listing, post_listing);
+    let expected_post_listing = vec![
+      ("tegan".to_owned(), false, true, false),
+      ("mybot".to_owned(), false, false, false),
+      ("tegan".to_owned(), false, true, false),
+      ("john".to_owned(), true, false, false),
+    ];
+    assert_eq!(expected_post_listing, bot_listings);
 
     Ok(())
   }
@@ -1308,8 +1433,8 @@ mod tests {
       language_id: Some(spanish_id),
       ..PostInsertForm::new(
         EL_POSTO.to_string(),
-        data.local_user_view.person.id,
-        data.inserted_community.id,
+        data.tegan_local_user_view.person.id,
+        data.community.id,
       )
     };
     Post::create(pool, &post_spanish).await?;
@@ -1322,7 +1447,12 @@ mod tests {
       names(&post_listings_all)
     );
 
-    LocalUserLanguage::update(pool, vec![french_id], data.local_user_view.local_user.id).await?;
+    LocalUserLanguage::update(
+      pool,
+      vec![french_id],
+      data.tegan_local_user_view.local_user.id,
+    )
+    .await?;
 
     let post_listing_french = data.default_post_query().list(&data.site, pool).await?;
 
@@ -1339,7 +1469,7 @@ mod tests {
     LocalUserLanguage::update(
       pool,
       vec![french_id, UNDETERMINED_ID],
-      data.local_user_view.local_user.id,
+      data.tegan_local_user_view.local_user.id,
     )
     .await?;
     let post_listings_french_und = data
@@ -1371,7 +1501,7 @@ mod tests {
     // Remove the post
     Post::update(
       pool,
-      data.inserted_bot_post.id,
+      data.bot_post.id,
       &PostUpdateForm {
         removed: Some(true),
         ..Default::default()
@@ -1384,9 +1514,9 @@ mod tests {
     assert_eq!(vec![POST_WITH_TAGS, POST], names(&post_listings_no_admin));
 
     // Removed bot post is shown to admins on its profile page
-    data.local_user_view.local_user.admin = true;
+    data.tegan_local_user_view.local_user.admin = true;
     let post_listings_is_admin = PostQuery {
-      creator_id: Some(data.inserted_bot.id),
+      creator_id: Some(data.bot_local_user_view.person.id),
       ..data.default_post_query()
     }
     .list(&data.site, pool)
@@ -1406,7 +1536,7 @@ mod tests {
     // Delete the post
     Post::update(
       pool,
-      data.inserted_post.id,
+      data.post.id,
       &PostUpdateForm {
         deleted: Some(true),
         ..Default::default()
@@ -1417,8 +1547,8 @@ mod tests {
     // Deleted post is only shown to creator
     for (local_user, expect_contains_deleted) in [
       (None, false),
-      (Some(&data.blocked_local_user_view.local_user), false),
-      (Some(&data.local_user_view.local_user), true),
+      (Some(&data.john_local_user_view.local_user), false),
+      (Some(&data.tegan_local_user_view.local_user), true),
     ] {
       let contains_deleted = PostQuery {
         local_user,
@@ -1427,7 +1557,7 @@ mod tests {
       .list(&data.site, pool)
       .await?
       .iter()
-      .any(|p| p.post.id == data.inserted_post.id);
+      .any(|p| p.post.id == data.post.id);
 
       assert_eq!(expect_contains_deleted, contains_deleted);
     }
@@ -1444,7 +1574,7 @@ mod tests {
 
     Community::update(
       pool,
-      data.inserted_community.id,
+      data.community.id,
       &CommunityUpdateForm {
         hidden: Some(true),
         ..Default::default()
@@ -1461,7 +1591,7 @@ mod tests {
     // Follow the community
     let form = CommunityFollowerForm {
       state: Some(CommunityFollowerState::Accepted),
-      ..CommunityFollowerForm::new(data.inserted_community.id, data.local_user_view.person.id)
+      ..CommunityFollowerForm::new(data.community.id, data.tegan_local_user_view.person.id)
     };
     CommunityFollower::follow(pool, &form).await?;
 
@@ -1494,7 +1624,7 @@ mod tests {
       language_id: Some(LanguageId(1)),
       ..PostInsertForm::new(
         POST_FROM_BLOCKED_INSTANCE.to_string(),
-        data.inserted_bot.id,
+        data.bot_local_user_view.person.id,
         inserted_community.id,
       )
     };
@@ -1514,7 +1644,7 @@ mod tests {
 
     // block the instance
     let block_form = InstanceBlockForm {
-      person_id: data.local_user_view.person.id,
+      person_id: data.tegan_local_user_view.person.id,
       instance_id: blocked_instance.id,
     };
     InstanceBlock::block(pool, &block_form).await?;
@@ -1554,7 +1684,7 @@ mod tests {
     let pool = &mut pool.into();
 
     let community_form = CommunityInsertForm::new(
-      data.inserted_instance.id,
+      data.instance.id,
       "yes".to_string(),
       "yes".to_owned(),
       "pubkey".to_string(),
@@ -1574,7 +1704,7 @@ mod tests {
           published: Some(Utc::now() - Duration::from_secs(comments % 3)),
           ..PostInsertForm::new(
             "keep Christ in Christmas".to_owned(),
-            data.local_user_view.person.id,
+            data.tegan_local_user_view.person.id,
             inserted_community.id,
           )
         };
@@ -1583,7 +1713,7 @@ mod tests {
 
         for _ in 0..comments {
           let comment_form = CommentInsertForm::new(
-            data.local_user_view.person.id,
+            data.tegan_local_user_view.person.id,
             inserted_post.id,
             "yes".to_owned(),
           );
@@ -1674,11 +1804,16 @@ mod tests {
       show_read_posts: Some(false),
       ..Default::default()
     };
-    LocalUser::update(pool, data.local_user_view.local_user.id, &local_user_form).await?;
-    data.local_user_view.local_user.show_read_posts = false;
+    LocalUser::update(
+      pool,
+      data.tegan_local_user_view.local_user.id,
+      &local_user_form,
+    )
+    .await?;
+    data.tegan_local_user_view.local_user.show_read_posts = false;
 
     // Mark a post as read
-    let read_form = PostReadForm::new(data.inserted_bot_post.id, data.local_user_view.person.id);
+    let read_form = PostReadForm::new(data.bot_post.id, data.tegan_local_user_view.person.id);
     PostRead::mark_as_read(pool, &read_form).await?;
 
     // Make sure you don't see the read post in the results
@@ -1719,12 +1854,7 @@ mod tests {
     let pool = &mut pool.into();
 
     // Mark a post as hidden
-    PostHide::hide(
-      pool,
-      data.inserted_bot_post.id,
-      data.local_user_view.person.id,
-    )
-    .await?;
+    PostHide::hide(pool, data.bot_post.id, data.tegan_local_user_view.person.id).await?;
 
     // Make sure you don't see the hidden post in the results
     let post_listings_hide_hidden = data.default_post_query().list(&data.site, pool).await?;
@@ -1736,7 +1866,7 @@ mod tests {
     // Make sure it does come back with the show_hidden option
     let post_listings_show_hidden = PostQuery {
       sort: Some(PostSortType::New),
-      local_user: Some(&data.local_user_view.local_user),
+      local_user: Some(&data.tegan_local_user_view.local_user),
       show_hidden: Some(true),
       ..Default::default()
     }
@@ -1766,7 +1896,7 @@ mod tests {
       ..Default::default()
     };
 
-    Post::update(pool, data.inserted_post_with_tags.id, &update_form).await?;
+    Post::update(pool, data.post_with_tags.id, &update_form).await?;
 
     // Make sure you don't see the nsfw post in the regular results
     let post_listings_hide_nsfw = data.default_post_query().list(&data.site, pool).await?;
@@ -1776,7 +1906,7 @@ mod tests {
     let post_listings_show_nsfw = PostQuery {
       sort: Some(PostSortType::New),
       show_nsfw: Some(true),
-      local_user: Some(&data.local_user_view.local_user),
+      local_user: Some(&data.tegan_local_user_view.local_user),
       ..Default::default()
     }
     .list(&data.site, pool)
@@ -1800,9 +1930,9 @@ mod tests {
 
   async fn expected_post_view(data: &Data, pool: &mut DbPool<'_>) -> LemmyResult<PostView> {
     let (inserted_person, inserted_community, inserted_post) = (
-      &data.local_user_view.person,
-      &data.inserted_community,
-      &data.inserted_post,
+      &data.tegan_local_user_view.person,
+      &data.community,
+      &data.post,
     );
     let agg = PostAggregates::read(pool, inserted_post.id).await?;
 
@@ -1852,7 +1982,7 @@ mod tests {
         inbox_url: inserted_person.inbox_url.clone(),
         matrix_user_id: None,
         ban_expires: None,
-        instance_id: data.inserted_instance.id,
+        instance_id: data.instance.id,
         private_key: inserted_person.private_key.clone(),
         public_key: inserted_person.public_key.clone(),
         last_refreshed_at: inserted_person.last_refreshed_at,
@@ -1862,6 +1992,7 @@ mod tests {
       banned_from_community: false,
       creator_is_moderator: false,
       creator_is_admin: true,
+      can_mod: true,
       community: Community {
         id: inserted_community.id,
         name: inserted_community.name.clone(),
@@ -1879,7 +2010,7 @@ mod tests {
         hidden: false,
         posting_restricted_to_mods: false,
         published: inserted_community.published,
-        instance_id: data.inserted_instance.id,
+        instance_id: data.instance.id,
         private_key: inserted_community.private_key.clone(),
         public_key: inserted_community.public_key.clone(),
         last_refreshed_at: inserted_community.last_refreshed_at,
@@ -1907,7 +2038,7 @@ mod tests {
         scaled_rank: RANK_DEFAULT,
         community_id: inserted_post.community_id,
         creator_id: inserted_post.creator_id,
-        instance_id: data.inserted_instance.id,
+        instance_id: data.instance.id,
         report_count: 0,
         unresolved_report_count: 0,
       },
@@ -1929,7 +2060,7 @@ mod tests {
 
     Community::update(
       pool,
-      data.inserted_community.id,
+      data.community.id,
       &CommunityUpdateForm {
         visibility: Some(CommunityVisibility::LocalOnly),
         ..Default::default()
@@ -1945,20 +2076,20 @@ mod tests {
     assert_eq!(0, unauthenticated_query.len());
 
     let authenticated_query = PostQuery {
-      local_user: Some(&data.local_user_view.local_user),
+      local_user: Some(&data.tegan_local_user_view.local_user),
       ..Default::default()
     }
     .list(&data.site, pool)
     .await?;
     assert_eq!(3, authenticated_query.len());
 
-    let unauthenticated_post = PostView::read(pool, data.inserted_post.id, None, false).await;
+    let unauthenticated_post = PostView::read(pool, data.post.id, None, false).await;
     assert!(unauthenticated_post.is_err());
 
     let authenticated_post = PostView::read(
       pool,
-      data.inserted_post.id,
-      Some(&data.local_user_view.local_user),
+      data.post.id,
+      Some(&data.tegan_local_user_view.local_user),
       false,
     )
     .await;
@@ -1975,7 +2106,7 @@ mod tests {
     let pool = &mut pool.into();
 
     // Test that post view shows if local user is blocked from community
-    let banned_from_comm_person = PersonInsertForm::test_form(data.inserted_instance.id, "jill");
+    let banned_from_comm_person = PersonInsertForm::test_form(data.instance.id, "jill");
 
     let inserted_banned_from_comm_person = Person::create(pool, &banned_from_comm_person).await?;
 
@@ -1989,7 +2120,7 @@ mod tests {
     CommunityPersonBan::ban(
       pool,
       &CommunityPersonBanForm {
-        community_id: data.inserted_community.id,
+        community_id: data.community.id,
         person_id: inserted_banned_from_comm_person.id,
         expires: None,
       },
@@ -1998,7 +2129,7 @@ mod tests {
 
     let post_view = PostView::read(
       pool,
-      data.inserted_post.id,
+      data.post.id,
       Some(&inserted_banned_from_comm_local_user),
       false,
     )
@@ -2019,8 +2150,8 @@ mod tests {
 
     let post_view = PostView::read(
       pool,
-      data.inserted_post.id,
-      Some(&data.local_user_view.local_user),
+      data.post.id,
+      Some(&data.tegan_local_user_view.local_user),
       false,
     )
     .await?;
@@ -2050,8 +2181,8 @@ mod tests {
         url,
         ..PostInsertForm::new(
           name,
-          data.local_user_view.person.id,
-          data.inserted_community.id,
+          data.tegan_local_user_view.person.id,
+          data.community.id,
         )
       };
       Post::create(pool, &post_form).await?;
@@ -2067,7 +2198,7 @@ mod tests {
     let now = Instant::now();
     PostQuery {
       sort: Some(PostSortType::Active),
-      local_user: Some(&data.local_user_view.local_user),
+      local_user: Some(&data.tegan_local_user_view.local_user),
       ..Default::default()
     }
     .list(&data.site, pool)
@@ -2095,8 +2226,8 @@ mod tests {
 
     // Create a comment for a post
     let comment_form = CommentInsertForm::new(
-      data.local_user_view.person.id,
-      data.inserted_post.id,
+      data.tegan_local_user_view.person.id,
+      data.post.id,
       "a comment".to_owned(),
     );
     Comment::create(pool, &comment_form, None).await?;
@@ -2105,7 +2236,7 @@ mod tests {
     let post_listings_no_comments = PostQuery {
       sort: Some(PostSortType::New),
       no_comments_only: Some(true),
-      local_user: Some(&data.local_user_view.local_user),
+      local_user: Some(&data.tegan_local_user_view.local_user),
       ..Default::default()
     }
     .list(&data.site, pool)
@@ -2129,7 +2260,7 @@ mod tests {
     // Mark community as private
     Community::update(
       pool,
-      data.inserted_community.id,
+      data.community.id,
       &CommunityUpdateForm {
         visibility: Some(CommunityVisibility::Private),
         ..Default::default()
@@ -2139,20 +2270,20 @@ mod tests {
 
     // No posts returned without auth
     let read_post_listing = PostQuery {
-      community_id: Some(data.inserted_community.id),
+      community_id: Some(data.community.id),
       ..Default::default()
     }
     .list(&data.site, pool)
     .await?;
     assert_eq!(0, read_post_listing.len());
-    let post_view = PostView::read(pool, data.inserted_post.id, None, false).await;
+    let post_view = PostView::read(pool, data.post.id, None, false).await;
     assert!(post_view.is_err());
 
     // No posts returned for non-follower who is not admin
-    data.local_user_view.local_user.admin = false;
+    data.tegan_local_user_view.local_user.admin = false;
     let read_post_listing = PostQuery {
-      community_id: Some(data.inserted_community.id),
-      local_user: Some(&data.local_user_view.local_user),
+      community_id: Some(data.community.id),
+      local_user: Some(&data.tegan_local_user_view.local_user),
       ..Default::default()
     }
     .list(&data.site, pool)
@@ -2160,18 +2291,18 @@ mod tests {
     assert_eq!(0, read_post_listing.len());
     let post_view = PostView::read(
       pool,
-      data.inserted_post.id,
-      Some(&data.local_user_view.local_user),
+      data.post.id,
+      Some(&data.tegan_local_user_view.local_user),
       false,
     )
     .await;
     assert!(post_view.is_err());
 
     // Admin can view content without following
-    data.local_user_view.local_user.admin = true;
+    data.tegan_local_user_view.local_user.admin = true;
     let read_post_listing = PostQuery {
-      community_id: Some(data.inserted_community.id),
-      local_user: Some(&data.local_user_view.local_user),
+      community_id: Some(data.community.id),
+      local_user: Some(&data.tegan_local_user_view.local_user),
       ..Default::default()
     }
     .list(&data.site, pool)
@@ -2179,26 +2310,26 @@ mod tests {
     assert_eq!(3, read_post_listing.len());
     let post_view = PostView::read(
       pool,
-      data.inserted_post.id,
-      Some(&data.local_user_view.local_user),
+      data.post.id,
+      Some(&data.tegan_local_user_view.local_user),
       true,
     )
     .await;
     assert!(post_view.is_ok());
-    data.local_user_view.local_user.admin = false;
+    data.tegan_local_user_view.local_user.admin = false;
 
     // User can view after following
     CommunityFollower::follow(
       pool,
       &CommunityFollowerForm {
         state: Some(CommunityFollowerState::Accepted),
-        ..CommunityFollowerForm::new(data.inserted_community.id, data.local_user_view.person.id)
+        ..CommunityFollowerForm::new(data.community.id, data.tegan_local_user_view.person.id)
       },
     )
     .await?;
     let read_post_listing = PostQuery {
-      community_id: Some(data.inserted_community.id),
-      local_user: Some(&data.local_user_view.local_user),
+      community_id: Some(data.community.id),
+      local_user: Some(&data.tegan_local_user_view.local_user),
       ..Default::default()
     }
     .list(&data.site, pool)
@@ -2206,8 +2337,8 @@ mod tests {
     assert_eq!(3, read_post_listing.len());
     let post_view = PostView::read(
       pool,
-      data.inserted_post.id,
-      Some(&data.local_user_view.local_user),
+      data.post.id,
+      Some(&data.tegan_local_user_view.local_user),
       true,
     )
     .await;
@@ -2226,7 +2357,7 @@ mod tests {
     // Make one post an image post
     Post::update(
       pool,
-      data.inserted_bot_post.id,
+      data.bot_post.id,
       &PostUpdateForm {
         url_content_type: Some(Some(String::from("image/png"))),
         ..Default::default()
@@ -2236,8 +2367,8 @@ mod tests {
 
     // Make sure all the posts are returned when `hide_media` is unset
     let hide_media_listing = PostQuery {
-      community_id: Some(data.inserted_community.id),
-      local_user: Some(&data.local_user_view.local_user),
+      community_id: Some(data.community.id),
+      local_user: Some(&data.tegan_local_user_view.local_user),
       ..Default::default()
     }
     .list(&data.site, pool)
@@ -2249,13 +2380,18 @@ mod tests {
       hide_media: Some(true),
       ..Default::default()
     };
-    LocalUser::update(pool, data.local_user_view.local_user.id, &local_user_form).await?;
-    data.local_user_view.local_user.hide_media = true;
+    LocalUser::update(
+      pool,
+      data.tegan_local_user_view.local_user.id,
+      &local_user_form,
+    )
+    .await?;
+    data.tegan_local_user_view.local_user.hide_media = true;
 
     // Ensure you don't see the image post
     let hide_media_listing = PostQuery {
-      community_id: Some(data.inserted_community.id),
-      local_user: Some(&data.local_user_view.local_user),
+      community_id: Some(data.community.id),
+      local_user: Some(&data.tegan_local_user_view.local_user),
       ..Default::default()
     }
     .list(&data.site, pool)
@@ -2264,8 +2400,8 @@ mod tests {
 
     // Make sure the `hide_media` override works
     let hide_media_listing = PostQuery {
-      community_id: Some(data.inserted_community.id),
-      local_user: Some(&data.local_user_view.local_user),
+      community_id: Some(data.community.id),
+      local_user: Some(&data.tegan_local_user_view.local_user),
       hide_media: Some(false),
       ..Default::default()
     }
@@ -2285,8 +2421,8 @@ mod tests {
 
     let post_view = PostView::read(
       pool,
-      data.inserted_post_with_tags.id,
-      Some(&data.local_user_view.local_user),
+      data.post_with_tags.id,
+      Some(&data.tegan_local_user_view.local_user),
       false,
     )
     .await?;
diff --git a/crates/db_views/src/reports/comment_report_view.rs b/crates/db_views/src/reports/comment_report_view.rs
index 2ca059147c..7bc57d073d 100644
--- a/crates/db_views/src/reports/comment_report_view.rs
+++ b/crates/db_views/src/reports/comment_report_view.rs
@@ -11,6 +11,7 @@ use diesel::{
 use diesel_async::RunQueryDsl;
 use lemmy_db_schema::{
   aliases::{self, creator_community_actions},
+  impls::community::community_follower_select_subscribed_type,
   newtypes::{CommentReportId, PersonId},
   schema::{
     comment,
@@ -24,7 +25,6 @@ use lemmy_db_schema::{
     person_actions,
     post,
   },
-  source::community::CommunityFollower,
   utils::{functions::coalesce, get_conn, DbPool},
 };
 
@@ -135,7 +135,7 @@ impl CommentReportView {
           .is_not_null(),
         local_user::admin.nullable().is_not_null(),
         person_actions::blocked.nullable().is_not_null(),
-        CommunityFollower::select_subscribed_type(),
+        community_follower_select_subscribed_type(),
         comment_actions::saved.nullable(),
         comment_actions::like_score.nullable(),
         aliases::person2.fields(person::all_columns).nullable(),
diff --git a/crates/db_views/src/reports/post_report_view.rs b/crates/db_views/src/reports/post_report_view.rs
index 5f246be9d1..25a8f90e39 100644
--- a/crates/db_views/src/reports/post_report_view.rs
+++ b/crates/db_views/src/reports/post_report_view.rs
@@ -10,6 +10,7 @@ use diesel::{
 use diesel_async::RunQueryDsl;
 use lemmy_db_schema::{
   aliases::{self, creator_community_actions},
+  impls::community::community_follower_select_subscribed_type,
   newtypes::{PersonId, PostReportId},
   schema::{
     community,
@@ -22,7 +23,6 @@ use lemmy_db_schema::{
     post_aggregates,
     post_report,
   },
-  source::community::CommunityFollower,
   utils::{functions::coalesce, get_conn, DbPool},
 };
 
@@ -119,7 +119,7 @@ impl PostReportView {
           .nullable()
           .is_not_null(),
         local_user::admin.nullable().is_not_null(),
-        CommunityFollower::select_subscribed_type(),
+        community_follower_select_subscribed_type(),
         post_actions::saved.nullable(),
         post_actions::read.nullable().is_not_null(),
         post_actions::hidden.nullable().is_not_null(),
diff --git a/crates/db_views/src/structs.rs b/crates/db_views/src/structs.rs
index 23996a8d12..5f3a39adbe 100644
--- a/crates/db_views/src/structs.rs
+++ b/crates/db_views/src/structs.rs
@@ -3,12 +3,14 @@ use chrono::{DateTime, Utc};
 use diesel::{
   deserialize::FromSqlRow,
   dsl::exists,
+
   dsl::Nullable,
   expression::AsExpression,
   sql_types,
   BoolExpressionMethods,
   ExpressionMethods,
   NullableExpressionMethods,
+  PgExpressionMethods,
   QueryDsl,
   Queryable,
   Selectable,
@@ -73,9 +75,11 @@ use lemmy_db_schema::{
 };
 #[cfg(feature = "full")]
 use lemmy_db_schema::{
-  aliases::{creator_community_actions, person1},
+  aliases::{creator_community_actions, creator_local_user, person1},
+  impls::comment::comment_select_remove_deletes,
+  impls::community::community_follower_select_subscribed_type,
+  impls::local_user::local_user_can_mod,
   schema::{comment, comment_actions, community_actions, local_user, person, person_actions},
-  source::community::CommunityFollower,
   utils::functions::coalesce,
   Person1AliasAllColumnsTuple,
 };
@@ -119,7 +123,11 @@ pub struct CommentReportView {
 #[cfg_attr(feature = "full", ts(export))]
 /// A comment view.
 pub struct CommentView {
-  #[cfg_attr(feature = "full", diesel(embed))]
+  #[cfg_attr(feature = "full",
+    diesel(
+      select_expression = comment_select_remove_deletes()
+    )
+  )]
   pub comment: Comment,
   #[cfg_attr(feature = "full", diesel(embed))]
   pub creator: Person,
@@ -159,21 +167,17 @@ pub struct CommentView {
   #[cfg_attr(feature = "full",
     diesel(
       select_expression =
-        exists(
-          local_user::table.filter(
-            comment::creator_id
-              .eq(local_user::person_id)
-              .and(local_user::admin.eq(true))
-          )
-        )
+        exists(creator_local_user.filter(
+          comment::creator_id
+            .eq(creator_local_user.field(local_user::person_id))
+            .and(creator_local_user.field(local_user::admin).eq(true)),
+        ))
     )
   )]
   pub creator_is_admin: bool,
   #[cfg_attr(feature = "full",
     diesel(
-      select_expression_type = Nullable<community_actions::follow_state>,
-      select_expression =
-        CommunityFollower::select_subscribed_type(),
+      select_expression = community_follower_select_subscribed_type(),
     )
   )]
   pub subscribed: SubscribedType,
@@ -201,6 +205,12 @@ pub struct CommentView {
     )
   )]
   pub my_vote: Option<i16>,
+  #[cfg_attr(feature = "full",
+    diesel(
+      select_expression = local_user_can_mod()
+    )
+  )]
+  pub can_mod: bool,
 }
 
 #[skip_serializing_none]
@@ -224,6 +234,7 @@ pub struct CommentSlimView {
   pub creator_blocked: bool,
   #[cfg_attr(feature = "full", ts(optional))]
   pub my_vote: Option<i16>,
+  pub can_mod: bool,
 }
 
 #[skip_serializing_none]
@@ -343,6 +354,7 @@ pub struct PostView {
   pub my_vote: Option<i16>,
   pub unread_comments: i64,
   pub tags: PostTags,
+  pub can_mod: bool,
 }
 
 #[skip_serializing_none]
@@ -513,6 +525,7 @@ pub(crate) struct PersonContentCombinedViewInternal {
   pub item_creator_banned_from_community: bool,
   pub item_creator_blocked: bool,
   pub banned_from_community: bool,
+  pub can_mod: bool,
 }
 
 #[derive(Debug, PartialEq, Serialize, Deserialize, Clone)]
@@ -570,8 +583,7 @@ pub struct CommunityView {
   pub community: Community,
   #[cfg_attr(feature = "full",
     diesel(
-      select_expression_type = Nullable<community_actions::follow_state>,
-      select_expression = CommunityFollower::select_subscribed_type()
+      select_expression = community_follower_select_subscribed_type()
     )
   )]
   pub subscribed: SubscribedType,
@@ -589,6 +601,14 @@ pub struct CommunityView {
     )
   )]
   pub banned_from_community: bool,
+  #[cfg_attr(feature = "full",
+    diesel(
+      select_expression = local_user::admin.nullable()
+        .or(community_actions::became_moderator.nullable().is_not_null())
+        .is_not_distinct_from(true)
+    )
+  )]
+  pub can_mod: bool,
 }
 
 /// The community sort types. See here for descriptions: https://join-lemmy.org/docs/en/users/03-votes-and-ranking.html
@@ -637,6 +657,7 @@ pub struct PersonCommentMentionView {
   pub creator_blocked: bool,
   #[cfg_attr(feature = "full", ts(optional))]
   pub my_vote: Option<i16>,
+  pub can_mod: bool,
 }
 
 #[skip_serializing_none]
@@ -669,6 +690,7 @@ pub struct PersonPostMentionView {
   pub my_vote: Option<i16>,
   pub unread_comments: i64,
   pub post_tags: PostTags,
+  pub can_mod: bool,
 }
 
 #[skip_serializing_none]
@@ -696,6 +718,7 @@ pub struct CommentReplyView {
   pub creator_blocked: bool,
   #[cfg_attr(feature = "full", ts(optional))]
   pub my_vote: Option<i16>,
+  pub can_mod: bool,
 }
 
 #[derive(Debug, PartialEq, Serialize, Deserialize, Clone)]
@@ -789,6 +812,7 @@ pub struct InboxCombinedViewInternal {
   pub item_creator_banned_from_community: bool,
   pub item_creator_blocked: bool,
   pub banned_from_community: bool,
+  pub can_mod: bool,
 }
 
 #[derive(Debug, PartialEq, Serialize, Deserialize, Clone)]
@@ -1167,6 +1191,7 @@ pub(crate) struct SearchCombinedViewInternal {
   pub item_creator_banned_from_community: bool,
   pub item_creator_blocked: bool,
   pub banned_from_community: bool,
+  pub can_mod: bool,
 }
 
 #[derive(Debug, PartialEq, Serialize, Deserialize, Clone)]


Test patch: