2 aggregates::post_aggregates::PostAggregates,
18 community::{Community, CommunityFollower, CommunitySafe, CommunityUserBan},
19 post::{Post, PostRead, PostSaved},
20 user::{UserSafe, User_},
28 use diesel::{result::Error, *};
31 #[derive(Debug, PartialEq, Serialize, Clone)]
34 pub creator: UserSafe,
35 pub community: CommunitySafe,
36 pub counts: PostAggregates,
37 pub subscribed: bool, // Left join to CommunityFollower
38 pub creator_banned_from_community: bool, // Left Join to CommunityUserBan
39 pub saved: bool, // Left join to PostSaved
40 pub read: bool, // Left join to PostRead
41 pub my_vote: Option<i16>, // Left join to PostLike
44 type PostViewTuple = (
48 Option<CommunityUserBan>,
50 Option<CommunityFollower>,
57 pub fn read(conn: &PgConnection, post_id: i32, my_user_id: Option<i32>) -> Result<Self, Error> {
58 // The left join below will return None in this case
59 let user_id_join = my_user_id.unwrap_or(-1);
65 creator_banned_from_community,
73 .inner_join(user_::table)
74 .inner_join(community::table)
76 community_user_ban::table.on(
78 .eq(community_user_ban::community_id)
79 .and(community_user_ban::user_id.eq(community::creator_id)),
82 .inner_join(post_aggregates::table)
84 community_follower::table.on(
86 .eq(community_follower::community_id)
87 .and(community_follower::user_id.eq(user_id_join)),
93 .eq(post_saved::post_id)
94 .and(post_saved::user_id.eq(user_id_join)),
100 .eq(post_read::post_id)
101 .and(post_read::user_id.eq(user_id_join)),
107 .eq(post_like::post_id)
108 .and(post_like::user_id.eq(user_id_join)),
113 User_::safe_columns_tuple(),
114 Community::safe_columns_tuple(),
115 community_user_ban::all_columns.nullable(),
116 post_aggregates::all_columns,
117 community_follower::all_columns.nullable(),
118 post_saved::all_columns.nullable(),
119 post_read::all_columns.nullable(),
120 post_like::score.nullable(),
122 .first::<PostViewTuple>(conn)?;
128 creator_banned_from_community: creator_banned_from_community.is_some(),
130 subscribed: follower.is_some(),
131 saved: saved.is_some(),
132 read: read.is_some(),
152 query_builder::BoxedSelectStatement,
153 query_source::joins::{Inner, Join, JoinOn, LeftOuter},
157 // /// TODO awful, but necessary because of the boxed join
158 pub(super) type BoxedPostJoin<'a> = BoxedSelectStatement<
215 Nullable<(Integer, Integer, Integer, Timestamp)>,
216 (Integer, Integer, BigInt, BigInt, BigInt, BigInt, Timestamp),
217 Nullable<(Integer, Integer, Integer, Timestamp, Nullable<Bool>)>,
218 Nullable<(Integer, Integer, Integer, Timestamp)>,
219 Nullable<(Integer, Integer, Integer, Timestamp)>,
237 Join<post::table, user_::table, Inner>,
238 diesel::expression::operators::Eq<
239 diesel::expression::nullable::Nullable<
240 post::columns::creator_id,
242 diesel::expression::nullable::Nullable<user_::columns::id>,
248 diesel::expression::operators::Eq<
249 diesel::expression::nullable::Nullable<post::columns::community_id>,
250 diesel::expression::nullable::Nullable<community::columns::id>,
253 community_user_ban::table,
256 diesel::expression::operators::And<
257 diesel::expression::operators::Eq<
258 post::columns::community_id,
259 community_user_ban::columns::community_id,
261 diesel::expression::operators::Eq<
262 community_user_ban::columns::user_id,
263 community::columns::creator_id,
267 post_aggregates::table,
270 diesel::expression::operators::Eq<
271 diesel::expression::nullable::Nullable<post_aggregates::columns::post_id>,
272 diesel::expression::nullable::Nullable<post::columns::id>,
275 community_follower::table,
278 diesel::expression::operators::And<
279 diesel::expression::operators::Eq<
280 post::columns::community_id,
281 community_follower::columns::community_id,
283 diesel::expression::operators::Eq<
284 community_follower::columns::user_id,
285 diesel::expression::bound::Bound<Integer, i32>,
292 diesel::expression::operators::And<
293 diesel::expression::operators::Eq<post::columns::id, post_saved::columns::post_id>,
294 diesel::expression::operators::Eq<
295 post_saved::columns::user_id,
296 diesel::expression::bound::Bound<Integer, i32>,
303 diesel::expression::operators::And<
304 diesel::expression::operators::Eq<post::columns::id, post_read::columns::post_id>,
305 diesel::expression::operators::Eq<
306 post_read::columns::user_id,
307 diesel::expression::bound::Bound<Integer, i32>,
314 diesel::expression::operators::And<
315 diesel::expression::operators::Eq<post::columns::id, post_like::columns::post_id>,
316 diesel::expression::operators::Eq<
317 post_like::columns::user_id,
318 diesel::expression::bound::Bound<Integer, i32>,
326 pub struct PostQueryBuilder<'a> {
327 conn: &'a PgConnection,
328 query: join_types::BoxedPostJoin<'a>,
329 listing_type: &'a ListingType,
331 for_creator_id: Option<i32>,
332 for_community_id: Option<i32>,
333 for_community_name: Option<String>,
334 search_term: Option<String>,
335 url_search: Option<String>,
343 impl<'a> PostQueryBuilder<'a> {
344 pub fn create(conn: &'a PgConnection, my_user_id: Option<i32>) -> Self {
345 // The left join below will return None in this case
346 let user_id_join = my_user_id.unwrap_or(-1);
348 let query = post::table
349 .inner_join(user_::table)
350 .inner_join(community::table)
352 community_user_ban::table.on(
354 .eq(community_user_ban::community_id)
355 .and(community_user_ban::user_id.eq(community::creator_id)),
358 .inner_join(post_aggregates::table)
360 community_follower::table.on(
362 .eq(community_follower::community_id)
363 .and(community_follower::user_id.eq(user_id_join)),
367 post_saved::table.on(
369 .eq(post_saved::post_id)
370 .and(post_saved::user_id.eq(user_id_join)),
376 .eq(post_read::post_id)
377 .and(post_read::user_id.eq(user_id_join)),
383 .eq(post_like::post_id)
384 .and(post_like::user_id.eq(user_id_join)),
389 User_::safe_columns_tuple(),
390 Community::safe_columns_tuple(),
391 community_user_ban::all_columns.nullable(),
392 post_aggregates::all_columns,
393 community_follower::all_columns.nullable(),
394 post_saved::all_columns.nullable(),
395 post_read::all_columns.nullable(),
396 post_like::score.nullable(),
403 listing_type: &ListingType::All,
404 sort: &SortType::Hot,
405 for_creator_id: None,
406 for_community_id: None,
407 for_community_name: None,
418 pub fn listing_type(mut self, listing_type: &'a ListingType) -> Self {
419 self.listing_type = listing_type;
423 pub fn sort(mut self, sort: &'a SortType) -> Self {
428 pub fn for_community_id<T: MaybeOptional<i32>>(mut self, for_community_id: T) -> Self {
429 self.for_community_id = for_community_id.get_optional();
433 pub fn for_community_name<T: MaybeOptional<String>>(mut self, for_community_name: T) -> Self {
434 self.for_community_name = for_community_name.get_optional();
438 pub fn for_creator_id<T: MaybeOptional<i32>>(mut self, for_creator_id: T) -> Self {
439 self.for_creator_id = for_creator_id.get_optional();
443 pub fn search_term<T: MaybeOptional<String>>(mut self, search_term: T) -> Self {
444 self.search_term = search_term.get_optional();
448 pub fn url_search<T: MaybeOptional<String>>(mut self, url_search: T) -> Self {
449 self.url_search = url_search.get_optional();
453 pub fn show_nsfw(mut self, show_nsfw: bool) -> Self {
454 self.show_nsfw = show_nsfw;
458 pub fn saved_only(mut self, saved_only: bool) -> Self {
459 self.saved_only = saved_only;
463 pub fn page<T: MaybeOptional<i64>>(mut self, page: T) -> Self {
464 self.page = page.get_optional();
468 pub fn limit<T: MaybeOptional<i64>>(mut self, limit: T) -> Self {
469 self.limit = limit.get_optional();
473 pub fn list(self) -> Result<Vec<PostView>, Error> {
476 let mut query = self.query;
478 query = match self.listing_type {
479 ListingType::Subscribed => query.filter(community_follower::user_id.is_not_null()), // TODO could be this: and(community_follower::user_id.eq(user_id_join)),
480 ListingType::Local => query.filter(community::local.eq(true)),
484 if let Some(for_community_id) = self.for_community_id {
486 .filter(post::community_id.eq(for_community_id))
487 .then_order_by(post::stickied.desc());
490 if let Some(for_community_name) = self.for_community_name {
492 .filter(community::name.eq(for_community_name))
493 .filter(community::local.eq(true))
494 .then_order_by(post::stickied.desc());
497 if let Some(url_search) = self.url_search {
498 query = query.filter(post::url.eq(url_search));
501 if let Some(search_term) = self.search_term {
502 let searcher = fuzzy_search(&search_term);
503 query = query.filter(
505 .ilike(searcher.to_owned())
506 .or(post::body.ilike(searcher)),
510 query = match self.sort {
511 SortType::Active => query
513 hot_rank(post_aggregates::score, post_aggregates::newest_comment_time).desc(),
515 .then_order_by(post::published.desc()),
516 SortType::Hot => query
517 .then_order_by(hot_rank(post_aggregates::score, post::published).desc())
518 .then_order_by(post::published.desc()),
519 SortType::New => query.then_order_by(post::published.desc()),
520 SortType::TopAll => query.then_order_by(post_aggregates::score.desc()),
521 SortType::TopYear => query
522 .filter(post::published.gt(now - 1.years()))
523 .then_order_by(post_aggregates::score.desc()),
524 SortType::TopMonth => query
525 .filter(post::published.gt(now - 1.months()))
526 .then_order_by(post_aggregates::score.desc()),
527 SortType::TopWeek => query
528 .filter(post::published.gt(now - 1.weeks()))
529 .then_order_by(post_aggregates::score.desc()),
530 SortType::TopDay => query
531 .filter(post::published.gt(now - 1.days()))
532 .then_order_by(post_aggregates::score.desc()),
535 // If its for a specific user, show the removed / deleted
536 if let Some(for_creator_id) = self.for_creator_id {
537 query = query.filter(post::creator_id.eq(for_creator_id));
542 .filter(post::nsfw.eq(false))
543 .filter(community::nsfw.eq(false));
546 // TODO These two might be wrong
548 query = query.filter(post_saved::id.is_not_null());
551 if self.unread_only {
552 query = query.filter(post_read::id.is_not_null());
555 let (limit, offset) = limit_and_offset(self.page, self.limit);
560 .filter(post::removed.eq(false))
561 .filter(post::deleted.eq(false))
562 .filter(community::removed.eq(false))
563 .filter(community::deleted.eq(false))
564 .load::<PostViewTuple>(self.conn)?;
566 Ok(PostView::to_vec(res))
570 impl ViewToVec for PostView {
571 type DbTuple = PostViewTuple;
572 fn to_vec(posts: Vec<Self::DbTuple>) -> Vec<Self> {
576 post: a.0.to_owned(),
577 creator: a.1.to_owned(),
578 community: a.2.to_owned(),
579 creator_banned_from_community: a.3.is_some(),
580 counts: a.4.to_owned(),
581 subscribed: a.5.is_some(),
582 saved: a.6.is_some(),
586 .collect::<Vec<Self>>()
593 aggregates::post_aggregates::PostAggregates,
594 source::{community::*, post::*, user::*},
595 tests::establish_unpooled_connection,
596 views::post_view::{PostQueryBuilder, PostView},
604 let conn = establish_unpooled_connection();
606 let user_name = "tegan".to_string();
607 let community_name = "test_community_3".to_string();
608 let post_name = "test post 3".to_string();
610 let new_user = UserForm {
611 name: user_name.to_owned(),
612 preferred_username: None,
613 password_encrypted: "nope".into(),
615 matrix_user_id: None,
623 theme: "browser".into(),
624 default_sort_type: SortType::Hot as i16,
625 default_listing_type: ListingType::Subscribed as i16,
626 lang: "browser".into(),
628 send_notifications_to_email: false,
634 last_refreshed_at: None,
637 let inserted_user = User_::create(&conn, &new_user).unwrap();
639 let new_community = CommunityForm {
640 name: community_name.to_owned(),
641 title: "nada".to_owned(),
643 creator_id: inserted_user.id,
653 last_refreshed_at: None,
659 let inserted_community = Community::create(&conn, &new_community).unwrap();
661 let new_post = PostForm {
662 name: post_name.to_owned(),
665 creator_id: inserted_user.id,
666 community_id: inserted_community.id,
674 embed_description: None,
682 let inserted_post = Post::create(&conn, &new_post).unwrap();
684 let post_like_form = PostLikeForm {
685 post_id: inserted_post.id,
686 user_id: inserted_user.id,
690 let inserted_post_like = PostLike::like(&conn, &post_like_form).unwrap();
692 let expected_post_like = PostLike {
693 id: inserted_post_like.id,
694 post_id: inserted_post.id,
695 user_id: inserted_user.id,
696 published: inserted_post_like.published,
700 let read_post_listings_with_user = PostQueryBuilder::create(&conn, Some(inserted_user.id))
701 .listing_type(&ListingType::Community)
702 .sort(&SortType::New)
703 .for_community_id(inserted_community.id)
707 let read_post_listings_no_user = PostQueryBuilder::create(&conn, None)
708 .listing_type(&ListingType::Community)
709 .sort(&SortType::New)
710 .for_community_id(inserted_community.id)
714 let read_post_listing_no_user = PostView::read(&conn, inserted_post.id, None).unwrap();
715 let read_post_listing_with_user =
716 PostView::read(&conn, inserted_post.id, Some(inserted_user.id)).unwrap();
718 // the non user version
719 let expected_post_listing_no_user = PostView {
721 id: inserted_post.id,
722 name: post_name.to_owned(),
723 creator_id: inserted_user.id,
726 published: inserted_post.published,
728 community_id: inserted_community.id,
735 embed_description: None,
738 ap_id: inserted_post.ap_id.to_owned(),
743 id: inserted_user.id,
744 name: user_name.to_owned(),
745 preferred_username: None,
746 published: inserted_user.published,
748 actor_id: inserted_user.actor_id.to_owned(),
756 matrix_user_id: None,
758 creator_banned_from_community: false,
759 community: CommunitySafe {
760 id: inserted_community.id,
761 name: community_name.to_owned(),
766 actor_id: inserted_community.actor_id.to_owned(),
768 title: "nada".to_owned(),
770 creator_id: inserted_user.id,
774 published: inserted_community.published,
776 counts: PostAggregates {
777 id: inserted_post.id, // TODO this might fail
778 post_id: inserted_post.id,
783 newest_comment_time: inserted_post.published,
790 // TODO More needs to be added here
791 let mut expected_post_listing_with_user = expected_post_listing_no_user.to_owned();
792 expected_post_listing_with_user.my_vote = Some(1);
794 let like_removed = PostLike::remove(&conn, inserted_user.id, inserted_post.id).unwrap();
795 let num_deleted = Post::delete(&conn, inserted_post.id).unwrap();
796 Community::delete(&conn, inserted_community.id).unwrap();
797 User_::delete(&conn, inserted_user.id).unwrap();
801 expected_post_listing_with_user,
802 read_post_listings_with_user[0]
804 assert_eq!(expected_post_listing_with_user, read_post_listing_with_user);
805 assert_eq!(1, read_post_listings_with_user.len());
808 assert_eq!(expected_post_listing_no_user, read_post_listings_no_user[0]);
809 assert_eq!(expected_post_listing_no_user, read_post_listing_no_user);
810 assert_eq!(1, read_post_listings_no_user.len());
812 // assert_eq!(expected_post, inserted_post);
813 // assert_eq!(expected_post, updated_post);
814 assert_eq!(expected_post_like, inserted_post_like);
815 assert_eq!(1, like_removed);
816 assert_eq!(1, num_deleted);