1 use crate::structs::{CommentView, LocalUserView};
8 NullableExpressionMethods,
9 PgTextExpressionMethods,
12 use diesel_async::RunQueryDsl;
13 use diesel_ltree::{nlevel, subpath, Ltree, LtreeExtensions};
14 use lemmy_db_schema::{
15 aggregates::structs::CommentAggregates,
16 newtypes::{CommentId, CommunityId, LocalUserId, PersonId, PostId},
32 comment::{Comment, CommentSaved},
33 community::{Community, CommunityFollower, CommunityPersonBan},
35 person_block::PersonBlock,
39 utils::{fuzzy_search, limit_and_offset, DbConn, DbPool, ListFn, Queries, ReadFn},
44 type CommentViewTuple = (
50 Option<CommunityPersonBan>,
51 Option<CommunityFollower>,
57 fn queries<'a>() -> Queries<
58 impl ReadFn<'a, CommentView, (CommentId, Option<PersonId>)>,
59 impl ListFn<'a, CommentView, CommentQuery<'a>>,
61 let all_joins = |query: comment::BoxedQuery<'a, Pg>, my_person_id: Option<PersonId>| {
62 // The left join below will return None in this case
63 let person_id_join = my_person_id.unwrap_or(PersonId(-1));
65 .inner_join(person::table)
66 .inner_join(post::table)
67 .inner_join(community::table.on(post::community_id.eq(community::id)))
68 .inner_join(comment_aggregates::table)
70 community_person_ban::table.on(
72 .eq(community_person_ban::community_id)
73 .and(community_person_ban::person_id.eq(comment::creator_id)),
77 community_follower::table.on(
79 .eq(community_follower::community_id)
80 .and(community_follower::person_id.eq(person_id_join)),
84 comment_saved::table.on(
86 .eq(comment_saved::comment_id)
87 .and(comment_saved::person_id.eq(person_id_join)),
91 person_block::table.on(
93 .eq(person_block::target_id)
94 .and(person_block::person_id.eq(person_id_join)),
98 comment_like::table.on(
100 .eq(comment_like::comment_id)
101 .and(comment_like::person_id.eq(person_id_join)),
107 comment::all_columns,
110 community::all_columns,
111 comment_aggregates::all_columns,
112 community_person_ban::all_columns.nullable(),
113 community_follower::all_columns.nullable(),
114 comment_saved::all_columns.nullable(),
115 person_block::all_columns.nullable(),
116 comment_like::score.nullable(),
119 let read = move |mut conn: DbConn<'a>,
120 (comment_id, my_person_id): (CommentId, Option<PersonId>)| async move {
121 all_joins(comment::table.find(comment_id).into_boxed(), my_person_id)
123 .first::<CommentViewTuple>(&mut conn)
127 let list = move |mut conn: DbConn<'a>, options: CommentQuery<'a>| async move {
128 let person_id = options.local_user.map(|l| l.person.id);
129 let local_user_id = options.local_user.map(|l| l.local_user.id);
131 // The left join below will return None in this case
132 let person_id_join = person_id.unwrap_or(PersonId(-1));
133 let local_user_id_join = local_user_id.unwrap_or(LocalUserId(-1));
135 let mut query = all_joins(comment::table.into_boxed(), person_id)
137 community_block::table.on(
139 .eq(community_block::community_id)
140 .and(community_block::person_id.eq(person_id_join)),
144 local_user_language::table.on(
146 .eq(local_user_language::language_id)
147 .and(local_user_language::local_user_id.eq(local_user_id_join)),
152 if let Some(creator_id) = options.creator_id {
153 query = query.filter(comment::creator_id.eq(creator_id));
156 if let Some(post_id) = options.post_id {
157 query = query.filter(comment::post_id.eq(post_id));
160 if let Some(parent_path) = options.parent_path.as_ref() {
161 query = query.filter(comment::path.contained_by(parent_path));
164 if let Some(search_term) = options.search_term {
165 query = query.filter(comment::content.ilike(fuzzy_search(&search_term)));
168 if let Some(community_id) = options.community_id {
169 query = query.filter(post::community_id.eq(community_id));
172 if let Some(listing_type) = options.listing_type {
174 ListingType::Subscribed => {
175 query = query.filter(community_follower::person_id.is_not_null())
176 } // TODO could be this: and(community_follower::person_id.eq(person_id_join)),
177 ListingType::Local => {
178 query = query.filter(community::local.eq(true)).filter(
181 .or(community_follower::person_id.eq(person_id_join)),
184 ListingType::All => {
185 query = query.filter(
188 .or(community_follower::person_id.eq(person_id_join)),
194 if options.saved_only.unwrap_or(false) {
195 query = query.filter(comment_saved::comment_id.is_not_null());
198 if options.liked_only.unwrap_or_default() {
199 query = query.filter(comment_like::score.eq(1));
200 } else if options.disliked_only.unwrap_or_default() {
201 query = query.filter(comment_like::score.eq(-1));
204 let is_creator = options.creator_id == options.local_user.map(|l| l.person.id);
205 // only show deleted comments to creator
207 query = query.filter(comment::deleted.eq(false));
210 let is_admin = options.local_user.map(|l| l.person.admin).unwrap_or(false);
211 // only show removed comments to admin when viewing user profile
212 if !(options.is_profile_view && is_admin) {
213 query = query.filter(comment::removed.eq(false));
218 .map(|l| l.local_user.show_bot_accounts)
221 query = query.filter(person::bot_account.eq(false));
224 if options.local_user.is_some() {
225 // Filter out the rows with missing languages
226 query = query.filter(local_user_language::language_id.is_not_null());
228 // Don't show blocked communities or persons
229 if options.post_id.is_none() {
230 query = query.filter(community_block::person_id.is_null());
232 query = query.filter(person_block::person_id.is_null());
235 // A Max depth given means its a tree fetch
236 let (limit, offset) = if let Some(max_depth) = options.max_depth {
237 let depth_limit = if let Some(parent_path) = options.parent_path.as_ref() {
238 parent_path.0.split('.').count() as i32 + max_depth
239 // Add one because of root "0"
244 query = query.filter(nlevel(comment::path).le(depth_limit));
246 // only order if filtering by a post id, or parent_path. DOS potential otherwise and max_depth + !post_id isn't used anyways (afaik)
247 if options.post_id.is_some() || options.parent_path.is_some() {
248 // Always order by the parent path first
249 query = query.order_by(subpath(comment::path, 0, -1));
252 // TODO limit question. Limiting does not work for comment threads ATM, only max_depth
253 // For now, don't do any limiting for tree fetches
254 // https://stackoverflow.com/questions/72983614/postgres-ltree-how-to-limit-the-max-number-of-children-at-any-given-level
256 // Don't use the regular error-checking one, many more comments must ofter be fetched.
257 // This does not work for comment trees, and the limit should be manually set to a high number
259 // If a max depth is given, then you know its a tree fetch, and limits should be ignored
260 // TODO a kludge to prevent attacks. Limit comments to 300 for now.
264 // limit_and_offset_unlimited(options.page, options.limit)
265 limit_and_offset(options.page, options.limit)?
268 query = match options.sort.unwrap_or(CommentSortType::Hot) {
269 CommentSortType::Hot => query
270 .then_order_by(comment_aggregates::hot_rank.desc())
271 .then_order_by(comment_aggregates::score.desc()),
272 CommentSortType::Controversial => {
273 query.then_order_by(comment_aggregates::controversy_rank.desc())
275 CommentSortType::New => query.then_order_by(comment::published.desc()),
276 CommentSortType::Old => query.then_order_by(comment::published.asc()),
277 CommentSortType::Top => query.order_by(comment_aggregates::score.desc()),
280 // Note: deleted and removed comments are done on the front side
284 .load::<CommentViewTuple>(&mut conn)
288 Queries::new(read, list)
293 pool: &mut DbPool<'_>,
294 comment_id: CommentId,
295 my_person_id: Option<PersonId>,
296 ) -> Result<Self, Error> {
297 // If a person is given, then my_vote (res.9), if None, should be 0, not null
298 // Necessary to differentiate between other person's votes
299 let mut res = queries().read(pool, (comment_id, my_person_id)).await?;
300 if my_person_id.is_some() && res.my_vote.is_none() {
301 res.my_vote = Some(0);
308 pub struct CommentQuery<'a> {
309 pub listing_type: Option<ListingType>,
310 pub sort: Option<CommentSortType>,
311 pub community_id: Option<CommunityId>,
312 pub post_id: Option<PostId>,
313 pub parent_path: Option<Ltree>,
314 pub creator_id: Option<PersonId>,
315 pub local_user: Option<&'a LocalUserView>,
316 pub search_term: Option<String>,
317 pub saved_only: Option<bool>,
318 pub liked_only: Option<bool>,
319 pub disliked_only: Option<bool>,
320 pub is_profile_view: bool,
321 pub page: Option<i64>,
322 pub limit: Option<i64>,
323 pub max_depth: Option<i32>,
326 impl<'a> CommentQuery<'a> {
327 pub async fn list(self, pool: &mut DbPool<'_>) -> Result<Vec<CommentView>, Error> {
328 queries().list(pool, self).await
332 impl JoinView for CommentView {
333 type JoinTuple = CommentViewTuple;
334 fn from_tuple(a: Self::JoinTuple) -> Self {
341 creator_banned_from_community: a.5.is_some(),
342 subscribed: CommunityFollower::to_subscribed_type(&a.6),
343 saved: a.7.is_some(),
344 creator_blocked: a.8.is_some(),
352 #![allow(clippy::unwrap_used)]
353 #![allow(clippy::indexing_slicing)]
367 structs::LocalUserView,
369 use lemmy_db_schema::{
370 aggregates::structs::CommentAggregates,
371 impls::actor_language::UNDETERMINED_ID,
372 newtypes::LanguageId,
374 actor_language::LocalUserLanguage,
375 comment::{CommentInsertForm, CommentLike, CommentLikeForm},
376 community::CommunityInsertForm,
379 local_user::{LocalUser, LocalUserInsertForm},
380 person::PersonInsertForm,
381 person_block::PersonBlockForm,
382 post::PostInsertForm,
384 traits::{Blockable, Crud, Likeable},
385 utils::build_db_pool_for_tests,
388 use serial_test::serial;
391 inserted_instance: Instance,
392 inserted_comment_0: Comment,
393 inserted_comment_1: Comment,
394 inserted_comment_2: Comment,
396 local_user_view: LocalUserView,
397 inserted_person_2: Person,
398 inserted_community: Community,
401 async fn init_data(pool: &mut DbPool<'_>) -> Data {
402 let inserted_instance = Instance::read_or_create(pool, "my_domain.tld".to_string())
406 let new_person = PersonInsertForm::builder()
407 .name("timmy".into())
408 .public_key("pubkey".to_string())
409 .instance_id(inserted_instance.id)
411 let inserted_person = Person::create(pool, &new_person).await.unwrap();
412 let local_user_form = LocalUserInsertForm::builder()
413 .person_id(inserted_person.id)
414 .password_encrypted(String::new())
416 let inserted_local_user = LocalUser::create(pool, &local_user_form).await.unwrap();
418 let new_person_2 = PersonInsertForm::builder()
420 .public_key("pubkey".to_string())
421 .instance_id(inserted_instance.id)
423 let inserted_person_2 = Person::create(pool, &new_person_2).await.unwrap();
425 let new_community = CommunityInsertForm::builder()
426 .name("test community 5".to_string())
427 .title("nada".to_owned())
428 .public_key("pubkey".to_string())
429 .instance_id(inserted_instance.id)
432 let inserted_community = Community::create(pool, &new_community).await.unwrap();
434 let new_post = PostInsertForm::builder()
435 .name("A test post 2".into())
436 .creator_id(inserted_person.id)
437 .community_id(inserted_community.id)
440 let inserted_post = Post::create(pool, &new_post).await.unwrap();
441 let english_id = Language::read_id_from_code(pool, Some("en")).await.unwrap();
443 // Create a comment tree with this hierarchy
451 let comment_form_0 = CommentInsertForm::builder()
452 .content("Comment 0".into())
453 .creator_id(inserted_person.id)
454 .post_id(inserted_post.id)
455 .language_id(english_id)
458 let inserted_comment_0 = Comment::create(pool, &comment_form_0, None).await.unwrap();
460 let comment_form_1 = CommentInsertForm::builder()
461 .content("Comment 1, A test blocked comment".into())
462 .creator_id(inserted_person_2.id)
463 .post_id(inserted_post.id)
464 .language_id(english_id)
467 let inserted_comment_1 = Comment::create(pool, &comment_form_1, Some(&inserted_comment_0.path))
471 let finnish_id = Language::read_id_from_code(pool, Some("fi")).await.unwrap();
472 let comment_form_2 = CommentInsertForm::builder()
473 .content("Comment 2".into())
474 .creator_id(inserted_person.id)
475 .post_id(inserted_post.id)
476 .language_id(finnish_id)
479 let inserted_comment_2 = Comment::create(pool, &comment_form_2, Some(&inserted_comment_0.path))
483 let comment_form_3 = CommentInsertForm::builder()
484 .content("Comment 3".into())
485 .creator_id(inserted_person.id)
486 .post_id(inserted_post.id)
487 .language_id(english_id)
490 let _inserted_comment_3 =
491 Comment::create(pool, &comment_form_3, Some(&inserted_comment_1.path))
495 let polish_id = Language::read_id_from_code(pool, Some("pl"))
499 let comment_form_4 = CommentInsertForm::builder()
500 .content("Comment 4".into())
501 .creator_id(inserted_person.id)
502 .post_id(inserted_post.id)
503 .language_id(Some(polish_id))
506 let inserted_comment_4 = Comment::create(pool, &comment_form_4, Some(&inserted_comment_1.path))
510 let comment_form_5 = CommentInsertForm::builder()
511 .content("Comment 5".into())
512 .creator_id(inserted_person.id)
513 .post_id(inserted_post.id)
516 let _inserted_comment_5 =
517 Comment::create(pool, &comment_form_5, Some(&inserted_comment_4.path))
521 let timmy_blocks_sara_form = PersonBlockForm {
522 person_id: inserted_person.id,
523 target_id: inserted_person_2.id,
526 let inserted_block = PersonBlock::block(pool, &timmy_blocks_sara_form)
530 let expected_block = PersonBlock {
531 id: inserted_block.id,
532 person_id: inserted_person.id,
533 target_id: inserted_person_2.id,
534 published: inserted_block.published,
536 assert_eq!(expected_block, inserted_block);
538 let comment_like_form = CommentLikeForm {
539 comment_id: inserted_comment_0.id,
540 post_id: inserted_post.id,
541 person_id: inserted_person.id,
545 let _inserted_comment_like = CommentLike::like(pool, &comment_like_form).await.unwrap();
547 let local_user_view = LocalUserView {
548 local_user: inserted_local_user.clone(),
549 person: inserted_person.clone(),
550 counts: Default::default(),
566 async fn test_crud() {
567 let pool = &build_db_pool_for_tests().await;
568 let pool = &mut pool.into();
569 let data = init_data(pool).await;
571 let expected_comment_view_no_person = expected_comment_view(&data, pool).await;
573 let mut expected_comment_view_with_person = expected_comment_view_no_person.clone();
574 expected_comment_view_with_person.my_vote = Some(1);
576 let read_comment_views_no_person = CommentQuery {
577 sort: (Some(CommentSortType::Old)),
578 post_id: (Some(data.inserted_post.id)),
586 expected_comment_view_no_person,
587 read_comment_views_no_person[0]
590 let read_comment_views_with_person = CommentQuery {
591 sort: (Some(CommentSortType::Old)),
592 post_id: (Some(data.inserted_post.id)),
593 local_user: (Some(&data.local_user_view)),
601 expected_comment_view_with_person,
602 read_comment_views_with_person[0]
605 // Make sure its 1, not showing the blocked comment
606 assert_eq!(5, read_comment_views_with_person.len());
608 let read_comment_from_blocked_person = CommentView::read(
610 data.inserted_comment_1.id,
611 Some(data.local_user_view.person.id),
616 // Make sure block set the creator blocked
617 assert!(read_comment_from_blocked_person.creator_blocked);
619 let read_liked_comment_views = CommentQuery {
620 local_user: (Some(&data.local_user_view)),
621 liked_only: (Some(true)),
629 expected_comment_view_with_person,
630 read_liked_comment_views[0]
633 assert_eq!(1, read_liked_comment_views.len());
635 let read_disliked_comment_views: Vec<CommentView> = CommentQuery {
636 local_user: (Some(&data.local_user_view)),
637 disliked_only: (Some(true)),
644 assert!(read_disliked_comment_views.is_empty());
646 cleanup(data, pool).await;
651 async fn test_comment_tree() {
652 let pool = &build_db_pool_for_tests().await;
653 let pool = &mut pool.into();
654 let data = init_data(pool).await;
656 let top_path = data.inserted_comment_0.path.clone();
657 let read_comment_views_top_path = CommentQuery {
658 post_id: (Some(data.inserted_post.id)),
659 parent_path: (Some(top_path)),
666 let child_path = data.inserted_comment_1.path.clone();
667 let read_comment_views_child_path = CommentQuery {
668 post_id: (Some(data.inserted_post.id)),
669 parent_path: (Some(child_path)),
676 // Make sure the comment parent-limited fetch is correct
677 assert_eq!(6, read_comment_views_top_path.len());
678 assert_eq!(4, read_comment_views_child_path.len());
680 // Make sure it contains the parent, but not the comment from the other tree
681 let child_comments = read_comment_views_child_path
684 .collect::<Vec<Comment>>();
685 assert!(child_comments.contains(&data.inserted_comment_1));
686 assert!(!child_comments.contains(&data.inserted_comment_2));
688 let read_comment_views_top_max_depth = CommentQuery {
689 post_id: (Some(data.inserted_post.id)),
690 max_depth: (Some(1)),
697 // Make sure a depth limited one only has the top comment
699 expected_comment_view(&data, pool).await,
700 read_comment_views_top_max_depth[0]
702 assert_eq!(1, read_comment_views_top_max_depth.len());
704 let child_path = data.inserted_comment_1.path.clone();
705 let read_comment_views_parent_max_depth = CommentQuery {
706 post_id: (Some(data.inserted_post.id)),
707 parent_path: (Some(child_path)),
708 max_depth: (Some(1)),
709 sort: (Some(CommentSortType::New)),
716 // Make sure a depth limited one, and given child comment 1, has 3
717 assert!(read_comment_views_parent_max_depth[2]
721 assert_eq!(3, read_comment_views_parent_max_depth.len());
723 cleanup(data, pool).await;
728 async fn test_languages() {
729 let pool = &build_db_pool_for_tests().await;
730 let pool = &mut pool.into();
731 let data = init_data(pool).await;
733 // by default, user has all languages enabled and should see all comments
734 // (except from blocked user)
735 let all_languages = CommentQuery {
736 local_user: (Some(&data.local_user_view)),
742 assert_eq!(5, all_languages.len());
744 // change user lang to finnish, should only show one post in finnish and one undetermined
745 let finnish_id = Language::read_id_from_code(pool, Some("fi"))
749 LocalUserLanguage::update(pool, vec![finnish_id], data.local_user_view.local_user.id)
752 let finnish_comments = CommentQuery {
753 local_user: (Some(&data.local_user_view)),
759 assert_eq!(2, finnish_comments.len());
760 let finnish_comment = finnish_comments
762 .find(|c| c.comment.language_id == finnish_id);
763 assert!(finnish_comment.is_some());
765 data.inserted_comment_2.content,
766 finnish_comment.unwrap().comment.content
769 // now show all comments with undetermined language (which is the default value)
770 LocalUserLanguage::update(
772 vec![UNDETERMINED_ID],
773 data.local_user_view.local_user.id,
777 let undetermined_comment = CommentQuery {
778 local_user: (Some(&data.local_user_view)),
784 assert_eq!(1, undetermined_comment.len());
786 cleanup(data, pool).await;
789 async fn cleanup(data: Data, pool: &mut DbPool<'_>) {
792 data.local_user_view.person.id,
793 data.inserted_comment_0.id,
797 Comment::delete(pool, data.inserted_comment_0.id)
800 Comment::delete(pool, data.inserted_comment_1.id)
803 Post::delete(pool, data.inserted_post.id).await.unwrap();
804 Community::delete(pool, data.inserted_community.id)
807 Person::delete(pool, data.local_user_view.person.id)
810 Person::delete(pool, data.inserted_person_2.id)
813 Instance::delete(pool, data.inserted_instance.id)
818 async fn expected_comment_view(data: &Data, pool: &mut DbPool<'_>) -> CommentView {
819 let agg = CommentAggregates::read(pool, data.inserted_comment_0.id)
823 creator_banned_from_community: false,
825 subscribed: SubscribedType::NotSubscribed,
827 creator_blocked: false,
829 id: data.inserted_comment_0.id,
830 content: "Comment 0".into(),
831 creator_id: data.local_user_view.person.id,
832 post_id: data.inserted_post.id,
835 published: data.inserted_comment_0.published,
836 ap_id: data.inserted_comment_0.ap_id.clone(),
839 distinguished: false,
840 path: data.inserted_comment_0.clone().path,
841 language_id: LanguageId(37),
844 id: data.local_user_view.person.id,
845 name: "timmy".into(),
847 published: data.local_user_view.person.published,
849 actor_id: data.local_user_view.person.actor_id.clone(),
858 inbox_url: data.local_user_view.person.inbox_url.clone(),
859 shared_inbox_url: None,
860 matrix_user_id: None,
862 instance_id: data.inserted_instance.id,
863 private_key: data.local_user_view.person.private_key.clone(),
864 public_key: data.local_user_view.person.public_key.clone(),
865 last_refreshed_at: data.local_user_view.person.last_refreshed_at,
868 id: data.inserted_post.id,
869 name: data.inserted_post.name.clone(),
870 creator_id: data.local_user_view.person.id,
873 published: data.inserted_post.published,
875 community_id: data.inserted_community.id,
881 embed_description: None,
882 embed_video_url: None,
884 ap_id: data.inserted_post.ap_id.clone(),
886 language_id: Default::default(),
887 featured_community: false,
888 featured_local: false,
890 community: Community {
891 id: data.inserted_community.id,
892 name: "test community 5".to_string(),
897 actor_id: data.inserted_community.actor_id.clone(),
899 title: "nada".to_owned(),
904 posting_restricted_to_mods: false,
905 published: data.inserted_community.published,
906 instance_id: data.inserted_instance.id,
907 private_key: data.inserted_community.private_key.clone(),
908 public_key: data.inserted_community.public_key.clone(),
909 last_refreshed_at: data.inserted_community.last_refreshed_at,
910 followers_url: data.inserted_community.followers_url.clone(),
911 inbox_url: data.inserted_community.inbox_url.clone(),
912 shared_inbox_url: data.inserted_community.shared_inbox_url.clone(),
913 moderators_url: data.inserted_community.moderators_url.clone(),
914 featured_url: data.inserted_community.featured_url.clone(),
916 counts: CommentAggregates {
918 comment_id: data.inserted_comment_0.id,
922 published: agg.published,
925 controversy_rank: 0.0,