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,
26 community_icon -> Nullable<Text>,
28 banned_from_community -> Bool,
29 creator_actor_id -> Text,
30 creator_local -> Bool,
31 creator_name -> Varchar,
32 creator_preferred_username -> Nullable<Varchar>,
33 creator_published -> Timestamp,
34 creator_avatar -> Nullable<Text>,
39 hot_rank_active -> Int4,
40 user_id -> Nullable<Int4>,
41 my_vote -> Nullable<Int4>,
42 subscribed -> Nullable<Bool>,
43 saved -> Nullable<Bool>,
48 comment_fast_view (id) {
53 parent_id -> Nullable<Int4>,
57 published -> Timestamp,
58 updated -> Nullable<Timestamp>,
63 community_actor_id -> Text,
64 community_local -> Bool,
65 community_name -> Varchar,
66 community_icon -> Nullable<Text>,
68 banned_from_community -> Bool,
69 creator_actor_id -> Text,
70 creator_local -> Bool,
71 creator_name -> Varchar,
72 creator_preferred_username -> Nullable<Varchar>,
73 creator_published -> Timestamp,
74 creator_avatar -> Nullable<Text>,
79 hot_rank_active -> Int4,
80 user_id -> Nullable<Int4>,
81 my_vote -> Nullable<Int4>,
82 subscribed -> Nullable<Bool>,
83 saved -> Nullable<Bool>,
87 #[derive(Queryable, Identifiable, PartialEq, Debug, Serialize, QueryableByName, Clone)]
88 #[table_name = "comment_fast_view"]
89 pub struct CommentView {
93 pub post_name: String,
94 pub parent_id: Option<i32>,
98 pub published: chrono::NaiveDateTime,
99 pub updated: Option<chrono::NaiveDateTime>,
103 pub community_id: i32,
104 pub community_actor_id: String,
105 pub community_local: bool,
106 pub community_name: String,
107 pub community_icon: Option<String>,
109 pub banned_from_community: bool,
110 pub creator_actor_id: String,
111 pub creator_local: bool,
112 pub creator_name: String,
113 pub creator_preferred_username: Option<String>,
114 pub creator_published: chrono::NaiveDateTime,
115 pub creator_avatar: Option<String>,
120 pub hot_rank_active: i32,
121 pub user_id: Option<i32>,
122 pub my_vote: Option<i32>,
123 pub subscribed: Option<bool>,
124 pub saved: Option<bool>,
127 pub struct CommentQueryBuilder<'a> {
128 conn: &'a PgConnection,
129 query: super::comment_view::comment_fast_view::BoxedQuery<'a, Pg>,
130 listing_type: ListingType,
132 for_community_id: Option<i32>,
133 for_post_id: Option<i32>,
134 for_creator_id: Option<i32>,
135 search_term: Option<String>,
136 my_user_id: Option<i32>,
142 impl<'a> CommentQueryBuilder<'a> {
143 pub fn create(conn: &'a PgConnection) -> Self {
144 use super::comment_view::comment_fast_view::dsl::*;
146 let query = comment_fast_view.into_boxed();
148 CommentQueryBuilder {
151 listing_type: ListingType::All,
152 sort: &SortType::New,
153 for_community_id: None,
155 for_creator_id: None,
164 pub fn listing_type(mut self, listing_type: ListingType) -> Self {
165 self.listing_type = listing_type;
169 pub fn sort(mut self, sort: &'a SortType) -> Self {
174 pub fn for_post_id<T: MaybeOptional<i32>>(mut self, for_post_id: T) -> Self {
175 self.for_post_id = for_post_id.get_optional();
179 pub fn for_creator_id<T: MaybeOptional<i32>>(mut self, for_creator_id: T) -> Self {
180 self.for_creator_id = for_creator_id.get_optional();
184 pub fn for_community_id<T: MaybeOptional<i32>>(mut self, for_community_id: T) -> Self {
185 self.for_community_id = for_community_id.get_optional();
189 pub fn search_term<T: MaybeOptional<String>>(mut self, search_term: T) -> Self {
190 self.search_term = search_term.get_optional();
194 pub fn my_user_id<T: MaybeOptional<i32>>(mut self, my_user_id: T) -> Self {
195 self.my_user_id = my_user_id.get_optional();
199 pub fn saved_only(mut self, saved_only: bool) -> Self {
200 self.saved_only = saved_only;
204 pub fn page<T: MaybeOptional<i64>>(mut self, page: T) -> Self {
205 self.page = page.get_optional();
209 pub fn limit<T: MaybeOptional<i64>>(mut self, limit: T) -> Self {
210 self.limit = limit.get_optional();
214 pub fn list(self) -> Result<Vec<CommentView>, Error> {
215 use super::comment_view::comment_fast_view::dsl::*;
217 let mut query = self.query;
219 // The view lets you pass a null user_id, if you're not logged in
220 if let Some(my_user_id) = self.my_user_id {
221 query = query.filter(user_id.eq(my_user_id));
223 query = query.filter(user_id.is_null());
226 if let Some(for_creator_id) = self.for_creator_id {
227 query = query.filter(creator_id.eq(for_creator_id));
230 if let Some(for_community_id) = self.for_community_id {
231 query = query.filter(community_id.eq(for_community_id));
234 if let Some(for_post_id) = self.for_post_id {
235 query = query.filter(post_id.eq(for_post_id));
238 if let Some(search_term) = self.search_term {
239 query = query.filter(content.ilike(fuzzy_search(&search_term)));
242 query = match self.listing_type {
243 ListingType::Subscribed => query.filter(subscribed.eq(true)),
244 ListingType::Local => query.filter(community_local.eq(true)),
249 query = query.filter(saved.eq(true));
252 query = match self.sort {
253 SortType::Hot => query
254 .order_by(hot_rank.desc())
255 .then_order_by(published.desc()),
256 SortType::Active => query
257 .order_by(hot_rank_active.desc())
258 .then_order_by(published.desc()),
259 SortType::New => query.order_by(published.desc()),
260 SortType::TopAll => query.order_by(score.desc()),
261 SortType::TopYear => query
262 .filter(published.gt(now - 1.years()))
263 .order_by(score.desc()),
264 SortType::TopMonth => query
265 .filter(published.gt(now - 1.months()))
266 .order_by(score.desc()),
267 SortType::TopWeek => query
268 .filter(published.gt(now - 1.weeks()))
269 .order_by(score.desc()),
270 SortType::TopDay => query
271 .filter(published.gt(now - 1.days()))
272 .order_by(score.desc()),
273 // _ => query.order_by(published.desc()),
276 let (limit, offset) = limit_and_offset(self.page, self.limit);
278 // Note: deleted and removed comments are done on the front side
282 .load::<CommentView>(self.conn)
289 from_comment_id: i32,
290 my_user_id: Option<i32>,
291 ) -> Result<Self, Error> {
292 use super::comment_view::comment_fast_view::dsl::*;
293 let mut query = comment_fast_view.into_boxed();
295 // The view lets you pass a null user_id, if you're not logged in
296 if let Some(my_user_id) = my_user_id {
297 query = query.filter(user_id.eq(my_user_id));
299 query = query.filter(user_id.is_null());
303 .filter(id.eq(from_comment_id))
304 .order_by(published.desc());
306 query.first::<Self>(conn)
310 // The faked schema since diesel doesn't do views
312 reply_fast_view (id) {
316 post_name -> Varchar,
317 parent_id -> Nullable<Int4>,
321 published -> Timestamp,
322 updated -> Nullable<Timestamp>,
326 community_id -> Int4,
327 community_actor_id -> Text,
328 community_local -> Bool,
329 community_name -> Varchar,
330 community_icon -> Nullable<Varchar>,
332 banned_from_community -> Bool,
333 creator_actor_id -> Text,
334 creator_local -> Bool,
335 creator_name -> Varchar,
336 creator_preferred_username -> Nullable<Varchar>,
337 creator_avatar -> Nullable<Text>,
338 creator_published -> Timestamp,
343 hot_rank_active -> Int4,
344 user_id -> Nullable<Int4>,
345 my_vote -> Nullable<Int4>,
346 subscribed -> Nullable<Bool>,
347 saved -> Nullable<Bool>,
348 recipient_id -> Int4,
353 Queryable, Identifiable, PartialEq, Debug, Serialize, Deserialize, QueryableByName, Clone,
355 #[table_name = "reply_fast_view"]
356 pub struct ReplyView {
360 pub post_name: String,
361 pub parent_id: Option<i32>,
365 pub published: chrono::NaiveDateTime,
366 pub updated: Option<chrono::NaiveDateTime>,
370 pub community_id: i32,
371 pub community_actor_id: String,
372 pub community_local: bool,
373 pub community_name: String,
374 pub community_icon: Option<String>,
376 pub banned_from_community: bool,
377 pub creator_actor_id: String,
378 pub creator_local: bool,
379 pub creator_name: String,
380 pub creator_preferred_username: Option<String>,
381 pub creator_avatar: Option<String>,
382 pub creator_published: chrono::NaiveDateTime,
387 pub hot_rank_active: i32,
388 pub user_id: Option<i32>,
389 pub my_vote: Option<i32>,
390 pub subscribed: Option<bool>,
391 pub saved: Option<bool>,
392 pub recipient_id: i32,
395 pub struct ReplyQueryBuilder<'a> {
396 conn: &'a PgConnection,
397 query: super::comment_view::reply_fast_view::BoxedQuery<'a, Pg>,
405 impl<'a> ReplyQueryBuilder<'a> {
406 pub fn create(conn: &'a PgConnection, for_user_id: i32) -> Self {
407 use super::comment_view::reply_fast_view::dsl::*;
409 let query = reply_fast_view.into_boxed();
415 sort: &SortType::New,
422 pub fn sort(mut self, sort: &'a SortType) -> Self {
427 pub fn unread_only(mut self, unread_only: bool) -> Self {
428 self.unread_only = unread_only;
432 pub fn page<T: MaybeOptional<i64>>(mut self, page: T) -> Self {
433 self.page = page.get_optional();
437 pub fn limit<T: MaybeOptional<i64>>(mut self, limit: T) -> Self {
438 self.limit = limit.get_optional();
442 pub fn list(self) -> Result<Vec<ReplyView>, Error> {
443 use super::comment_view::reply_fast_view::dsl::*;
445 let mut query = self.query;
448 .filter(user_id.eq(self.for_user_id))
449 .filter(recipient_id.eq(self.for_user_id))
450 .filter(deleted.eq(false))
451 .filter(removed.eq(false));
453 if self.unread_only {
454 query = query.filter(read.eq(false));
457 query = match self.sort {
458 // SortType::Hot => query.order_by(hot_rank.desc()), // TODO why is this commented
459 SortType::New => query.order_by(published.desc()),
460 SortType::TopAll => query.order_by(score.desc()),
461 SortType::TopYear => query
462 .filter(published.gt(now - 1.years()))
463 .order_by(score.desc()),
464 SortType::TopMonth => query
465 .filter(published.gt(now - 1.months()))
466 .order_by(score.desc()),
467 SortType::TopWeek => query
468 .filter(published.gt(now - 1.weeks()))
469 .order_by(score.desc()),
470 SortType::TopDay => query
471 .filter(published.gt(now - 1.days()))
472 .order_by(score.desc()),
473 _ => query.order_by(published.desc()),
476 let (limit, offset) = limit_and_offset(self.page, self.limit);
480 .load::<ReplyView>(self.conn)
491 tests::establish_unpooled_connection,
500 let conn = establish_unpooled_connection();
502 let new_user = UserForm {
503 name: "timmy".into(),
504 preferred_username: None,
505 password_encrypted: "nope".into(),
507 matrix_user_id: None,
514 theme: "darkly".into(),
515 default_sort_type: SortType::Hot as i16,
516 default_listing_type: ListingType::Subscribed as i16,
517 lang: "browser".into(),
519 send_notifications_to_email: false,
525 last_refreshed_at: None,
528 let inserted_user = User_::create(&conn, &new_user).unwrap();
530 let new_community = CommunityForm {
531 name: "test community 5".to_string(),
532 title: "nada".to_owned(),
535 creator_id: inserted_user.id,
544 last_refreshed_at: None,
550 let inserted_community = Community::create(&conn, &new_community).unwrap();
552 let new_post = PostForm {
553 name: "A test post 2".into(),
554 creator_id: inserted_user.id,
557 community_id: inserted_community.id,
565 embed_description: None,
573 let inserted_post = Post::create(&conn, &new_post).unwrap();
575 let comment_form = CommentForm {
576 content: "A test comment 32".into(),
577 creator_id: inserted_user.id,
578 post_id: inserted_post.id,
589 let inserted_comment = Comment::create(&conn, &comment_form).unwrap();
591 let comment_like_form = CommentLikeForm {
592 comment_id: inserted_comment.id,
593 post_id: inserted_post.id,
594 user_id: inserted_user.id,
598 let _inserted_comment_like = CommentLike::like(&conn, &comment_like_form).unwrap();
600 let expected_comment_view_no_user = CommentView {
601 id: inserted_comment.id,
602 content: "A test comment 32".into(),
603 creator_id: inserted_user.id,
604 post_id: inserted_post.id,
605 post_name: inserted_post.name.to_owned(),
606 community_id: inserted_community.id,
607 community_name: inserted_community.name.to_owned(),
608 community_icon: None,
614 banned_from_community: false,
615 published: inserted_comment.published,
617 creator_name: inserted_user.name.to_owned(),
618 creator_preferred_username: None,
619 creator_published: inserted_user.published,
620 creator_avatar: None,
630 ap_id: inserted_comment.ap_id.to_owned(),
632 community_actor_id: inserted_community.actor_id.to_owned(),
633 community_local: true,
634 creator_actor_id: inserted_user.actor_id.to_owned(),
638 let expected_comment_view_with_user = CommentView {
639 id: inserted_comment.id,
640 content: "A test comment 32".into(),
641 creator_id: inserted_user.id,
642 post_id: inserted_post.id,
643 post_name: inserted_post.name.to_owned(),
644 community_id: inserted_community.id,
645 community_name: inserted_community.name.to_owned(),
646 community_icon: None,
652 banned_from_community: false,
653 published: inserted_comment.published,
655 creator_name: inserted_user.name.to_owned(),
656 creator_preferred_username: None,
657 creator_published: inserted_user.published,
658 creator_avatar: None,
664 user_id: Some(inserted_user.id),
666 subscribed: Some(false),
668 ap_id: inserted_comment.ap_id.to_owned(),
670 community_actor_id: inserted_community.actor_id.to_owned(),
671 community_local: true,
672 creator_actor_id: inserted_user.actor_id.to_owned(),
676 let mut read_comment_views_no_user = CommentQueryBuilder::create(&conn)
677 .for_post_id(inserted_post.id)
680 read_comment_views_no_user[0].hot_rank = 0;
681 read_comment_views_no_user[0].hot_rank_active = 0;
683 let mut read_comment_views_with_user = CommentQueryBuilder::create(&conn)
684 .for_post_id(inserted_post.id)
685 .my_user_id(inserted_user.id)
688 read_comment_views_with_user[0].hot_rank = 0;
689 read_comment_views_with_user[0].hot_rank_active = 0;
691 let like_removed = CommentLike::remove(&conn, inserted_user.id, inserted_comment.id).unwrap();
692 let num_deleted = Comment::delete(&conn, inserted_comment.id).unwrap();
693 Post::delete(&conn, inserted_post.id).unwrap();
694 Community::delete(&conn, inserted_community.id).unwrap();
695 User_::delete(&conn, inserted_user.id).unwrap();
697 assert_eq!(expected_comment_view_no_user, read_comment_views_no_user[0]);
699 expected_comment_view_with_user,
700 read_comment_views_with_user[0]
702 assert_eq!(1, num_deleted);
703 assert_eq!(1, like_removed);