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_community_name: Option<String>,
134 for_post_id: Option<i32>,
135 for_creator_id: Option<i32>,
136 search_term: Option<String>,
137 my_user_id: Option<i32>,
143 impl<'a> CommentQueryBuilder<'a> {
144 pub fn create(conn: &'a PgConnection) -> Self {
145 use super::comment_view::comment_fast_view::dsl::*;
147 let query = comment_fast_view.into_boxed();
149 CommentQueryBuilder {
152 listing_type: ListingType::All,
153 sort: &SortType::New,
154 for_community_id: None,
155 for_community_name: None,
157 for_creator_id: None,
166 pub fn listing_type(mut self, listing_type: ListingType) -> Self {
167 self.listing_type = listing_type;
171 pub fn sort(mut self, sort: &'a SortType) -> Self {
176 pub fn for_post_id<T: MaybeOptional<i32>>(mut self, for_post_id: T) -> Self {
177 self.for_post_id = for_post_id.get_optional();
181 pub fn for_creator_id<T: MaybeOptional<i32>>(mut self, for_creator_id: T) -> Self {
182 self.for_creator_id = for_creator_id.get_optional();
186 pub fn for_community_id<T: MaybeOptional<i32>>(mut self, for_community_id: T) -> Self {
187 self.for_community_id = for_community_id.get_optional();
191 pub fn for_community_name<T: MaybeOptional<String>>(mut self, for_community_name: T) -> Self {
192 self.for_community_name = for_community_name.get_optional();
196 pub fn search_term<T: MaybeOptional<String>>(mut self, search_term: T) -> Self {
197 self.search_term = search_term.get_optional();
201 pub fn my_user_id<T: MaybeOptional<i32>>(mut self, my_user_id: T) -> Self {
202 self.my_user_id = my_user_id.get_optional();
206 pub fn saved_only(mut self, saved_only: bool) -> Self {
207 self.saved_only = saved_only;
211 pub fn page<T: MaybeOptional<i64>>(mut self, page: T) -> Self {
212 self.page = page.get_optional();
216 pub fn limit<T: MaybeOptional<i64>>(mut self, limit: T) -> Self {
217 self.limit = limit.get_optional();
221 pub fn list(self) -> Result<Vec<CommentView>, Error> {
222 use super::comment_view::comment_fast_view::dsl::*;
224 let mut query = self.query;
226 // The view lets you pass a null user_id, if you're not logged in
227 if let Some(my_user_id) = self.my_user_id {
228 query = query.filter(user_id.eq(my_user_id));
230 query = query.filter(user_id.is_null());
233 if let Some(for_creator_id) = self.for_creator_id {
234 query = query.filter(creator_id.eq(for_creator_id));
237 if let Some(for_community_id) = self.for_community_id {
238 query = query.filter(community_id.eq(for_community_id));
241 if let Some(for_community_name) = self.for_community_name {
242 query = query.filter(community_name.eq(for_community_name));
245 if let Some(for_post_id) = self.for_post_id {
246 query = query.filter(post_id.eq(for_post_id));
249 if let Some(search_term) = self.search_term {
250 query = query.filter(content.ilike(fuzzy_search(&search_term)));
253 query = match self.listing_type {
254 ListingType::Subscribed => query.filter(subscribed.eq(true)),
255 ListingType::Local => query.filter(community_local.eq(true)),
260 query = query.filter(saved.eq(true));
263 query = match self.sort {
264 SortType::Hot => query
265 .order_by(hot_rank.desc())
266 .then_order_by(published.desc()),
267 SortType::Active => query
268 .order_by(hot_rank_active.desc())
269 .then_order_by(published.desc()),
270 SortType::New => query.order_by(published.desc()),
271 SortType::TopAll => query.order_by(score.desc()),
272 SortType::TopYear => query
273 .filter(published.gt(now - 1.years()))
274 .order_by(score.desc()),
275 SortType::TopMonth => query
276 .filter(published.gt(now - 1.months()))
277 .order_by(score.desc()),
278 SortType::TopWeek => query
279 .filter(published.gt(now - 1.weeks()))
280 .order_by(score.desc()),
281 SortType::TopDay => query
282 .filter(published.gt(now - 1.days()))
283 .order_by(score.desc()),
284 // _ => query.order_by(published.desc()),
287 let (limit, offset) = limit_and_offset(self.page, self.limit);
289 // Note: deleted and removed comments are done on the front side
293 .load::<CommentView>(self.conn)
300 from_comment_id: i32,
301 my_user_id: Option<i32>,
302 ) -> Result<Self, Error> {
303 use super::comment_view::comment_fast_view::dsl::*;
304 let mut query = comment_fast_view.into_boxed();
306 // The view lets you pass a null user_id, if you're not logged in
307 if let Some(my_user_id) = my_user_id {
308 query = query.filter(user_id.eq(my_user_id));
310 query = query.filter(user_id.is_null());
314 .filter(id.eq(from_comment_id))
315 .order_by(published.desc());
317 query.first::<Self>(conn)
321 // The faked schema since diesel doesn't do views
323 reply_fast_view (id) {
327 post_name -> Varchar,
328 parent_id -> Nullable<Int4>,
332 published -> Timestamp,
333 updated -> Nullable<Timestamp>,
337 community_id -> Int4,
338 community_actor_id -> Text,
339 community_local -> Bool,
340 community_name -> Varchar,
341 community_icon -> Nullable<Varchar>,
343 banned_from_community -> Bool,
344 creator_actor_id -> Text,
345 creator_local -> Bool,
346 creator_name -> Varchar,
347 creator_preferred_username -> Nullable<Varchar>,
348 creator_avatar -> Nullable<Text>,
349 creator_published -> Timestamp,
354 hot_rank_active -> Int4,
355 user_id -> Nullable<Int4>,
356 my_vote -> Nullable<Int4>,
357 subscribed -> Nullable<Bool>,
358 saved -> Nullable<Bool>,
359 recipient_id -> Int4,
364 Queryable, Identifiable, PartialEq, Debug, Serialize, Deserialize, QueryableByName, Clone,
366 #[table_name = "reply_fast_view"]
367 pub struct ReplyView {
371 pub post_name: String,
372 pub parent_id: Option<i32>,
376 pub published: chrono::NaiveDateTime,
377 pub updated: Option<chrono::NaiveDateTime>,
381 pub community_id: i32,
382 pub community_actor_id: String,
383 pub community_local: bool,
384 pub community_name: String,
385 pub community_icon: Option<String>,
387 pub banned_from_community: bool,
388 pub creator_actor_id: String,
389 pub creator_local: bool,
390 pub creator_name: String,
391 pub creator_preferred_username: Option<String>,
392 pub creator_avatar: Option<String>,
393 pub creator_published: chrono::NaiveDateTime,
398 pub hot_rank_active: i32,
399 pub user_id: Option<i32>,
400 pub my_vote: Option<i32>,
401 pub subscribed: Option<bool>,
402 pub saved: Option<bool>,
403 pub recipient_id: i32,
406 pub struct ReplyQueryBuilder<'a> {
407 conn: &'a PgConnection,
408 query: super::comment_view::reply_fast_view::BoxedQuery<'a, Pg>,
416 impl<'a> ReplyQueryBuilder<'a> {
417 pub fn create(conn: &'a PgConnection, for_user_id: i32) -> Self {
418 use super::comment_view::reply_fast_view::dsl::*;
420 let query = reply_fast_view.into_boxed();
426 sort: &SortType::New,
433 pub fn sort(mut self, sort: &'a SortType) -> Self {
438 pub fn unread_only(mut self, unread_only: bool) -> Self {
439 self.unread_only = unread_only;
443 pub fn page<T: MaybeOptional<i64>>(mut self, page: T) -> Self {
444 self.page = page.get_optional();
448 pub fn limit<T: MaybeOptional<i64>>(mut self, limit: T) -> Self {
449 self.limit = limit.get_optional();
453 pub fn list(self) -> Result<Vec<ReplyView>, Error> {
454 use super::comment_view::reply_fast_view::dsl::*;
456 let mut query = self.query;
459 .filter(user_id.eq(self.for_user_id))
460 .filter(recipient_id.eq(self.for_user_id))
461 .filter(deleted.eq(false))
462 .filter(removed.eq(false));
464 if self.unread_only {
465 query = query.filter(read.eq(false));
468 query = match self.sort {
469 // SortType::Hot => query.order_by(hot_rank.desc()), // TODO why is this commented
470 SortType::New => query.order_by(published.desc()),
471 SortType::TopAll => query.order_by(score.desc()),
472 SortType::TopYear => query
473 .filter(published.gt(now - 1.years()))
474 .order_by(score.desc()),
475 SortType::TopMonth => query
476 .filter(published.gt(now - 1.months()))
477 .order_by(score.desc()),
478 SortType::TopWeek => query
479 .filter(published.gt(now - 1.weeks()))
480 .order_by(score.desc()),
481 SortType::TopDay => query
482 .filter(published.gt(now - 1.days()))
483 .order_by(score.desc()),
484 _ => query.order_by(published.desc()),
487 let (limit, offset) = limit_and_offset(self.page, self.limit);
491 .load::<ReplyView>(self.conn)
502 tests::establish_unpooled_connection,
511 let conn = establish_unpooled_connection();
513 let new_user = UserForm {
514 name: "timmy".into(),
515 preferred_username: None,
516 password_encrypted: "nope".into(),
518 matrix_user_id: None,
525 theme: "darkly".into(),
526 default_sort_type: SortType::Hot as i16,
527 default_listing_type: ListingType::Subscribed as i16,
528 lang: "browser".into(),
530 send_notifications_to_email: false,
536 last_refreshed_at: None,
539 let inserted_user = User_::create(&conn, &new_user).unwrap();
541 let new_community = CommunityForm {
542 name: "test community 5".to_string(),
543 title: "nada".to_owned(),
546 creator_id: inserted_user.id,
555 last_refreshed_at: None,
561 let inserted_community = Community::create(&conn, &new_community).unwrap();
563 let new_post = PostForm {
564 name: "A test post 2".into(),
565 creator_id: inserted_user.id,
568 community_id: inserted_community.id,
576 embed_description: None,
584 let inserted_post = Post::create(&conn, &new_post).unwrap();
586 let comment_form = CommentForm {
587 content: "A test comment 32".into(),
588 creator_id: inserted_user.id,
589 post_id: inserted_post.id,
600 let inserted_comment = Comment::create(&conn, &comment_form).unwrap();
602 let comment_like_form = CommentLikeForm {
603 comment_id: inserted_comment.id,
604 post_id: inserted_post.id,
605 user_id: inserted_user.id,
609 let _inserted_comment_like = CommentLike::like(&conn, &comment_like_form).unwrap();
611 let expected_comment_view_no_user = CommentView {
612 id: inserted_comment.id,
613 content: "A test comment 32".into(),
614 creator_id: inserted_user.id,
615 post_id: inserted_post.id,
616 post_name: inserted_post.name.to_owned(),
617 community_id: inserted_community.id,
618 community_name: inserted_community.name.to_owned(),
619 community_icon: None,
625 banned_from_community: false,
626 published: inserted_comment.published,
628 creator_name: inserted_user.name.to_owned(),
629 creator_preferred_username: None,
630 creator_published: inserted_user.published,
631 creator_avatar: None,
641 ap_id: inserted_comment.ap_id.to_owned(),
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 expected_comment_view_with_user = CommentView {
650 id: inserted_comment.id,
651 content: "A test comment 32".into(),
652 creator_id: inserted_user.id,
653 post_id: inserted_post.id,
654 post_name: inserted_post.name.to_owned(),
655 community_id: inserted_community.id,
656 community_name: inserted_community.name.to_owned(),
657 community_icon: None,
663 banned_from_community: false,
664 published: inserted_comment.published,
666 creator_name: inserted_user.name.to_owned(),
667 creator_preferred_username: None,
668 creator_published: inserted_user.published,
669 creator_avatar: None,
675 user_id: Some(inserted_user.id),
677 subscribed: Some(false),
679 ap_id: inserted_comment.ap_id.to_owned(),
681 community_actor_id: inserted_community.actor_id.to_owned(),
682 community_local: true,
683 creator_actor_id: inserted_user.actor_id.to_owned(),
687 let mut read_comment_views_no_user = CommentQueryBuilder::create(&conn)
688 .for_post_id(inserted_post.id)
691 read_comment_views_no_user[0].hot_rank = 0;
692 read_comment_views_no_user[0].hot_rank_active = 0;
694 let mut read_comment_views_with_user = CommentQueryBuilder::create(&conn)
695 .for_post_id(inserted_post.id)
696 .my_user_id(inserted_user.id)
699 read_comment_views_with_user[0].hot_rank = 0;
700 read_comment_views_with_user[0].hot_rank_active = 0;
702 let like_removed = CommentLike::remove(&conn, inserted_user.id, inserted_comment.id).unwrap();
703 let num_deleted = Comment::delete(&conn, inserted_comment.id).unwrap();
704 Post::delete(&conn, inserted_post.id).unwrap();
705 Community::delete(&conn, inserted_community.id).unwrap();
706 User_::delete(&conn, inserted_user.id).unwrap();
708 assert_eq!(expected_comment_view_no_user, read_comment_views_no_user[0]);
710 expected_comment_view_with_user,
711 read_comment_views_with_user[0]
713 assert_eq!(1, num_deleted);
714 assert_eq!(1, like_removed);