1 // TODO, remove the cross join here, just join to user directly
2 use crate::{fuzzy_search, limit_and_offset, ListingType, MaybeOptional, SortType};
3 use diesel::{dsl::*, pg::Pg, result::Error, *};
4 use serde::{Deserialize, Serialize};
6 // The faked schema since diesel doesn't do views
13 parent_id -> Nullable<Int4>,
17 published -> Timestamp,
18 updated -> Nullable<Timestamp>,
23 community_actor_id -> Text,
24 community_local -> Bool,
25 community_name -> Varchar,
27 banned_from_community -> Bool,
28 creator_actor_id -> Text,
29 creator_local -> Bool,
30 creator_name -> Varchar,
31 creator_published -> Timestamp,
32 creator_avatar -> Nullable<Text>,
37 user_id -> Nullable<Int4>,
38 my_vote -> Nullable<Int4>,
39 subscribed -> Nullable<Bool>,
40 saved -> Nullable<Bool>,
45 comment_fast_view (id) {
50 parent_id -> Nullable<Int4>,
54 published -> Timestamp,
55 updated -> Nullable<Timestamp>,
60 community_actor_id -> Text,
61 community_local -> Bool,
62 community_name -> Varchar,
64 banned_from_community -> Bool,
65 creator_actor_id -> Text,
66 creator_local -> Bool,
67 creator_name -> Varchar,
68 creator_published -> Timestamp,
69 creator_avatar -> Nullable<Text>,
74 user_id -> Nullable<Int4>,
75 my_vote -> Nullable<Int4>,
76 subscribed -> Nullable<Bool>,
77 saved -> Nullable<Bool>,
82 Queryable, Identifiable, PartialEq, Debug, Serialize, Deserialize, QueryableByName, Clone,
84 #[table_name = "comment_fast_view"]
85 pub struct CommentView {
89 pub post_name: String,
90 pub parent_id: Option<i32>,
94 pub published: chrono::NaiveDateTime,
95 pub updated: Option<chrono::NaiveDateTime>,
99 pub community_id: i32,
100 pub community_actor_id: String,
101 pub community_local: bool,
102 pub community_name: String,
104 pub banned_from_community: bool,
105 pub creator_actor_id: String,
106 pub creator_local: bool,
107 pub creator_name: String,
108 pub creator_published: chrono::NaiveDateTime,
109 pub creator_avatar: Option<String>,
114 pub user_id: Option<i32>,
115 pub my_vote: Option<i32>,
116 pub subscribed: Option<bool>,
117 pub saved: Option<bool>,
120 pub struct CommentQueryBuilder<'a> {
121 conn: &'a PgConnection,
122 query: super::comment_view::comment_fast_view::BoxedQuery<'a, Pg>,
123 listing_type: ListingType,
125 for_community_id: Option<i32>,
126 for_post_id: Option<i32>,
127 for_creator_id: Option<i32>,
128 search_term: Option<String>,
129 my_user_id: Option<i32>,
135 impl<'a> CommentQueryBuilder<'a> {
136 pub fn create(conn: &'a PgConnection) -> Self {
137 use super::comment_view::comment_fast_view::dsl::*;
139 let query = comment_fast_view.into_boxed();
141 CommentQueryBuilder {
144 listing_type: ListingType::All,
145 sort: &SortType::New,
146 for_community_id: None,
148 for_creator_id: None,
157 pub fn listing_type(mut self, listing_type: ListingType) -> Self {
158 self.listing_type = listing_type;
162 pub fn sort(mut self, sort: &'a SortType) -> Self {
167 pub fn for_post_id<T: MaybeOptional<i32>>(mut self, for_post_id: T) -> Self {
168 self.for_post_id = for_post_id.get_optional();
172 pub fn for_creator_id<T: MaybeOptional<i32>>(mut self, for_creator_id: T) -> Self {
173 self.for_creator_id = for_creator_id.get_optional();
177 pub fn for_community_id<T: MaybeOptional<i32>>(mut self, for_community_id: T) -> Self {
178 self.for_community_id = for_community_id.get_optional();
182 pub fn search_term<T: MaybeOptional<String>>(mut self, search_term: T) -> Self {
183 self.search_term = search_term.get_optional();
187 pub fn my_user_id<T: MaybeOptional<i32>>(mut self, my_user_id: T) -> Self {
188 self.my_user_id = my_user_id.get_optional();
192 pub fn saved_only(mut self, saved_only: bool) -> Self {
193 self.saved_only = saved_only;
197 pub fn page<T: MaybeOptional<i64>>(mut self, page: T) -> Self {
198 self.page = page.get_optional();
202 pub fn limit<T: MaybeOptional<i64>>(mut self, limit: T) -> Self {
203 self.limit = limit.get_optional();
207 pub fn list(self) -> Result<Vec<CommentView>, Error> {
208 use super::comment_view::comment_fast_view::dsl::*;
210 let mut query = self.query;
212 // The view lets you pass a null user_id, if you're not logged in
213 if let Some(my_user_id) = self.my_user_id {
214 query = query.filter(user_id.eq(my_user_id));
216 query = query.filter(user_id.is_null());
219 if let Some(for_creator_id) = self.for_creator_id {
220 query = query.filter(creator_id.eq(for_creator_id));
223 if let Some(for_community_id) = self.for_community_id {
224 query = query.filter(community_id.eq(for_community_id));
227 if let Some(for_post_id) = self.for_post_id {
228 query = query.filter(post_id.eq(for_post_id));
231 if let Some(search_term) = self.search_term {
232 query = query.filter(content.ilike(fuzzy_search(&search_term)));
235 if let ListingType::Subscribed = self.listing_type {
236 query = query.filter(subscribed.eq(true));
240 query = query.filter(saved.eq(true));
243 query = match self.sort {
244 SortType::Hot => query
245 .order_by(hot_rank.desc())
246 .then_order_by(published.desc()),
247 SortType::New => query.order_by(published.desc()),
248 SortType::TopAll => query.order_by(score.desc()),
249 SortType::TopYear => query
250 .filter(published.gt(now - 1.years()))
251 .order_by(score.desc()),
252 SortType::TopMonth => query
253 .filter(published.gt(now - 1.months()))
254 .order_by(score.desc()),
255 SortType::TopWeek => query
256 .filter(published.gt(now - 1.weeks()))
257 .order_by(score.desc()),
258 SortType::TopDay => query
259 .filter(published.gt(now - 1.days()))
260 .order_by(score.desc()),
261 // _ => query.order_by(published.desc()),
264 let (limit, offset) = limit_and_offset(self.page, self.limit);
266 // Note: deleted and removed comments are done on the front side
270 .load::<CommentView>(self.conn)
277 from_comment_id: i32,
278 my_user_id: Option<i32>,
279 ) -> Result<Self, Error> {
280 use super::comment_view::comment_fast_view::dsl::*;
281 let mut query = comment_fast_view.into_boxed();
283 // The view lets you pass a null user_id, if you're not logged in
284 if let Some(my_user_id) = my_user_id {
285 query = query.filter(user_id.eq(my_user_id));
287 query = query.filter(user_id.is_null());
291 .filter(id.eq(from_comment_id))
292 .order_by(published.desc());
294 query.first::<Self>(conn)
298 // The faked schema since diesel doesn't do views
300 reply_fast_view (id) {
304 post_name -> Varchar,
305 parent_id -> Nullable<Int4>,
309 published -> Timestamp,
310 updated -> Nullable<Timestamp>,
314 community_id -> Int4,
315 community_actor_id -> Text,
316 community_local -> Bool,
317 community_name -> Varchar,
319 banned_from_community -> Bool,
320 creator_actor_id -> Text,
321 creator_local -> Bool,
322 creator_name -> Varchar,
323 creator_avatar -> Nullable<Text>,
324 creator_published -> Timestamp,
329 user_id -> Nullable<Int4>,
330 my_vote -> Nullable<Int4>,
331 subscribed -> Nullable<Bool>,
332 saved -> Nullable<Bool>,
333 recipient_id -> Int4,
338 Queryable, Identifiable, PartialEq, Debug, Serialize, Deserialize, QueryableByName, Clone,
340 #[table_name = "reply_fast_view"]
341 pub struct ReplyView {
345 pub post_name: String,
346 pub parent_id: Option<i32>,
350 pub published: chrono::NaiveDateTime,
351 pub updated: Option<chrono::NaiveDateTime>,
355 pub community_id: i32,
356 pub community_actor_id: String,
357 pub community_local: bool,
358 pub community_name: String,
360 pub banned_from_community: bool,
361 pub creator_actor_id: String,
362 pub creator_local: bool,
363 pub creator_name: String,
364 pub creator_avatar: Option<String>,
365 pub creator_published: chrono::NaiveDateTime,
370 pub user_id: Option<i32>,
371 pub my_vote: Option<i32>,
372 pub subscribed: Option<bool>,
373 pub saved: Option<bool>,
374 pub recipient_id: i32,
377 pub struct ReplyQueryBuilder<'a> {
378 conn: &'a PgConnection,
379 query: super::comment_view::reply_fast_view::BoxedQuery<'a, Pg>,
387 impl<'a> ReplyQueryBuilder<'a> {
388 pub fn create(conn: &'a PgConnection, for_user_id: i32) -> Self {
389 use super::comment_view::reply_fast_view::dsl::*;
391 let query = reply_fast_view.into_boxed();
397 sort: &SortType::New,
404 pub fn sort(mut self, sort: &'a SortType) -> Self {
409 pub fn unread_only(mut self, unread_only: bool) -> Self {
410 self.unread_only = unread_only;
414 pub fn page<T: MaybeOptional<i64>>(mut self, page: T) -> Self {
415 self.page = page.get_optional();
419 pub fn limit<T: MaybeOptional<i64>>(mut self, limit: T) -> Self {
420 self.limit = limit.get_optional();
424 pub fn list(self) -> Result<Vec<ReplyView>, Error> {
425 use super::comment_view::reply_fast_view::dsl::*;
427 let mut query = self.query;
430 .filter(user_id.eq(self.for_user_id))
431 .filter(recipient_id.eq(self.for_user_id))
432 .filter(deleted.eq(false))
433 .filter(removed.eq(false));
435 if self.unread_only {
436 query = query.filter(read.eq(false));
439 query = match self.sort {
440 // SortType::Hot => query.order_by(hot_rank.desc()),
441 SortType::New => query.order_by(published.desc()),
442 SortType::TopAll => query.order_by(score.desc()),
443 SortType::TopYear => query
444 .filter(published.gt(now - 1.years()))
445 .order_by(score.desc()),
446 SortType::TopMonth => query
447 .filter(published.gt(now - 1.months()))
448 .order_by(score.desc()),
449 SortType::TopWeek => query
450 .filter(published.gt(now - 1.weeks()))
451 .order_by(score.desc()),
452 SortType::TopDay => query
453 .filter(published.gt(now - 1.days()))
454 .order_by(score.desc()),
455 _ => query.order_by(published.desc()),
458 let (limit, offset) = limit_and_offset(self.page, self.limit);
462 .load::<ReplyView>(self.conn)
473 tests::establish_unpooled_connection,
482 let conn = establish_unpooled_connection();
484 let new_user = UserForm {
485 name: "timmy".into(),
486 preferred_username: None,
487 password_encrypted: "nope".into(),
489 matrix_user_id: None,
495 theme: "darkly".into(),
496 default_sort_type: SortType::Hot as i16,
497 default_listing_type: ListingType::Subscribed as i16,
498 lang: "browser".into(),
500 send_notifications_to_email: false,
501 actor_id: "changeme_92873982".into(),
506 last_refreshed_at: None,
509 let inserted_user = User_::create(&conn, &new_user).unwrap();
511 let new_community = CommunityForm {
512 name: "test community 5".to_string(),
513 title: "nada".to_owned(),
516 creator_id: inserted_user.id,
521 actor_id: "changeme_7625376".into(),
525 last_refreshed_at: None,
529 let inserted_community = Community::create(&conn, &new_community).unwrap();
531 let new_post = PostForm {
532 name: "A test post 2".into(),
533 creator_id: inserted_user.id,
536 community_id: inserted_community.id,
544 embed_description: None,
547 ap_id: "http://fake.com".into(),
552 let inserted_post = Post::create(&conn, &new_post).unwrap();
554 let comment_form = CommentForm {
555 content: "A test comment 32".into(),
556 creator_id: inserted_user.id,
557 post_id: inserted_post.id,
564 ap_id: "http://fake.com".into(),
568 let inserted_comment = Comment::create(&conn, &comment_form).unwrap();
570 let comment_like_form = CommentLikeForm {
571 comment_id: inserted_comment.id,
572 post_id: inserted_post.id,
573 user_id: inserted_user.id,
577 let _inserted_comment_like = CommentLike::like(&conn, &comment_like_form).unwrap();
579 let expected_comment_view_no_user = CommentView {
580 id: inserted_comment.id,
581 content: "A test comment 32".into(),
582 creator_id: inserted_user.id,
583 post_id: inserted_post.id,
584 post_name: inserted_post.name.to_owned(),
585 community_id: inserted_community.id,
586 community_name: inserted_community.name.to_owned(),
592 banned_from_community: false,
593 published: inserted_comment.published,
595 creator_name: inserted_user.name.to_owned(),
596 creator_published: inserted_user.published,
597 creator_avatar: None,
606 ap_id: "http://fake.com".to_string(),
608 community_actor_id: inserted_community.actor_id.to_owned(),
609 community_local: true,
610 creator_actor_id: inserted_user.actor_id.to_owned(),
614 let expected_comment_view_with_user = CommentView {
615 id: inserted_comment.id,
616 content: "A test comment 32".into(),
617 creator_id: inserted_user.id,
618 post_id: inserted_post.id,
619 post_name: inserted_post.name.to_owned(),
620 community_id: inserted_community.id,
621 community_name: inserted_community.name.to_owned(),
627 banned_from_community: false,
628 published: inserted_comment.published,
630 creator_name: inserted_user.name.to_owned(),
631 creator_published: inserted_user.published,
632 creator_avatar: None,
637 user_id: Some(inserted_user.id),
639 subscribed: Some(false),
641 ap_id: "http://fake.com".to_string(),
643 community_actor_id: inserted_community.actor_id.to_owned(),
644 community_local: true,
645 creator_actor_id: inserted_user.actor_id.to_owned(),
649 let mut read_comment_views_no_user = CommentQueryBuilder::create(&conn)
650 .for_post_id(inserted_post.id)
653 read_comment_views_no_user[0].hot_rank = 0;
655 let mut read_comment_views_with_user = CommentQueryBuilder::create(&conn)
656 .for_post_id(inserted_post.id)
657 .my_user_id(inserted_user.id)
660 read_comment_views_with_user[0].hot_rank = 0;
662 let like_removed = CommentLike::remove(&conn, &comment_like_form).unwrap();
663 let num_deleted = Comment::delete(&conn, inserted_comment.id).unwrap();
664 Post::delete(&conn, inserted_post.id).unwrap();
665 Community::delete(&conn, inserted_community.id).unwrap();
666 User_::delete(&conn, inserted_user.id).unwrap();
668 assert_eq!(expected_comment_view_no_user, read_comment_views_no_user[0]);
670 expected_comment_view_with_user,
671 read_comment_views_with_user[0]
673 assert_eq!(1, num_deleted);
674 assert_eq!(1, like_removed);