From 9a5a13c734a1792511e1bfef7b9ac4121e0e7371 Mon Sep 17 00:00:00 2001 From: dullbananas <dull.bananas0@gmail.com> Date: Fri, 28 Jul 2023 01:36:50 -0700 Subject: [PATCH] Use same table join code for both read and list functions (#3663) * Try stuff * Revert "Try stuff" This reverts commit 3da5f83a8b4928368bf58e0061091c270259a226. * Revert "Revert "Try stuff"" This reverts commit 178bd43cac8c7674d30d2c285ed47ca0493ad659. * Revert "Revert "Revert "Try stuff""" This reverts commit b9f9a2316e7cd37082319608c606a1c7db057206. * Revert "Revert "Revert "Revert "Try stuff"""" This reverts commit ccd498dd7228050ae05d2022e9106034fd4132f8. * Try more stuff * Add queries function * Simplify queries function * Move aliases to db_schema * Revert "Move aliases to db_schema" This reverts commit 69afed05c1807c3fef8d5b5872546fa22e60b4d0. * Add ReadFuture and ListFuture * Refactor queries function and add Queries struct * Box futures in Queries::new * Use from_tuple * Add comment_view::queries and improve comment_report_view::queries * Add local_user_view::queries * Add post_report_view::queries * Ad post_view::queries * Add private_message_report_view::queries * private_message_view, registration_application_view * Use 'a in BoxedQuery * comment_reply_view, community_view * Change aliases to inline module * person_mention_view * person_view * Use separate community_person_ban joins instead of including boolean literal in join-on clause * Fix comment_view * rerun ci --- crates/db_schema/src/lib.rs | 5 + crates/db_schema/src/utils.rs | 91 +++++- crates/db_views/src/comment_report_view.rs | 234 +++++++------- crates/db_views/src/comment_view.rs | 262 ++++++--------- crates/db_views/src/local_user_view.rs | 178 +++++------ crates/db_views/src/post_report_view.rs | 190 +++++------ crates/db_views/src/post_view.rs | 302 +++++++----------- .../src/private_message_report_view.rs | 138 ++++---- crates/db_views/src/private_message_view.rs | 143 ++++----- .../src/registration_application_view.rs | 135 ++++---- .../db_views_actor/src/comment_reply_view.rs | 207 ++++-------- crates/db_views_actor/src/community_view.rs | 211 ++++++------ .../db_views_actor/src/person_mention_view.rs | 267 ++++++---------- crates/db_views_actor/src/person_view.rs | 139 ++++---- 14 files changed, 1107 insertions(+), 1395 deletions(-) diff --git a/crates/db_schema/src/lib.rs b/crates/db_schema/src/lib.rs index e5b86fe1..e3232c40 100644 --- a/crates/db_schema/src/lib.rs +++ b/crates/db_schema/src/lib.rs @@ -28,6 +28,11 @@ pub mod newtypes; #[rustfmt::skip] #[allow(clippy::wildcard_imports)] pub mod schema; +#[cfg(feature = "full")] +pub mod aliases { + use crate::schema::person; + diesel::alias!(person as person1: Person1, person as person2: Person2); +} pub mod source; #[cfg(feature = "full")] pub mod traits; diff --git a/crates/db_schema/src/utils.rs b/crates/db_schema/src/utils.rs index 7e8204de..dc26bedf 100644 --- a/crates/db_schema/src/utils.rs +++ b/crates/db_schema/src/utils.rs @@ -2,6 +2,7 @@ use crate::{ diesel::Connection, diesel_migrations::MigrationHarness, newtypes::DbUrl, + traits::JoinView, CommentSortType, PersonSortType, SortType, @@ -26,7 +27,7 @@ use diesel_async::{ }, }; use diesel_migrations::EmbeddedMigrations; -use futures_util::{future::BoxFuture, FutureExt}; +use futures_util::{future::BoxFuture, Future, FutureExt}; use lemmy_utils::{ error::{LemmyError, LemmyErrorExt, LemmyErrorType}, settings::structs::Settings, @@ -420,6 +421,94 @@ where } } +pub type ResultFuture<'a, T> = BoxFuture<'a, Result<T, DieselError>>; + +pub trait ReadFn<'a, T: JoinView, Args>: + Fn(DbConn<'a>, Args) -> ResultFuture<'a, <T as JoinView>::JoinTuple> +{ +} + +impl< + 'a, + T: JoinView, + Args, + F: Fn(DbConn<'a>, Args) -> ResultFuture<'a, <T as JoinView>::JoinTuple>, + > ReadFn<'a, T, Args> for F +{ +} + +pub trait ListFn<'a, T: JoinView, Args>: + Fn(DbConn<'a>, Args) -> ResultFuture<'a, Vec<<T as JoinView>::JoinTuple>> +{ +} + +impl< + 'a, + T: JoinView, + Args, + F: Fn(DbConn<'a>, Args) -> ResultFuture<'a, Vec<<T as JoinView>::JoinTuple>>, + > ListFn<'a, T, Args> for F +{ +} + +/// Allows read and list functions to capture a shared closure that has an inferred return type, which is useful for join logic +pub struct Queries<RF, LF> { + pub read_fn: RF, + pub list_fn: LF, +} + +// `()` is used to prevent type inference error +impl Queries<(), ()> { + pub fn new<'a, RFut, LFut, RT, LT, RA, LA, RF2, LF2>( + read_fn: RF2, + list_fn: LF2, + ) -> Queries<impl ReadFn<'a, RT, RA>, impl ListFn<'a, LT, LA>> + where + RFut: Future<Output = Result<<RT as JoinView>::JoinTuple, DieselError>> + Sized + Send + 'a, + LFut: + Future<Output = Result<Vec<<LT as JoinView>::JoinTuple>, DieselError>> + Sized + Send + 'a, + RT: JoinView, + LT: JoinView, + RF2: Fn(DbConn<'a>, RA) -> RFut, + LF2: Fn(DbConn<'a>, LA) -> LFut, + { + Queries { + read_fn: move |conn, args| read_fn(conn, args).boxed(), + list_fn: move |conn, args| list_fn(conn, args).boxed(), + } + } +} + +impl<RF, LF> Queries<RF, LF> { + pub async fn read<'a, T, Args>( + self, + pool: &'a mut DbPool<'_>, + args: Args, + ) -> Result<T, DieselError> + where + T: JoinView, + RF: ReadFn<'a, T, Args>, + { + let conn = get_conn(pool).await?; + let res = (self.read_fn)(conn, args).await?; + Ok(T::from_tuple(res)) + } + + pub async fn list<'a, T, Args>( + self, + pool: &'a mut DbPool<'_>, + args: Args, + ) -> Result<Vec<T>, DieselError> + where + T: JoinView, + LF: ListFn<'a, T, Args>, + { + let conn = get_conn(pool).await?; + let res = (self.list_fn)(conn, args).await?; + Ok(res.into_iter().map(T::from_tuple).collect()) + } +} + #[cfg(test)] mod tests { #![allow(clippy::unwrap_used)] diff --git a/crates/db_views/src/comment_report_view.rs b/crates/db_views/src/comment_report_view.rs index 0b1821c1..a92b3806 100644 --- a/crates/db_views/src/comment_report_view.rs +++ b/crates/db_views/src/comment_report_view.rs @@ -1,6 +1,7 @@ use crate::structs::CommentReportView; use diesel::{ dsl::now, + pg::Pg, result::Error, BoolExpressionMethods, ExpressionMethods, @@ -11,6 +12,7 @@ use diesel::{ use diesel_async::RunQueryDsl; use lemmy_db_schema::{ aggregates::structs::CommentAggregates, + aliases, newtypes::{CommentReportId, CommunityId, PersonId}, schema::{ comment, @@ -31,39 +33,23 @@ use lemmy_db_schema::{ post::Post, }, traits::JoinView, - utils::{get_conn, limit_and_offset, DbPool}, + utils::{get_conn, limit_and_offset, DbConn, DbPool, ListFn, Queries, ReadFn}, }; -impl CommentReportView { - /// returns the CommentReportView for the provided report_id - /// - /// * `report_id` - the report id to obtain - pub async fn read( - pool: &mut DbPool<'_>, - report_id: CommentReportId, - my_person_id: PersonId, - ) -> Result<Self, Error> { - let conn = &mut get_conn(pool).await?; - - let (person_alias_1, person_alias_2) = diesel::alias!(person as person1, person as person2); - - let res = comment_report::table - .find(report_id) +fn queries<'a>() -> Queries< + impl ReadFn<'a, CommentReportView, (CommentReportId, PersonId)>, + impl ListFn<'a, CommentReportView, (CommentReportQuery, &'a Person)>, +> { + let all_joins = |query: comment_report::BoxedQuery<'a, Pg>, my_person_id: PersonId| { + query .inner_join(comment::table) .inner_join(post::table.on(comment::post_id.eq(post::id))) .inner_join(community::table.on(post::community_id.eq(community::id))) .inner_join(person::table.on(comment_report::creator_id.eq(person::id))) - .inner_join(person_alias_1.on(comment::creator_id.eq(person_alias_1.field(person::id)))) + .inner_join(aliases::person1.on(comment::creator_id.eq(aliases::person1.field(person::id)))) .inner_join( comment_aggregates::table.on(comment_report::comment_id.eq(comment_aggregates::comment_id)), ) - .left_join( - community_person_ban::table.on( - community::id - .eq(community_person_ban::community_id) - .and(community_person_ban::person_id.eq(comment::creator_id)), - ), - ) .left_join( comment_like::table.on( comment::id @@ -72,25 +58,104 @@ impl CommentReportView { ), ) .left_join( - person_alias_2 - .on(comment_report::resolver_id.eq(person_alias_2.field(person::id).nullable())), + aliases::person2 + .on(comment_report::resolver_id.eq(aliases::person2.field(person::id).nullable())), + ) + }; + + let selection = ( + comment_report::all_columns, + comment::all_columns, + post::all_columns, + community::all_columns, + person::all_columns, + aliases::person1.fields(person::all_columns), + comment_aggregates::all_columns, + community_person_ban::all_columns.nullable(), + comment_like::score.nullable(), + aliases::person2.fields(person::all_columns).nullable(), + ); + + let read = move |mut conn: DbConn<'a>, (report_id, my_person_id): (CommentReportId, PersonId)| async move { + all_joins( + comment_report::table.find(report_id).into_boxed(), + my_person_id, + ) + .left_join( + community_person_ban::table.on( + community::id + .eq(community_person_ban::community_id) + .and(community_person_ban::person_id.eq(comment::creator_id)), + ), + ) + .select(selection) + .first::<<CommentReportView as JoinView>::JoinTuple>(&mut conn) + .await + }; + + let list = move |mut conn: DbConn<'a>, (options, my_person): (CommentReportQuery, &'a Person)| async move { + let mut query = all_joins(comment_report::table.into_boxed(), my_person.id) + .left_join( + community_person_ban::table.on( + community::id + .eq(community_person_ban::community_id) + .and(community_person_ban::person_id.eq(comment::creator_id)) + .and( + community_person_ban::expires + .is_null() + .or(community_person_ban::expires.gt(now)), + ), + ), ) - .select(( - comment_report::all_columns, - comment::all_columns, - post::all_columns, - community::all_columns, - person::all_columns, - person_alias_1.fields(person::all_columns), - comment_aggregates::all_columns, - community_person_ban::all_columns.nullable(), - comment_like::score.nullable(), - person_alias_2.fields(person::all_columns).nullable(), - )) - .first::<<CommentReportView as JoinView>::JoinTuple>(conn) - .await?; - - Ok(Self::from_tuple(res)) + .select(selection); + + if let Some(community_id) = options.community_id { + query = query.filter(post::community_id.eq(community_id)); + } + + if options.unresolved_only.unwrap_or(false) { + query = query.filter(comment_report::resolved.eq(false)); + } + + let (limit, offset) = limit_and_offset(options.page, options.limit)?; + + query = query + .order_by(comment_report::published.desc()) + .limit(limit) + .offset(offset); + + // If its not an admin, get only the ones you mod + if !my_person.admin { + query + .inner_join( + community_moderator::table.on( + community_moderator::community_id + .eq(post::community_id) + .and(community_moderator::person_id.eq(my_person.id)), + ), + ) + .load::<<CommentReportView as JoinView>::JoinTuple>(&mut conn) + .await + } else { + query + .load::<<CommentReportView as JoinView>::JoinTuple>(&mut conn) + .await + } + }; + + Queries::new(read, list) +} + +impl CommentReportView { + /// returns the CommentReportView for the provided report_id + /// + /// * `report_id` - the report id to obtain + pub async fn read( + pool: &mut DbPool<'_>, + report_id: CommentReportId, + my_person_id: PersonId, + ) -> Result<Self, Error> { + queries().read(pool, (report_id, my_person_id)).await } /// Returns the current unresolved post report count for the communities you mod @@ -150,90 +215,7 @@ impl CommentReportQuery { pool: &mut DbPool<'_>, my_person: &Person, ) -> Result<Vec<CommentReportView>, Error> { - let conn = &mut get_conn(pool).await?; - - let (person_alias_1, person_alias_2) = diesel::alias!(person as person1, person as person2); - - let mut query = comment_report::table - .inner_join(comment::table) - .inner_join(post::table.on(comment::post_id.eq(post::id))) - .inner_join(community::table.on(post::community_id.eq(community::id))) - .inner_join(person::table.on(comment_report::creator_id.eq(person::id))) - .inner_join(person_alias_1.on(comment::creator_id.eq(person_alias_1.field(person::id)))) - .inner_join( - comment_aggregates::table.on(comment_report::comment_id.eq(comment_aggregates::comment_id)), - ) - .left_join( - community_person_ban::table.on( - community::id - .eq(community_person_ban::community_id) - .and(community_person_ban::person_id.eq(comment::creator_id)) - .and( - community_person_ban::expires - .is_null() - .or(community_person_ban::expires.gt(now)), - ), - ), - ) - .left_join( - comment_like::table.on( - comment::id - .eq(comment_like::comment_id) - .and(comment_like::person_id.eq(my_person.id)), - ), - ) - .left_join( - person_alias_2 - .on(comment_report::resolver_id.eq(person_alias_2.field(person::id).nullable())), - ) - .select(( - comment_report::all_columns, - comment::all_columns, - post::all_columns, - community::all_columns, - person::all_columns, - person_alias_1.fields(person::all_columns), - comment_aggregates::all_columns, - community_person_ban::all_columns.nullable(), - comment_like::score.nullable(), - person_alias_2.fields(person::all_columns).nullable(), - )) - .into_boxed(); - - if let Some(community_id) = self.community_id { - query = query.filter(post::community_id.eq(community_id)); - } - - if self.unresolved_only.unwrap_or(false) { - query = query.filter(comment_report::resolved.eq(false)); - } - - let (limit, offset) = limit_and_offset(self.page, self.limit)?; - - query = query - .order_by(comment_report::published.desc()) - .limit(limit) - .offset(offset); - - // If its not an admin, get only the ones you mod - let res = if !my_person.admin { - query - .inner_join( - community_moderator::table.on( - community_moderator::community_id - .eq(post::community_id) - .and(community_moderator::person_id.eq(my_person.id)), - ), - ) - .load::<<CommentReportView as JoinView>::JoinTuple>(conn) - .await? - } else { - query - .load::<<CommentReportView as JoinView>::JoinTuple>(conn) - .await? - }; - - Ok(res.into_iter().map(CommentReportView::from_tuple).collect()) + queries().list(pool, (self, my_person)).await } } diff --git a/crates/db_views/src/comment_view.rs b/crates/db_views/src/comment_view.rs index 2d233438..26787cce 100644 --- a/crates/db_views/src/comment_view.rs +++ b/crates/db_views/src/comment_view.rs @@ -1,5 +1,6 @@ use crate::structs::{CommentView, LocalUserView}; use diesel::{ + pg::Pg, result::Error, BoolExpressionMethods, ExpressionMethods, @@ -35,7 +36,7 @@ use lemmy_db_schema::{ post::Post, }, traits::JoinView, - utils::{fuzzy_search, get_conn, limit_and_offset, DbPool}, + utils::{fuzzy_search, limit_and_offset, DbConn, DbPool, ListFn, Queries, ReadFn}, CommentSortType, ListingType, }; @@ -53,30 +54,14 @@ type CommentViewTuple = ( Option<i16>, ); -impl CommentView { - pub async fn read( - pool: &mut DbPool<'_>, - comment_id: CommentId, - my_person_id: Option<PersonId>, - ) -> Result<Self, Error> { - let conn = &mut get_conn(pool).await?; - +fn queries<'a>() -> Queries< + impl ReadFn<'a, CommentView, (CommentId, Option<PersonId>)>, + impl ListFn<'a, CommentView, CommentQuery<'a>>, +> { + let all_joins = |query: comment::BoxedQuery<'a, Pg>, my_person_id: Option<PersonId>| { // The left join below will return None in this case let person_id_join = my_person_id.unwrap_or(PersonId(-1)); - - let ( - comment, - creator, - post, - community, - counts, - creator_banned_from_community, - follower, - saved, - creator_blocked, - comment_like, - ) = comment::table - .find(comment_id) + query .inner_join(person::table) .inner_join(post::table) .inner_join(community::table.on(post::community_id.eq(community::id))) @@ -116,106 +101,38 @@ impl CommentView { .and(comment_like::person_id.eq(person_id_join)), ), ) - .select(( - comment::all_columns, - person::all_columns, - post::all_columns, - community::all_columns, - comment_aggregates::all_columns, - community_person_ban::all_columns.nullable(), - community_follower::all_columns.nullable(), - comment_saved::all_columns.nullable(), - person_block::all_columns.nullable(), - comment_like::score.nullable(), - )) - .first::<CommentViewTuple>(conn) - .await?; - - // If a person is given, then my_vote, if None, should be 0, not null - // Necessary to differentiate between other person's votes - let my_vote = if my_person_id.is_some() && comment_like.is_none() { - Some(0) - } else { - comment_like - }; - - Ok(CommentView { - comment, - post, - creator, - community, - counts, - creator_banned_from_community: creator_banned_from_community.is_some(), - subscribed: CommunityFollower::to_subscribed_type(&follower), - saved: saved.is_some(), - creator_blocked: creator_blocked.is_some(), - my_vote, - }) - } -} + }; -#[derive(Default)] -pub struct CommentQuery<'a> { - pub listing_type: Option<ListingType>, - pub sort: Option<CommentSortType>, - pub community_id: Option<CommunityId>, - pub post_id: Option<PostId>, - pub parent_path: Option<Ltree>, - pub creator_id: Option<PersonId>, - pub local_user: Option<&'a LocalUserView>, - pub search_term: Option<String>, - pub saved_only: Option<bool>, - pub is_profile_view: Option<bool>, - pub show_deleted_and_removed: Option<bool>, - pub page: Option<i64>, - pub limit: Option<i64>, - pub max_depth: Option<i32>, -} + let selection = ( + comment::all_columns, + person::all_columns, + post::all_columns, + community::all_columns, + comment_aggregates::all_columns, + community_person_ban::all_columns.nullable(), + community_follower::all_columns.nullable(), + comment_saved::all_columns.nullable(), + person_block::all_columns.nullable(), + comment_like::score.nullable(), + ); + + let read = move |mut conn: DbConn<'a>, + (comment_id, my_person_id): (CommentId, Option<PersonId>)| async move { + all_joins(comment::table.find(comment_id).into_boxed(), my_person_id) + .select(selection) + .first::<CommentViewTuple>(&mut conn) + .await + }; -impl<'a> CommentQuery<'a> { - pub async fn list(self, pool: &mut DbPool<'_>) -> Result<Vec<CommentView>, Error> { - let conn = &mut get_conn(pool).await?; + let list = move |mut conn: DbConn<'a>, options: CommentQuery<'a>| async move { + let person_id = options.local_user.map(|l| l.person.id); + let local_user_id = options.local_user.map(|l| l.local_user.id); // The left join below will return None in this case - let person_id_join = self.local_user.map(|l| l.person.id).unwrap_or(PersonId(-1)); - let local_user_id_join = self - .local_user - .map(|l| l.local_user.id) - .unwrap_or(LocalUserId(-1)); + let person_id_join = person_id.unwrap_or(PersonId(-1)); + let local_user_id_join = local_user_id.unwrap_or(LocalUserId(-1)); - let mut query = comment::table - .inner_join(person::table) - .inner_join(post::table) - .inner_join(community::table.on(post::community_id.eq(community::id))) - .inner_join(comment_aggregates::table) - .left_join( - community_person_ban::table.on( - community::id - .eq(community_person_ban::community_id) - .and(community_person_ban::person_id.eq(comment::creator_id)), - ), - ) - .left_join( - community_follower::table.on( - post::community_id - .eq(community_follower::community_id) - .and(community_follower::person_id.eq(person_id_join)), - ), - ) - .left_join( - comment_saved::table.on( - comment::id - .eq(comment_saved::comment_id) - .and(comment_saved::person_id.eq(person_id_join)), - ), - ) - .left_join( - person_block::table.on( - comment::creator_id - .eq(person_block::target_id) - .and(person_block::person_id.eq(person_id_join)), - ), - ) + let mut query = all_joins(comment::table.into_boxed(), person_id) .left_join( community_block::table.on( community::id @@ -223,13 +140,6 @@ impl<'a> CommentQuery<'a> { .and(community_block::person_id.eq(person_id_join)), ), ) - .left_join( - comment_like::table.on( - comment::id - .eq(comment_like::comment_id) - .and(comment_like::person_id.eq(person_id_join)), - ), - ) .left_join( local_user_language::table.on( comment::language_id @@ -237,41 +147,29 @@ impl<'a> CommentQuery<'a> { .and(local_user_language::local_user_id.eq(local_user_id_join)), ), ) - .select(( - comment::all_columns, - person::all_columns, - post::all_columns, - community::all_columns, - comment_aggregates::all_columns, - community_person_ban::all_columns.nullable(), - community_follower::all_columns.nullable(), - comment_saved::all_columns.nullable(), - person_block::all_columns.nullable(), - comment_like::score.nullable(), - )) - .into_boxed(); - - if let Some(creator_id) = self.creator_id { + .select(selection); + + if let Some(creator_id) = options.creator_id { query = query.filter(comment::creator_id.eq(creator_id)); }; - if let Some(post_id) = self.post_id { + if let Some(post_id) = options.post_id { query = query.filter(comment::post_id.eq(post_id)); }; - if let Some(parent_path) = self.parent_path.as_ref() { + if let Some(parent_path) = options.parent_path.as_ref() { query = query.filter(comment::path.contained_by(parent_path)); }; - if let Some(search_term) = self.search_term { + if let Some(search_term) = options.search_term { query = query.filter(comment::content.ilike(fuzzy_search(&search_term))); }; - if let Some(community_id) = self.community_id { + if let Some(community_id) = options.community_id { query = query.filter(post::community_id.eq(community_id)); } - if let Some(listing_type) = self.listing_type { + if let Some(listing_type) = options.listing_type { match listing_type { ListingType::Subscribed => { query = query.filter(community_follower::person_id.is_not_null()) @@ -293,24 +191,24 @@ impl<'a> CommentQuery<'a> { } } - if self.saved_only.unwrap_or(false) { + if options.saved_only.unwrap_or(false) { query = query.filter(comment_saved::comment_id.is_not_null()); } - let is_profile_view = self.is_profile_view.unwrap_or(false); - let is_creator = self.creator_id == self.local_user.map(|l| l.person.id); + let is_profile_view = options.is_profile_view.unwrap_or(false); + let is_creator = options.creator_id == options.local_user.map(|l| l.person.id); // only show deleted comments to creator if !is_creator { query = query.filter(comment::deleted.eq(false)); } - let is_admin = self.local_user.map(|l| l.person.admin).unwrap_or(false); + let is_admin = options.local_user.map(|l| l.person.admin).unwrap_or(false); // only show removed comments to admin when viewing user profile if !(is_profile_view && is_admin) { query = query.filter(comment::removed.eq(false)); } - if !self + if !options .local_user .map(|l| l.local_user.show_bot_accounts) .unwrap_or(true) @@ -318,20 +216,20 @@ impl<'a> CommentQuery<'a> { query = query.filter(person::bot_account.eq(false)); }; - if self.local_user.is_some() { + if options.local_user.is_some() { // Filter out the rows with missing languages query = query.filter(local_user_language::language_id.is_not_null()); // Don't show blocked communities or persons - if self.post_id.is_none() { + if options.post_id.is_none() { query = query.filter(community_block::person_id.is_null()); } query = query.filter(person_block::person_id.is_null()); } // A Max depth given means its a tree fetch - let (limit, offset) = if let Some(max_depth) = self.max_depth { - let depth_limit = if let Some(parent_path) = self.parent_path.as_ref() { + let (limit, offset) = if let Some(max_depth) = options.max_depth { + let depth_limit = if let Some(parent_path) = options.parent_path.as_ref() { parent_path.0.split('.').count() as i32 + max_depth // Add one because of root "0" } else { @@ -341,7 +239,7 @@ impl<'a> CommentQuery<'a> { query = query.filter(nlevel(comment::path).le(depth_limit)); // only order if filtering by a post id. DOS potential otherwise and max_depth + !post_id isn't used anyways (afaik) - if self.post_id.is_some() { + if options.post_id.is_some() { // Always order by the parent path first query = query.order_by(subpath(comment::path, 0, -1)); } @@ -358,11 +256,11 @@ impl<'a> CommentQuery<'a> { // (i64::MAX, 0) (300, 0) } else { - // limit_and_offset_unlimited(self.page, self.limit) - limit_and_offset(self.page, self.limit)? + // limit_and_offset_unlimited(options.page, options.limit) + limit_and_offset(options.page, options.limit)? }; - query = match self.sort.unwrap_or(CommentSortType::Hot) { + query = match options.sort.unwrap_or(CommentSortType::Hot) { CommentSortType::Hot => query .then_order_by(comment_aggregates::hot_rank.desc()) .then_order_by(comment_aggregates::score.desc()), @@ -375,13 +273,53 @@ impl<'a> CommentQuery<'a> { }; // Note: deleted and removed comments are done on the front side - let res = query + query .limit(limit) .offset(offset) - .load::<CommentViewTuple>(conn) - .await?; + .load::<CommentViewTuple>(&mut conn) + .await + }; + + Queries::new(read, list) +} + +impl CommentView { + pub async fn read( + pool: &mut DbPool<'_>, + comment_id: CommentId, + my_person_id: Option<PersonId>, + ) -> Result<Self, Error> { + // If a person is given, then my_vote (res.9), if None, should be 0, not null + // Necessary to differentiate between other person's votes + let mut res = queries().read(pool, (comment_id, my_person_id)).await?; + if my_person_id.is_some() && res.my_vote.is_none() { + res.my_vote = Some(0); + } + Ok(res) + } +} + +#[derive(Default)] +pub struct CommentQuery<'a> { + pub listing_type: Option<ListingType>, + pub sort: Option<CommentSortType>, + pub community_id: Option<CommunityId>, + pub post_id: Option<PostId>, + pub parent_path: Option<Ltree>, + pub creator_id: Option<PersonId>, + pub local_user: Option<&'a LocalUserView>, + pub search_term: Option<String>, + pub saved_only: Option<bool>, + pub is_profile_view: Option<bool>, + pub show_deleted_and_removed: Option<bool>, + pub page: Option<i64>, + pub limit: Option<i64>, + pub max_depth: Option<i32>, +} - Ok(res.into_iter().map(CommentView::from_tuple).collect()) +impl<'a> CommentQuery<'a> { + pub async fn list(self, pool: &mut DbPool<'_>) -> Result<Vec<CommentView>, Error> { + queries().list(pool, self).await } } diff --git a/crates/db_views/src/local_user_view.rs b/crates/db_views/src/local_user_view.rs index 567ca3fe..23a8b8f0 100644 --- a/crates/db_views/src/local_user_view.rs +++ b/crates/db_views/src/local_user_view.rs @@ -7,136 +7,102 @@ use lemmy_db_schema::{ schema::{local_user, person, person_aggregates}, source::{local_user::LocalUser, person::Person}, traits::JoinView, - utils::{functions::lower, get_conn, DbPool}, + utils::{functions::lower, DbConn, DbPool, ListFn, Queries, ReadFn}, }; type LocalUserViewTuple = (LocalUser, Person, PersonAggregates); -impl LocalUserView { - pub async fn read(pool: &mut DbPool<'_>, local_user_id: LocalUserId) -> Result<Self, Error> { - let conn = &mut get_conn(pool).await?; +enum ReadBy<'a> { + Id(LocalUserId), + Person(PersonId), + Name(&'a str), + NameOrEmail(&'a str), + Email(&'a str), +} - let (local_user, person, counts) = local_user::table - .find(local_user_id) - .inner_join(person::table) +enum ListMode { + AdminsWithEmails, +} + +fn queries<'a>( +) -> Queries<impl ReadFn<'a, LocalUserView, ReadBy<'a>>, impl ListFn<'a, LocalUserView, ListMode>> { + let selection = ( + local_user::all_columns, + person::all_columns, + person_aggregates::all_columns, + ); + + let read = move |mut conn: DbConn<'a>, search: ReadBy<'a>| async move { + let mut query = local_user::table.into_boxed(); + query = match search { + ReadBy::Id(local_user_id) => query.filter(local_user::id.eq(local_user_id)), + ReadBy::Email(from_email) => query.filter(local_user::email.eq(from_email)), + _ => query, + }; + let mut query = query.inner_join(person::table); + query = match search { + ReadBy::Person(person_id) => query.filter(person::id.eq(person_id)), + ReadBy::Name(name) => query.filter(lower(person::name).eq(name.to_lowercase())), + ReadBy::NameOrEmail(name_or_email) => query.filter( + lower(person::name) + .eq(lower(name_or_email)) + .or(local_user::email.eq(name_or_email)), + ), + _ => query, + }; + query .inner_join(person_aggregates::table.on(person::id.eq(person_aggregates::person_id))) - .select(( - local_user::all_columns, - person::all_columns, - person_aggregates::all_columns, - )) - .first::<LocalUserViewTuple>(conn) - .await?; - Ok(Self { - local_user, - person, - counts, - }) + .select(selection) + .first::<LocalUserViewTuple>(&mut conn) + .await + }; + + let list = move |mut conn: DbConn<'a>, mode: ListMode| async move { + match mode { + ListMode::AdminsWithEmails => { + local_user::table + .filter(local_user::email.is_not_null()) + .filter(person::admin.eq(true)) + .inner_join(person::table) + .inner_join(person_aggregates::table.on(person::id.eq(person_aggregates::person_id))) + .select(selection) + .load::<LocalUserViewTuple>(&mut conn) + .await + } + } + }; + + Queries::new(read, list) +} + +impl LocalUserView { + pub async fn read(pool: &mut DbPool<'_>, local_user_id: LocalUserId) -> Result<Self, Error> { + queries().read(pool, ReadBy::Id(local_user_id)).await } pub async fn read_person(pool: &mut DbPool<'_>, person_id: PersonId) -> Result<Self, Error> { - let conn = &mut get_conn(pool).await?; - let (local_user, person, counts) = local_user::table - .filter(person::id.eq(person_id)) - .inner_join(person::table) - .inner_join(person_aggregates::table.on(person::id.eq(person_aggregates::person_id))) - .select(( - local_user::all_columns, - person::all_columns, - person_aggregates::all_columns, - )) - .first::<LocalUserViewTuple>(conn) - .await?; - Ok(Self { - local_user, - person, - counts, - }) + queries().read(pool, ReadBy::Person(person_id)).await } pub async fn read_from_name(pool: &mut DbPool<'_>, name: &str) -> Result<Self, Error> { - let conn = &mut get_conn(pool).await?; - let (local_user, person, counts) = local_user::table - .filter(lower(person::name).eq(name.to_lowercase())) - .inner_join(person::table) - .inner_join(person_aggregates::table.on(person::id.eq(person_aggregates::person_id))) - .select(( - local_user::all_columns, - person::all_columns, - person_aggregates::all_columns, - )) - .first::<LocalUserViewTuple>(conn) - .await?; - Ok(Self { - local_user, - person, - counts, - }) + queries().read(pool, ReadBy::Name(name)).await } pub async fn find_by_email_or_name( pool: &mut DbPool<'_>, name_or_email: &str, ) -> Result<Self, Error> { - let conn = &mut get_conn(pool).await?; - let (local_user, person, counts) = local_user::table - .inner_join(person::table) - .inner_join(person_aggregates::table.on(person::id.eq(person_aggregates::person_id))) - .filter( - lower(person::name) - .eq(lower(name_or_email)) - .or(local_user::email.eq(name_or_email)), - ) - .select(( - local_user::all_columns, - person::all_columns, - person_aggregates::all_columns, - )) - .first::<LocalUserViewTuple>(conn) - .await?; - Ok(Self { - local_user, - person, - counts, - }) + queries() + .read(pool, ReadBy::NameOrEmail(name_or_email)) + .await } pub async fn find_by_email(pool: &mut DbPool<'_>, from_email: &str) -> Result<Self, Error> { - let conn = &mut get_conn(pool).await?; - let (local_user, person, counts) = local_user::table - .inner_join(person::table) - .inner_join(person_aggregates::table.on(person::id.eq(person_aggregates::person_id))) - .filter(local_user::email.eq(from_email)) - .select(( - local_user::all_columns, - person::all_columns, - person_aggregates::all_columns, - )) - .first::<LocalUserViewTuple>(conn) - .await?; - Ok(Self { - local_user, - person, - counts, - }) + queries().read(pool, ReadBy::Email(from_email)).await } pub async fn list_admins_with_emails(pool: &mut DbPool<'_>) -> Result<Vec<Self>, Error> { - let conn = &mut get_conn(pool).await?; - let res = local_user::table - .filter(person::admin.eq(true)) - .filter(local_user::email.is_not_null()) - .inner_join(person::table) - .inner_join(person_aggregates::table.on(person::id.eq(person_aggregates::person_id))) - .select(( - local_user::all_columns, - person::all_columns, - person_aggregates::all_columns, - )) - .load::<LocalUserViewTuple>(conn) - .await?; - - Ok(res.into_iter().map(LocalUserView::from_tuple).collect()) + queries().list(pool, ListMode::AdminsWithEmails).await } } diff --git a/crates/db_views/src/post_report_view.rs b/crates/db_views/src/post_report_view.rs index 8c47d8c5..4ef6067f 100644 --- a/crates/db_views/src/post_report_view.rs +++ b/crates/db_views/src/post_report_view.rs @@ -1,5 +1,6 @@ use crate::structs::PostReportView; use diesel::{ + pg::Pg, result::Error, BoolExpressionMethods, ExpressionMethods, @@ -10,6 +11,7 @@ use diesel::{ use diesel_async::RunQueryDsl; use lemmy_db_schema::{ aggregates::structs::PostAggregates, + aliases, newtypes::{CommunityId, PersonId, PostReportId}, schema::{ community, @@ -28,7 +30,7 @@ use lemmy_db_schema::{ post_report::PostReport, }, traits::JoinView, - utils::{get_conn, limit_and_offset, DbPool}, + utils::{get_conn, limit_and_offset, DbConn, DbPool, ListFn, Queries, ReadFn}, }; type PostReportViewTuple = ( @@ -43,34 +45,16 @@ type PostReportViewTuple = ( Option<Person>, ); -impl PostReportView { - /// returns the PostReportView for the provided report_id - /// - /// * `report_id` - the report id to obtain - pub async fn read( - pool: &mut DbPool<'_>, - report_id: PostReportId, - my_person_id: PersonId, - ) -> Result<Self, Error> { - let conn = &mut get_conn(pool).await?; - let (person_alias_1, person_alias_2) = diesel::alias!(person as person1, person as person2); - - let ( - post_report, - post, - community, - creator, - post_creator, - creator_banned_from_community, - post_like, - counts, - resolver, - ) = post_report::table - .find(report_id) +fn queries<'a>() -> Queries< + impl ReadFn<'a, PostReportView, (PostReportId, PersonId)>, + impl ListFn<'a, PostReportView, (PostReportQuery, &'a Person)>, +> { + let all_joins = |query: post_report::BoxedQuery<'a, Pg>, my_person_id: PersonId| { + query .inner_join(post::table) .inner_join(community::table.on(post::community_id.eq(community::id))) .inner_join(person::table.on(post_report::creator_id.eq(person::id))) - .inner_join(person_alias_1.on(post::creator_id.eq(person_alias_1.field(person::id)))) + .inner_join(aliases::person1.on(post::creator_id.eq(aliases::person1.field(person::id)))) .left_join( community_person_ban::table.on( post::community_id @@ -87,35 +71,79 @@ impl PostReportView { ) .inner_join(post_aggregates::table.on(post_report::post_id.eq(post_aggregates::post_id))) .left_join( - person_alias_2.on(post_report::resolver_id.eq(person_alias_2.field(person::id).nullable())), + aliases::person2 + .on(post_report::resolver_id.eq(aliases::person2.field(person::id).nullable())), ) .select(( post_report::all_columns, post::all_columns, community::all_columns, person::all_columns, - person_alias_1.fields(person::all_columns), + aliases::person1.fields(person::all_columns), community_person_ban::all_columns.nullable(), post_like::score.nullable(), post_aggregates::all_columns, - person_alias_2.fields(person::all_columns.nullable()), + aliases::person2.fields(person::all_columns.nullable()), )) - .first::<PostReportViewTuple>(conn) - .await?; - - let my_vote = post_like; - - Ok(Self { - post_report, - post, - community, - creator, - post_creator, - creator_banned_from_community: creator_banned_from_community.is_some(), - my_vote, - counts, - resolver, - }) + }; + + let read = move |mut conn: DbConn<'a>, (report_id, my_person_id): (PostReportId, PersonId)| async move { + all_joins( + post_report::table.find(report_id).into_boxed(), + my_person_id, + ) + .first::<PostReportViewTuple>(&mut conn) + .await + }; + + let list = move |mut conn: DbConn<'a>, (options, my_person): (PostReportQuery, &'a Person)| async move { + let mut query = all_joins(post_report::table.into_boxed(), my_person.id); + + if let Some(community_id) = options.community_id { + query = query.filter(post::community_id.eq(community_id)); + } + + if options.unresolved_only.unwrap_or(false) { + query = query.filter(post_report::resolved.eq(false)); + } + + let (limit, offset) = limit_and_offset(options.page, options.limit)?; + + query = query + .order_by(post_report::published.desc()) + .limit(limit) + .offset(offset); + + // If its not an admin, get only the ones you mod + if !my_person.admin { + query + .inner_join( + community_moderator::table.on( + community_moderator::community_id + .eq(post::community_id) + .and(community_moderator::person_id.eq(my_person.id)), + ), + ) + .load::<PostReportViewTuple>(&mut conn) + .await + } else { + query.load::<PostReportViewTuple>(&mut conn).await + } + }; + + Queries::new(read, list) +} + +impl PostReportView { + /// returns the PostReportView for the provided report_id + /// + /// * `report_id` - the report id to obtain + pub async fn read( + pool: &mut DbPool<'_>, + report_id: PostReportId, + my_person_id: PersonId, + ) -> Result<Self, Error> { + queries().read(pool, (report_id, my_person_id)).await } /// returns the current unresolved post report count for the communities you mod @@ -172,77 +200,7 @@ impl PostReportQuery { pool: &mut DbPool<'_>, my_person: &Person, ) -> Result<Vec<PostReportView>, Error> { - let conn = &mut get_conn(pool).await?; - let (person_alias_1, person_alias_2) = diesel::alias!(person as person1, person as person2); - - let mut query = post_report::table - .inner_join(post::table) - .inner_join(community::table.on(post::community_id.eq(community::id))) - .inner_join(person::table.on(post_report::creator_id.eq(person::id))) - .inner_join(person_alias_1.on(post::creator_id.eq(person_alias_1.field(person::id)))) - .left_join( - community_person_ban::table.on( - post::community_id - .eq(community_person_ban::community_id) - .and(community_person_ban::person_id.eq(post::creator_id)), - ), - ) - .left_join( - post_like::table.on( - post::id - .eq(post_like::post_id) - .and(post_like::person_id.eq(my_person.id)), - ), - ) - .inner_join(post_aggregates::table.on(post_report::post_id.eq(post_aggregates::post_id))) - .left_join( - person_alias_2.on(post_report::resolver_id.eq(person_alias_2.field(person::id).nullable())), - ) - .select(( - post_report::all_columns, - post::all_columns, - community::all_columns, - person::all_columns, - person_alias_1.fields(person::all_columns), - community_person_ban::all_columns.nullable(), - post_like::score.nullable(), - post_aggregates::all_columns, - person_alias_2.fields(person::all_columns.nullable()), - )) - .into_boxed(); - - if let Some(community_id) = self.community_id { - query = query.filter(post::community_id.eq(community_id)); - } - - if self.unresolved_only.unwrap_or(false) { - query = query.filter(post_report::resolved.eq(false)); - } - - let (limit, offset) = limit_and_offset(self.page, self.limit)?; - - query = query - .order_by(post_report::published.desc()) - .limit(limit) - .offset(offset); - - // If its not an admin, get only the ones you mod - let res = if !my_person.admin { - query - .inner_join( - community_moderator::table.on( - community_moderator::community_id - .eq(post::community_id) - .and(community_moderator::person_id.eq(my_person.id)), - ), - ) - .load::<PostReportViewTuple>(conn) - .await? - } else { - query.load::<PostReportViewTuple>(conn).await? - }; - - Ok(res.into_iter().map(PostReportView::from_tuple).collect()) + queries().list(pool, (self, my_person)).await } } diff --git a/crates/db_views/src/post_view.rs b/crates/db_views/src/post_view.rs index e0231092..9f6d0735 100644 --- a/crates/db_views/src/post_view.rs +++ b/crates/db_views/src/post_view.rs @@ -40,7 +40,7 @@ use lemmy_db_schema::{ post::{Post, PostRead, PostSaved}, }, traits::JoinView, - utils::{fuzzy_search, get_conn, limit_and_offset, DbPool}, + utils::{fuzzy_search, limit_and_offset, DbConn, DbPool, ListFn, Queries, ReadFn}, ListingType, SortType, }; @@ -62,19 +62,15 @@ type PostViewTuple = ( sql_function!(fn coalesce(x: sql_types::Nullable<sql_types::BigInt>, y: sql_types::BigInt) -> sql_types::BigInt); -impl PostView { - pub async fn read( - pool: &mut DbPool<'_>, - post_id: PostId, - my_person_id: Option<PersonId>, - is_mod_or_admin: Option<bool>, - ) -> Result<Self, Error> { - let conn = &mut get_conn(pool).await?; - +fn queries<'a>() -> Queries< + impl ReadFn<'a, PostView, (PostId, Option<PersonId>, Option<bool>)>, + impl ListFn<'a, PostView, PostQuery<'a>>, +> { + let all_joins = |query: post_aggregates::BoxedQuery<'a, Pg>, my_person_id: Option<PersonId>| { // The left join below will return None in this case let person_id_join = my_person_id.unwrap_or(PersonId(-1)); - let mut query = post_aggregates::table - .filter(post_aggregates::post_id.eq(post_id)) + + query .inner_join(person::table) .inner_join(community::table) .left_join( @@ -134,23 +130,41 @@ impl PostView { .and(person_post_aggregates::person_id.eq(person_id_join)), ), ) - .select(( - post::all_columns, - person::all_columns, - community::all_columns, - community_person_ban::all_columns.nullable(), - post_aggregates::all_columns, - community_follower::all_columns.nullable(), - post_saved::all_columns.nullable(), - post_read::all_columns.nullable(), - person_block::all_columns.nullable(), - post_like::score.nullable(), - coalesce( - post_aggregates::comments.nullable() - person_post_aggregates::read_comments.nullable(), - post_aggregates::comments, - ), - )) - .into_boxed(); + }; + + let selection = ( + post::all_columns, + person::all_columns, + community::all_columns, + community_person_ban::all_columns.nullable(), + post_aggregates::all_columns, + community_follower::all_columns.nullable(), + post_saved::all_columns.nullable(), + post_read::all_columns.nullable(), + person_block::all_columns.nullable(), + post_like::score.nullable(), + coalesce( + post_aggregates::comments.nullable() - person_post_aggregates::read_comments.nullable(), + post_aggregates::comments, + ), + ); + + let read = move |mut conn: DbConn<'a>, + (post_id, my_person_id, is_mod_or_admin): ( + PostId, + Option<PersonId>, + Option<bool>, + )| async move { + // The left join below will return None in this case + let person_id_join = my_person_id.unwrap_or(PersonId(-1)); + + let mut query = all_joins( + post_aggregates::table + .filter(post_aggregates::post_id.eq(post_id)) + .into_boxed(), + my_person_id, + ) + .select(selection); // Hide deleted and removed for non-admins or mods if !is_mod_or_admin.unwrap_or(false) { @@ -170,117 +184,18 @@ impl PostView { ); } - let ( - post, - creator, - community, - creator_banned_from_community, - counts, - follower, - saved, - read, - creator_blocked, - post_like, - unread_comments, - ) = query.first::<PostViewTuple>(conn).await?; - - // If a person is given, then my_vote, if None, should be 0, not null - // Necessary to differentiate between other person's votes - let my_vote = if my_person_id.is_some() && post_like.is_none() { - Some(0) - } else { - post_like - }; - - Ok(PostView { - post, - creator, - community, - creator_banned_from_community: creator_banned_from_community.is_some(), - counts, - subscribed: CommunityFollower::to_subscribed_type(&follower), - saved: saved.is_some(), - read: read.is_some(), - creator_blocked: creator_blocked.is_some(), - my_vote, - unread_comments, - }) - } -} - -#[derive(Default)] -pub struct PostQuery<'a> { - pub listing_type: Option<ListingType>, - pub sort: Option<SortType>, - pub creator_id: Option<PersonId>, - pub community_id: Option<CommunityId>, - pub local_user: Option<&'a LocalUserView>, - pub search_term: Option<String>, - pub url_search: Option<String>, - pub saved_only: Option<bool>, - pub moderator_view: Option<bool>, - pub is_profile_view: Option<bool>, - pub page: Option<i64>, - pub limit: Option<i64>, -} + query.first::<PostViewTuple>(&mut conn).await + }; -impl<'a> PostQuery<'a> { - pub async fn list(self, pool: &mut DbPool<'_>) -> Result<Vec<PostView>, Error> { - let conn = &mut get_conn(pool).await?; + let list = move |mut conn: DbConn<'a>, options: PostQuery<'a>| async move { + let person_id = options.local_user.map(|l| l.person.id); + let local_user_id = options.local_user.map(|l| l.local_user.id); // The left join below will return None in this case - let person_id_join = self.local_user.map(|l| l.person.id).unwrap_or(PersonId(-1)); - let local_user_id_join = self - .local_user - .map(|l| l.local_user.id) - .unwrap_or(LocalUserId(-1)); + let person_id_join = person_id.unwrap_or(PersonId(-1)); + let local_user_id_join = local_user_id.unwrap_or(LocalUserId(-1)); - let mut query = post_aggregates::table - .inner_join(person::table) - .inner_join(post::table) - .inner_join(community::table) - .left_join( - community_person_ban::table.on( - post_aggregates::community_id - .eq(community_person_ban::community_id) - .and(community_person_ban::person_id.eq(post_aggregates::creator_id)), - ), - ) - .left_join( - community_follower::table.on( - post_aggregates::community_id - .eq(community_follower::community_id) - .and(community_follower::person_id.eq(person_id_join)), - ), - ) - .left_join( - community_moderator::table.on( - post::community_id - .eq(community_moderator::community_id) - .and(community_moderator::person_id.eq(person_id_join)), - ), - ) - .left_join( - post_saved::table.on( - post_aggregates::post_id - .eq(post_saved::post_id) - .and(post_saved::person_id.eq(person_id_join)), - ), - ) - .left_join( - post_read::table.on( - post_aggregates::post_id - .eq(post_read::post_id) - .and(post_read::person_id.eq(person_id_join)), - ), - ) - .left_join( - person_block::table.on( - post_aggregates::creator_id - .eq(person_block::target_id) - .and(person_block::person_id.eq(person_id_join)), - ), - ) + let mut query = all_joins(post_aggregates::table.into_boxed(), person_id) .left_join( community_block::table.on( post_aggregates::community_id @@ -288,20 +203,6 @@ impl<'a> PostQuery<'a> { .and(community_block::person_id.eq(person_id_join)), ), ) - .left_join( - post_like::table.on( - post_aggregates::post_id - .eq(post_like::post_id) - .and(post_like::person_id.eq(person_id_join)), - ), - ) - .left_join( - person_post_aggregates::table.on( - post_aggregates::post_id - .eq(person_post_aggregates::post_id) - .and(person_post_aggregates::person_id.eq(person_id_join)), - ), - ) .left_join( local_user_language::table.on( post::language_id @@ -309,26 +210,10 @@ impl<'a> PostQuery<'a> { .and(local_user_language::local_user_id.eq(local_user_id_join)), ), ) - .select(( - post::all_columns, - person::all_columns, - community::all_columns, - community_person_ban::all_columns.nullable(), - post_aggregates::all_columns, - community_follower::all_columns.nullable(), - post_saved::all_columns.nullable(), - post_read::all_columns.nullable(), - person_block::all_columns.nullable(), - post_like::score.nullable(), - coalesce( - post_aggregates::comments.nullable() - person_post_aggregates::read_comments.nullable(), - post_aggregates::comments, - ), - )) - .into_boxed(); + .select(selection); - let is_profile_view = self.is_profile_view.unwrap_or(false); - let is_creator = self.creator_id == self.local_user.map(|l| l.person.id); + let is_profile_view = options.is_profile_view.unwrap_or(false); + let is_creator = options.creator_id == options.local_user.map(|l| l.person.id); // only show deleted posts to creator if is_creator { query = query @@ -336,7 +221,7 @@ impl<'a> PostQuery<'a> { .filter(post::deleted.eq(false)); } - let is_admin = self.local_user.map(|l| l.person.admin).unwrap_or(false); + let is_admin = options.local_user.map(|l| l.person.admin).unwrap_or(false); // only show removed posts to admin when viewing user profile if !(is_profile_view && is_admin) { query = query @@ -344,19 +229,19 @@ impl<'a> PostQuery<'a> { .filter(post::removed.eq(false)); } - if self.community_id.is_none() { + if options.community_id.is_none() { query = query.then_order_by(post_aggregates::featured_local.desc()); - } else if let Some(community_id) = self.community_id { + } else if let Some(community_id) = options.community_id { query = query .filter(post_aggregates::community_id.eq(community_id)) .then_order_by(post_aggregates::featured_community.desc()); } - if let Some(creator_id) = self.creator_id { + if let Some(creator_id) = options.creator_id { query = query.filter(post_aggregates::creator_id.eq(creator_id)); } - if let Some(listing_type) = self.listing_type { + if let Some(listing_type) = options.listing_type { match listing_type { ListingType::Subscribed => { query = query.filter(community_follower::person_id.is_not_null()) @@ -378,11 +263,11 @@ impl<'a> PostQuery<'a> { } } - if let Some(url_search) = self.url_search { + if let Some(url_search) = options.url_search { query = query.filter(post::url.eq(url_search)); } - if let Some(search_term) = self.search_term { + if let Some(search_term) = options.search_term { let searcher = fuzzy_search(&search_term); query = query.filter( post::name @@ -391,7 +276,7 @@ impl<'a> PostQuery<'a> { ); } - if !self + if !options .local_user .map(|l| l.local_user.show_nsfw) .unwrap_or(false) @@ -401,7 +286,7 @@ impl<'a> PostQuery<'a> { .filter(community::nsfw.eq(false)); }; - if !self + if !options .local_user .map(|l| l.local_user.show_bot_accounts) .unwrap_or(true) @@ -409,16 +294,16 @@ impl<'a> PostQuery<'a> { query = query.filter(person::bot_account.eq(false)); }; - if self.saved_only.unwrap_or(false) { + if options.saved_only.unwrap_or(false) { query = query.filter(post_saved::post_id.is_not_null()); } - if self.moderator_view.unwrap_or(false) { + if options.moderator_view.unwrap_or(false) { query = query.filter(community_moderator::person_id.is_not_null()); } // Only hide the read posts, if the saved_only is false. Otherwise ppl with the hide_read // setting wont be able to see saved posts. - else if !self + else if !options .local_user .map(|l| l.local_user.show_read_posts) .unwrap_or(true) @@ -426,18 +311,18 @@ impl<'a> PostQuery<'a> { query = query.filter(post_read::post_id.is_null()); } - if self.local_user.is_some() { + if options.local_user.is_some() { // Filter out the rows with missing languages query = query.filter(local_user_language::language_id.is_not_null()); // Don't show blocked communities or persons query = query.filter(community_block::person_id.is_null()); - if !self.moderator_view.unwrap_or(false) { + if !options.moderator_view.unwrap_or(false) { query = query.filter(person_block::person_id.is_null()); } } - query = match self.sort.unwrap_or(SortType::Hot) { + query = match options.sort.unwrap_or(SortType::Hot) { SortType::Active => query .then_order_by(post_aggregates::hot_rank_active.desc()) .then_order_by(post_aggregates::published.desc()), @@ -496,15 +381,58 @@ impl<'a> PostQuery<'a> { .then_order_by(post_aggregates::published.desc()), }; - let (limit, offset) = limit_and_offset(self.page, self.limit)?; + let (limit, offset) = limit_and_offset(options.page, options.limit)?; query = query.limit(limit).offset(offset); debug!("Post View Query: {:?}", debug_query::<Pg, _>(&query)); - let res = query.load::<PostViewTuple>(conn).await?; + query.load::<PostViewTuple>(&mut conn).await + }; - Ok(res.into_iter().map(PostView::from_tuple).collect()) + Queries::new(read, list) +} + +impl PostView { + pub async fn read( + pool: &mut DbPool<'_>, + post_id: PostId, + my_person_id: Option<PersonId>, + is_mod_or_admin: Option<bool>, + ) -> Result<Self, Error> { + let mut res = queries() + .read(pool, (post_id, my_person_id, is_mod_or_admin)) + .await?; + + // If a person is given, then my_vote, if None, should be 0, not null + // Necessary to differentiate between other person's votes + if my_person_id.is_some() && res.my_vote.is_none() { + res.my_vote = Some(0) + }; + + Ok(res) + } +} + +#[derive(Default)] +pub struct PostQuery<'a> { + pub listing_type: Option<ListingType>, + pub sort: Option<SortType>, + pub creator_id: Option<PersonId>, + pub community_id: Option<CommunityId>, + pub local_user: Option<&'a LocalUserView>, + pub search_term: Option<String>, + pub url_search: Option<String>, + pub saved_only: Option<bool>, + pub moderator_view: Option<bool>, + pub is_profile_view: Option<bool>, + pub page: Option<i64>, + pub limit: Option<i64>, +} + +impl<'a> PostQuery<'a> { + pub async fn list(self, pool: &mut DbPool<'_>) -> Result<Vec<PostView>, Error> { + queries().list(pool, self).await } } diff --git a/crates/db_views/src/private_message_report_view.rs b/crates/db_views/src/private_message_report_view.rs index 7ceca271..ce87abae 100644 --- a/crates/db_views/src/private_message_report_view.rs +++ b/crates/db_views/src/private_message_report_view.rs @@ -1,7 +1,15 @@ use crate::structs::PrivateMessageReportView; -use diesel::{result::Error, ExpressionMethods, JoinOnDsl, NullableExpressionMethods, QueryDsl}; +use diesel::{ + pg::Pg, + result::Error, + ExpressionMethods, + JoinOnDsl, + NullableExpressionMethods, + QueryDsl, +}; use diesel_async::RunQueryDsl; use lemmy_db_schema::{ + aliases, newtypes::PrivateMessageReportId, schema::{person, private_message, private_message_report}, source::{ @@ -10,7 +18,7 @@ use lemmy_db_schema::{ private_message_report::PrivateMessageReport, }, traits::JoinView, - utils::{get_conn, limit_and_offset, DbPool}, + utils::{get_conn, limit_and_offset, DbConn, DbPool, ListFn, Queries, ReadFn}, }; type PrivateMessageReportViewTuple = ( @@ -21,48 +29,66 @@ type PrivateMessageReportViewTuple = ( Option<Person>, ); -impl PrivateMessageReportView { - /// returns the PrivateMessageReportView for the provided report_id - /// - /// * `report_id` - the report id to obtain - pub async fn read( - pool: &mut DbPool<'_>, - report_id: PrivateMessageReportId, - ) -> Result<Self, Error> { - let conn = &mut get_conn(pool).await?; - let (person_alias_1, person_alias_2) = diesel::alias!(person as person1, person as person2); - - let (private_message_report, private_message, private_message_creator, creator, resolver) = - private_message_report::table - .find(report_id) +fn queries<'a>() -> Queries< + impl ReadFn<'a, PrivateMessageReportView, PrivateMessageReportId>, + impl ListFn<'a, PrivateMessageReportView, PrivateMessageReportQuery>, +> { + let all_joins = + |query: private_message_report::BoxedQuery<'a, Pg>| { + query .inner_join(private_message::table) .inner_join(person::table.on(private_message::creator_id.eq(person::id))) .inner_join( - person_alias_1 - .on(private_message_report::creator_id.eq(person_alias_1.field(person::id))), - ) - .left_join( - person_alias_2.on( - private_message_report::resolver_id.eq(person_alias_2.field(person::id).nullable()), - ), + aliases::person1 + .on(private_message_report::creator_id.eq(aliases::person1.field(person::id))), ) + .left_join(aliases::person2.on( + private_message_report::resolver_id.eq(aliases::person2.field(person::id).nullable()), + )) .select(( private_message_report::all_columns, private_message::all_columns, person::all_columns, - person_alias_1.fields(person::all_columns), - person_alias_2.fields(person::all_columns).nullable(), + aliases::person1.fields(person::all_columns), + aliases::person2.fields(person::all_columns).nullable(), )) - .first::<PrivateMessageReportViewTuple>(conn) - .await?; - - Ok(Self { - private_message_report, - private_message, - private_message_creator, - creator, - resolver, - }) + }; + + let read = move |mut conn: DbConn<'a>, report_id: PrivateMessageReportId| async move { + all_joins(private_message_report::table.find(report_id).into_boxed()) + .first::<PrivateMessageReportViewTuple>(&mut conn) + .await + }; + + let list = move |mut conn: DbConn<'a>, options: PrivateMessageReportQuery| async move { + let mut query = all_joins(private_message_report::table.into_boxed()); + + if options.unresolved_only.unwrap_or(false) { + query = query.filter(private_message_report::resolved.eq(false)); + } + + let (limit, offset) = limit_and_offset(options.page, options.limit)?; + + query + .order_by(private_message::published.desc()) + .limit(limit) + .offset(offset) + .load::<PrivateMessageReportViewTuple>(&mut conn) + .await + }; + + Queries::new(read, list) +} + +impl PrivateMessageReportView { + /// returns the PrivateMessageReportView for the provided report_id + /// + /// * `report_id` - the report id to obtain + pub async fn read( + pool: &mut DbPool<'_>, + report_id: PrivateMessageReportId, + ) -> Result<Self, Error> { + queries().read(pool, report_id).await } /// Returns the current unresolved post report count for the communities you mod @@ -89,47 +115,7 @@ pub struct PrivateMessageReportQuery { impl PrivateMessageReportQuery { pub async fn list(self, pool: &mut DbPool<'_>) -> Result<Vec<PrivateMessageReportView>, Error> { - let conn = &mut get_conn(pool).await?; - let (person_alias_1, person_alias_2) = diesel::alias!(person as person1, person as person2); - - let mut query = private_message_report::table - .inner_join(private_message::table) - .inner_join(person::table.on(private_message::creator_id.eq(person::id))) - .inner_join( - person_alias_1.on(private_message_report::creator_id.eq(person_alias_1.field(person::id))), - ) - .left_join( - person_alias_2 - .on(private_message_report::resolver_id.eq(person_alias_2.field(person::id).nullable())), - ) - .select(( - private_message_report::all_columns, - private_message::all_columns, - person::all_columns, - person_alias_1.fields(person::all_columns), - person_alias_2.fields(person::all_columns).nullable(), - )) - .into_boxed(); - - if self.unresolved_only.unwrap_or(false) { - query = query.filter(private_message_report::resolved.eq(false)); - } - - let (limit, offset) = limit_and_offset(self.page, self.limit)?; - - query = query - .order_by(private_message::published.desc()) - .limit(limit) - .offset(offset); - - let res = query.load::<PrivateMessageReportViewTuple>(conn).await?; - - Ok( - res - .into_iter() - .map(PrivateMessageReportView::from_tuple) - .collect(), - ) + queries().list(pool, self).await } } diff --git a/crates/db_views/src/private_message_view.rs b/crates/db_views/src/private_message_view.rs index 863db812..55d6583b 100644 --- a/crates/db_views/src/private_message_view.rs +++ b/crates/db_views/src/private_message_view.rs @@ -10,44 +10,87 @@ use diesel::{ }; use diesel_async::RunQueryDsl; use lemmy_db_schema::{ + aliases, newtypes::{PersonId, PrivateMessageId}, schema::{person, private_message}, source::{person::Person, private_message::PrivateMessage}, traits::JoinView, - utils::{get_conn, limit_and_offset, DbPool}, + utils::{get_conn, limit_and_offset, DbConn, DbPool, ListFn, Queries, ReadFn}, }; use tracing::debug; type PrivateMessageViewTuple = (PrivateMessage, Person, Person); +fn queries<'a>() -> Queries< + impl ReadFn<'a, PrivateMessageView, PrivateMessageId>, + impl ListFn<'a, PrivateMessageView, (PrivateMessageQuery, PersonId)>, +> { + let all_joins = |query: private_message::BoxedQuery<'a, Pg>| { + query + .inner_join(person::table.on(private_message::creator_id.eq(person::id))) + .inner_join( + aliases::person1.on(private_message::recipient_id.eq(aliases::person1.field(person::id))), + ) + }; + + let selection = ( + private_message::all_columns, + person::all_columns, + aliases::person1.fields(person::all_columns), + ); + + let read = move |mut conn: DbConn<'a>, private_message_id: PrivateMessageId| async move { + all_joins(private_message::table.find(private_message_id).into_boxed()) + .order_by(private_message::published.desc()) + .select(selection) + .first::<PrivateMessageViewTuple>(&mut conn) + .await + }; + + let list = move |mut conn: DbConn<'a>, + (options, recipient_id): (PrivateMessageQuery, PersonId)| async move { + let mut query = all_joins(private_message::table.into_boxed()).select(selection); + + // If its unread, I only want the ones to me + if options.unread_only.unwrap_or(false) { + query = query + .filter(private_message::read.eq(false)) + .filter(private_message::recipient_id.eq(recipient_id)); + } + // Otherwise, I want the ALL view to show both sent and received + else { + query = query.filter( + private_message::recipient_id + .eq(recipient_id) + .or(private_message::creator_id.eq(recipient_id)), + ) + } + + let (limit, offset) = limit_and_offset(options.page, options.limit)?; + + query = query + .filter(private_message::deleted.eq(false)) + .limit(limit) + .offset(offset) + .order_by(private_message::published.desc()); + + debug!( + "Private Message View Query: {:?}", + debug_query::<Pg, _>(&query) + ); + + query.load::<PrivateMessageViewTuple>(&mut conn).await + }; + + Queries::new(read, list) +} + impl PrivateMessageView { pub async fn read( pool: &mut DbPool<'_>, private_message_id: PrivateMessageId, ) -> Result<Self, Error> { - let conn = &mut get_conn(pool).await?; - let person_alias_1 = diesel::alias!(person as person1); - - let (private_message, creator, recipient) = private_message::table - .find(private_message_id) - .inner_join(person::table.on(private_message::creator_id.eq(person::id))) - .inner_join( - person_alias_1.on(private_message::recipient_id.eq(person_alias_1.field(person::id))), - ) - .order_by(private_message::published.desc()) - .select(( - private_message::all_columns, - person::all_columns, - person_alias_1.fields(person::all_columns), - )) - .first::<PrivateMessageViewTuple>(conn) - .await?; - - Ok(PrivateMessageView { - private_message, - creator, - recipient, - }) + queries().read(pool, private_message_id).await } /// Gets the number of unread messages @@ -80,57 +123,7 @@ impl PrivateMessageQuery { pool: &mut DbPool<'_>, recipient_id: PersonId, ) -> Result<Vec<PrivateMessageView>, Error> { - let conn = &mut get_conn(pool).await?; - let person_alias_1 = diesel::alias!(person as person1); - - let mut query = private_message::table - .inner_join(person::table.on(private_message::creator_id.eq(person::id))) - .inner_join( - person_alias_1.on(private_message::recipient_id.eq(person_alias_1.field(person::id))), - ) - .select(( - private_message::all_columns, - person::all_columns, - person_alias_1.fields(person::all_columns), - )) - .into_boxed(); - - // If its unread, I only want the ones to me - if self.unread_only.unwrap_or(false) { - query = query - .filter(private_message::read.eq(false)) - .filter(private_message::recipient_id.eq(recipient_id)); - } - // Otherwise, I want the ALL view to show both sent and received - else { - query = query.filter( - private_message::recipient_id - .eq(recipient_id) - .or(private_message::creator_id.eq(recipient_id)), - ) - } - - let (limit, offset) = limit_and_offset(self.page, self.limit)?; - - query = query - .filter(private_message::deleted.eq(false)) - .limit(limit) - .offset(offset) - .order_by(private_message::published.desc()); - - debug!( - "Private Message View Query: {:?}", - debug_query::<Pg, _>(&query) - ); - - let res = query.load::<PrivateMessageViewTuple>(conn).await?; - - Ok( - res - .into_iter() - .map(PrivateMessageView::from_tuple) - .collect(), - ) + queries().list(pool, (self, recipient_id)).await } } diff --git a/crates/db_views/src/registration_application_view.rs b/crates/db_views/src/registration_application_view.rs index 8e2f5826..6064bf05 100644 --- a/crates/db_views/src/registration_application_view.rs +++ b/crates/db_views/src/registration_application_view.rs @@ -1,6 +1,7 @@ use crate::structs::RegistrationApplicationView; use diesel::{ dsl::count, + pg::Pg, result::Error, ExpressionMethods, JoinOnDsl, @@ -9,6 +10,7 @@ use diesel::{ }; use diesel_async::RunQueryDsl; use lemmy_db_schema::{ + aliases, schema::{local_user, person, registration_application}, source::{ local_user::LocalUser, @@ -16,47 +18,75 @@ use lemmy_db_schema::{ registration_application::RegistrationApplication, }, traits::JoinView, - utils::{get_conn, limit_and_offset, DbPool}, + utils::{get_conn, limit_and_offset, DbConn, DbPool, ListFn, Queries, ReadFn}, }; type RegistrationApplicationViewTuple = (RegistrationApplication, LocalUser, Person, Option<Person>); +fn queries<'a>() -> Queries< + impl ReadFn<'a, RegistrationApplicationView, i32>, + impl ListFn<'a, RegistrationApplicationView, RegistrationApplicationQuery>, +> { + let all_joins = |query: registration_application::BoxedQuery<'a, Pg>| { + query + .inner_join(local_user::table.on(registration_application::local_user_id.eq(local_user::id))) + .inner_join(person::table.on(local_user::person_id.eq(person::id))) + .left_join( + aliases::person1 + .on(registration_application::admin_id.eq(aliases::person1.field(person::id).nullable())), + ) + .order_by(registration_application::published.desc()) + .select(( + registration_application::all_columns, + local_user::all_columns, + person::all_columns, + aliases::person1.fields(person::all_columns).nullable(), + )) + }; + + let read = move |mut conn: DbConn<'a>, registration_application_id: i32| async move { + all_joins( + registration_application::table + .find(registration_application_id) + .into_boxed(), + ) + .first::<RegistrationApplicationViewTuple>(&mut conn) + .await + }; + + let list = move |mut conn: DbConn<'a>, options: RegistrationApplicationQuery| async move { + let mut query = all_joins(registration_application::table.into_boxed()); + + if options.unread_only.unwrap_or(false) { + query = query.filter(registration_application::admin_id.is_null()) + } + + if options.verified_email_only.unwrap_or(false) { + query = query.filter(local_user::email_verified.eq(true)) + } + + let (limit, offset) = limit_and_offset(options.page, options.limit)?; + + query = query + .limit(limit) + .offset(offset) + .order_by(registration_application::published.desc()); + + query + .load::<RegistrationApplicationViewTuple>(&mut conn) + .await + }; + + Queries::new(read, list) +} + impl RegistrationApplicationView { pub async fn read( pool: &mut DbPool<'_>, registration_application_id: i32, ) -> Result<Self, Error> { - let conn = &mut get_conn(pool).await?; - let person_alias_1 = diesel::alias!(person as person1); - - let (registration_application, creator_local_user, creator, admin) = - registration_application::table - .find(registration_application_id) - .inner_join( - local_user::table.on(registration_application::local_user_id.eq(local_user::id)), - ) - .inner_join(person::table.on(local_user::person_id.eq(person::id))) - .left_join( - person_alias_1 - .on(registration_application::admin_id.eq(person_alias_1.field(person::id).nullable())), - ) - .order_by(registration_application::published.desc()) - .select(( - registration_application::all_columns, - local_user::all_columns, - person::all_columns, - person_alias_1.fields(person::all_columns).nullable(), - )) - .first::<RegistrationApplicationViewTuple>(conn) - .await?; - - Ok(RegistrationApplicationView { - registration_application, - creator_local_user, - creator, - admin, - }) + queries().read(pool, registration_application_id).await } /// Returns the current unread registration_application count @@ -101,48 +131,7 @@ impl RegistrationApplicationQuery { self, pool: &mut DbPool<'_>, ) -> Result<Vec<RegistrationApplicationView>, Error> { - let conn = &mut get_conn(pool).await?; - let person_alias_1 = diesel::alias!(person as person1); - - let mut query = registration_application::table - .inner_join(local_user::table.on(registration_application::local_user_id.eq(local_user::id))) - .inner_join(person::table.on(local_user::person_id.eq(person::id))) - .left_join( - person_alias_1 - .on(registration_application::admin_id.eq(person_alias_1.field(person::id).nullable())), - ) - .order_by(registration_application::published.desc()) - .select(( - registration_application::all_columns, - local_user::all_columns, - person::all_columns, - person_alias_1.fields(person::all_columns).nullable(), - )) - .into_boxed(); - - if self.unread_only.unwrap_or(false) { - query = query.filter(registration_application::admin_id.is_null()) - } - - if self.verified_email_only.unwrap_or(false) { - query = query.filter(local_user::email_verified.eq(true)) - } - - let (limit, offset) = limit_and_offset(self.page, self.limit)?; - - query = query - .limit(limit) - .offset(offset) - .order_by(registration_application::published.desc()); - - let res = query.load::<RegistrationApplicationViewTuple>(conn).await?; - - Ok( - res - .into_iter() - .map(RegistrationApplicationView::from_tuple) - .collect(), - ) + queries().list(pool, self).await } } diff --git a/crates/db_views_actor/src/comment_reply_view.rs b/crates/db_views_actor/src/comment_reply_view.rs index 406bfcb9..86934520 100644 --- a/crates/db_views_actor/src/comment_reply_view.rs +++ b/crates/db_views_actor/src/comment_reply_view.rs @@ -1,5 +1,6 @@ use crate::structs::CommentReplyView; use diesel::{ + pg::Pg, result::Error, BoolExpressionMethods, ExpressionMethods, @@ -10,6 +11,7 @@ use diesel::{ use diesel_async::RunQueryDsl; use lemmy_db_schema::{ aggregates::structs::CommentAggregates, + aliases, newtypes::{CommentReplyId, PersonId}, schema::{ comment, @@ -33,7 +35,7 @@ use lemmy_db_schema::{ post::Post, }, traits::JoinView, - utils::{get_conn, limit_and_offset, DbPool}, + utils::{get_conn, limit_and_offset, DbConn, DbPool, ListFn, Queries, ReadFn}, CommentSortType, }; @@ -52,38 +54,20 @@ type CommentReplyViewTuple = ( Option<i16>, ); -impl CommentReplyView { - pub async fn read( - pool: &mut DbPool<'_>, - comment_reply_id: CommentReplyId, - my_person_id: Option<PersonId>, - ) -> Result<Self, Error> { - let conn = &mut get_conn(pool).await?; - let person_alias_1 = diesel::alias!(person as person1); - +fn queries<'a>() -> Queries< + impl ReadFn<'a, CommentReplyView, (CommentReplyId, Option<PersonId>)>, + impl ListFn<'a, CommentReplyView, CommentReplyQuery>, +> { + let all_joins = |query: comment_reply::BoxedQuery<'a, Pg>, my_person_id: Option<PersonId>| { // The left join below will return None in this case let person_id_join = my_person_id.unwrap_or(PersonId(-1)); - let ( - comment_reply, - comment, - creator, - post, - community, - recipient, - counts, - creator_banned_from_community, - follower, - saved, - creator_blocked, - my_vote, - ) = comment_reply::table - .find(comment_reply_id) + query .inner_join(comment::table) .inner_join(person::table.on(comment::creator_id.eq(person::id))) .inner_join(post::table.on(comment::post_id.eq(post::id))) .inner_join(community::table.on(post::community_id.eq(community::id))) - .inner_join(person_alias_1) + .inner_join(aliases::person1) .inner_join(comment_aggregates::table.on(comment::id.eq(comment_aggregates::comment_id))) .left_join( community_person_ban::table.on( @@ -126,7 +110,7 @@ impl CommentReplyView { person::all_columns, post::all_columns, community::all_columns, - person_alias_1.fields(person::all_columns), + aliases::person1.fields(person::all_columns), comment_aggregates::all_columns, community_person_ban::all_columns.nullable(), community_follower::all_columns.nullable(), @@ -134,23 +118,63 @@ impl CommentReplyView { person_block::all_columns.nullable(), comment_like::score.nullable(), )) - .first::<CommentReplyViewTuple>(conn) - .await?; + }; + + let read = + move |mut conn: DbConn<'a>, + (comment_reply_id, my_person_id): (CommentReplyId, Option<PersonId>)| async move { + all_joins( + comment_reply::table.find(comment_reply_id).into_boxed(), + my_person_id, + ) + .first::<CommentReplyViewTuple>(&mut conn) + .await + }; + + let list = move |mut conn: DbConn<'a>, options: CommentReplyQuery| async move { + let mut query = all_joins(comment_reply::table.into_boxed(), options.my_person_id); + + if let Some(recipient_id) = options.recipient_id { + query = query.filter(comment_reply::recipient_id.eq(recipient_id)); + } + + if options.unread_only.unwrap_or(false) { + query = query.filter(comment_reply::read.eq(false)); + } + + if !options.show_bot_accounts.unwrap_or(true) { + query = query.filter(person::bot_account.eq(false)); + }; + + query = match options.sort.unwrap_or(CommentSortType::New) { + CommentSortType::Hot => query.then_order_by(comment_aggregates::hot_rank.desc()), + CommentSortType::Controversial => { + query.then_order_by(comment_aggregates::controversy_rank.desc()) + } + CommentSortType::New => query.then_order_by(comment_reply::published.desc()), + CommentSortType::Old => query.then_order_by(comment_reply::published.asc()), + CommentSortType::Top => query.order_by(comment_aggregates::score.desc()), + }; + + let (limit, offset) = limit_and_offset(options.page, options.limit)?; - Ok(CommentReplyView { - comment_reply, - comment, - creator, - post, - community, - recipient, - counts, - creator_banned_from_community: creator_banned_from_community.is_some(), - subscribed: CommunityFollower::to_subscribed_type(&follower), - saved: saved.is_some(), - creator_blocked: creator_blocked.is_some(), - my_vote, - }) + query + .limit(limit) + .offset(offset) + .load::<CommentReplyViewTuple>(&mut conn) + .await + }; + + Queries::new(read, list) +} + +impl CommentReplyView { + pub async fn read( + pool: &mut DbPool<'_>, + comment_reply_id: CommentReplyId, + my_person_id: Option<PersonId>, + ) -> Result<Self, Error> { + queries().read(pool, (comment_reply_id, my_person_id)).await } /// Gets the number of unread replies @@ -187,102 +211,7 @@ pub struct CommentReplyQuery { impl CommentReplyQuery { pub async fn list(self, pool: &mut DbPool<'_>) -> Result<Vec<CommentReplyView>, Error> { - let conn = &mut get_conn(pool).await?; - - let person_alias_1 = diesel::alias!(person as person1); - - // The left join below will return None in this case - let person_id_join = self.my_person_id.unwrap_or(PersonId(-1)); - - let mut query = comment_reply::table - .inner_join(comment::table) - .inner_join(person::table.on(comment::creator_id.eq(person::id))) - .inner_join(post::table.on(comment::post_id.eq(post::id))) - .inner_join(community::table.on(post::community_id.eq(community::id))) - .inner_join(person_alias_1) - .inner_join(comment_aggregates::table.on(comment::id.eq(comment_aggregates::comment_id))) - .left_join( - community_person_ban::table.on( - community::id - .eq(community_person_ban::community_id) - .and(community_person_ban::person_id.eq(comment::creator_id)), - ), - ) - .left_join( - community_follower::table.on( - post::community_id - .eq(community_follower::community_id) - .and(community_follower::person_id.eq(person_id_join)), - ), - ) - .left_join( - comment_saved::table.on( - comment::id - .eq(comment_saved::comment_id) - .and(comment_saved::person_id.eq(person_id_join)), - ), - ) - .left_join( - person_block::table.on( - comment::creator_id - .eq(person_block::target_id) - .and(person_block::person_id.eq(person_id_join)), - ), - ) - .left_join( - comment_like::table.on( - comment::id - .eq(comment_like::comment_id) - .and(comment_like::person_id.eq(person_id_join)), - ), - ) - .select(( - comment_reply::all_columns, - comment::all_columns, - person::all_columns, - post::all_columns, - community::all_columns, - person_alias_1.fields(person::all_columns), - comment_aggregates::all_columns, - community_person_ban::all_columns.nullable(), - community_follower::all_columns.nullable(), - comment_saved::all_columns.nullable(), - person_block::all_columns.nullable(), - comment_like::score.nullable(), - )) - .into_boxed(); - - if let Some(recipient_id) = self.recipient_id { - query = query.filter(comment_reply::recipient_id.eq(recipient_id)); - } - - if self.unread_only.unwrap_or(false) { - query = query.filter(comment_reply::read.eq(false)); - } - - if !self.show_bot_accounts.unwrap_or(true) { - query = query.filter(person::bot_account.eq(false)); - }; - - query = match self.sort.unwrap_or(CommentSortType::New) { - CommentSortType::Hot => query.then_order_by(comment_aggregates::hot_rank.desc()), - CommentSortType::Controversial => { - query.then_order_by(comment_aggregates::controversy_rank.desc()) - } - CommentSortType::New => query.then_order_by(comment_reply::published.desc()), - CommentSortType::Old => query.then_order_by(comment_reply::published.asc()), - CommentSortType::Top => query.order_by(comment_aggregates::score.desc()), - }; - - let (limit, offset) = limit_and_offset(self.page, self.limit)?; - - let res = query - .limit(limit) - .offset(offset) - .load::<CommentReplyViewTuple>(conn) - .await?; - - Ok(res.into_iter().map(CommentReplyView::from_tuple).collect()) + queries().list(pool, self).await } } diff --git a/crates/db_views_actor/src/community_view.rs b/crates/db_views_actor/src/community_view.rs index c31a2bd5..9ca5c218 100644 --- a/crates/db_views_actor/src/community_view.rs +++ b/crates/db_views_actor/src/community_view.rs @@ -1,5 +1,6 @@ use crate::structs::{CommunityModeratorView, CommunityView, PersonView}; use diesel::{ + pg::Pg, result::Error, BoolExpressionMethods, ExpressionMethods, @@ -19,7 +20,7 @@ use lemmy_db_schema::{ local_user::LocalUser, }, traits::JoinView, - utils::{fuzzy_search, get_conn, limit_and_offset, DbPool}, + utils::{fuzzy_search, limit_and_offset, DbConn, DbPool, ListFn, Queries, ReadFn}, ListingType, SortType, }; @@ -31,19 +32,15 @@ type CommunityViewTuple = ( Option<CommunityBlock>, ); -impl CommunityView { - pub async fn read( - pool: &mut DbPool<'_>, - community_id: CommunityId, - my_person_id: Option<PersonId>, - is_mod_or_admin: Option<bool>, - ) -> Result<Self, Error> { - let conn = &mut get_conn(pool).await?; +fn queries<'a>() -> Queries< + impl ReadFn<'a, CommunityView, (CommunityId, Option<PersonId>, Option<bool>)>, + impl ListFn<'a, CommunityView, CommunityQuery<'a>>, +> { + let all_joins = |query: community::BoxedQuery<'a, Pg>, my_person_id: Option<PersonId>| { // The left join below will return None in this case let person_id_join = my_person_id.unwrap_or(PersonId(-1)); - let mut query = community::table - .find(community_id) + query .inner_join(community_aggregates::table) .left_join( community_follower::table.on( @@ -59,111 +56,68 @@ impl CommunityView { .and(community_block::person_id.eq(person_id_join)), ), ) - .select(( - community::all_columns, - community_aggregates::all_columns, - community_follower::all_columns.nullable(), - community_block::all_columns.nullable(), - )) - .into_boxed(); + }; + + let selection = ( + community::all_columns, + community_aggregates::all_columns, + community_follower::all_columns.nullable(), + community_block::all_columns.nullable(), + ); + + let not_removed_or_deleted = community::removed + .eq(false) + .and(community::deleted.eq(false)); + + let read = move |mut conn: DbConn<'a>, + (community_id, my_person_id, is_mod_or_admin): ( + CommunityId, + Option<PersonId>, + Option<bool>, + )| async move { + let mut query = all_joins( + community::table.find(community_id).into_boxed(), + my_person_id, + ) + .select(selection); // Hide deleted and removed for non-admins or mods if !is_mod_or_admin.unwrap_or(false) { - query = query - .filter(community::removed.eq(false)) - .filter(community::deleted.eq(false)); + query = query.filter(not_removed_or_deleted); } - let (community, counts, follower, blocked) = query.first::<CommunityViewTuple>(conn).await?; - - Ok(CommunityView { - community, - subscribed: CommunityFollower::to_subscribed_type(&follower), - blocked: blocked.is_some(), - counts, - }) - } - - pub async fn is_mod_or_admin( - pool: &mut DbPool<'_>, - person_id: PersonId, - community_id: CommunityId, - ) -> Result<bool, Error> { - let is_mod = - CommunityModeratorView::is_community_moderator(pool, community_id, person_id).await?; - if is_mod { - return Ok(true); - } - - PersonView::is_admin(pool, person_id).await - } -} - -#[derive(Default)] -pub struct CommunityQuery<'a> { - pub listing_type: Option<ListingType>, - pub sort: Option<SortType>, - pub local_user: Option<&'a LocalUser>, - pub search_term: Option<String>, - pub is_mod_or_admin: Option<bool>, - pub show_nsfw: Option<bool>, - pub page: Option<i64>, - pub limit: Option<i64>, -} + query.first::<CommunityViewTuple>(&mut conn).await + }; -impl<'a> CommunityQuery<'a> { - pub async fn list(self, pool: &mut DbPool<'_>) -> Result<Vec<CommunityView>, Error> { + let list = move |mut conn: DbConn<'a>, options: CommunityQuery<'a>| async move { use SortType::*; - let conn = &mut get_conn(pool).await?; + let my_person_id = options.local_user.map(|l| l.person_id); // The left join below will return None in this case - let person_id_join = self.local_user.map(|l| l.person_id).unwrap_or(PersonId(-1)); + let person_id_join = my_person_id.unwrap_or(PersonId(-1)); - let mut query = community::table - .inner_join(community_aggregates::table) + let mut query = all_joins(community::table.into_boxed(), my_person_id) .left_join(local_user::table.on(local_user::person_id.eq(person_id_join))) - .left_join( - community_follower::table.on( - community::id - .eq(community_follower::community_id) - .and(community_follower::person_id.eq(person_id_join)), - ), - ) - .left_join( - community_block::table.on( - community::id - .eq(community_block::community_id) - .and(community_block::person_id.eq(person_id_join)), - ), - ) - .select(( - community::all_columns, - community_aggregates::all_columns, - community_follower::all_columns.nullable(), - community_block::all_columns.nullable(), - )) - .into_boxed(); - - if let Some(search_term) = self.search_term { + .select(selection); + + if let Some(search_term) = options.search_term { let searcher = fuzzy_search(&search_term); query = query .filter(community::name.ilike(searcher.clone())) - .or_filter(community::title.ilike(searcher)); - }; + .or_filter(community::title.ilike(searcher)) + } // Hide deleted and removed for non-admins or mods - if !self.is_mod_or_admin.unwrap_or(false) { - query = query - .filter(community::removed.eq(false)) - .filter(community::deleted.eq(false)) - .filter( - community::hidden - .eq(false) - .or(community_follower::person_id.eq(person_id_join)), - ); + if !options.is_mod_or_admin.unwrap_or(false) { + query = query.filter(not_removed_or_deleted).filter( + community::hidden + .eq(false) + .or(community_follower::person_id.eq(person_id_join)), + ); } - match self.sort.unwrap_or(Hot) { + + match options.sort.unwrap_or(Hot) { Hot | Active => query = query.order_by(community_aggregates::hot_rank.desc()), NewComments | TopDay | TopTwelveHour | TopSixHour | TopHour => { query = query.order_by(community_aggregates::users_active_day.desc()) @@ -182,7 +136,7 @@ impl<'a> CommunityQuery<'a> { TopWeek => query = query.order_by(community_aggregates::users_active_week.desc()), }; - if let Some(listing_type) = self.listing_type { + if let Some(listing_type) = options.listing_type { query = match listing_type { ListingType::Subscribed => query.filter(community_follower::person_id.is_not_null()), // TODO could be this: and(community_follower::person_id.eq(person_id_join)), ListingType::Local => query.filter(community::local.eq(true)), @@ -191,24 +145,69 @@ impl<'a> CommunityQuery<'a> { } // Don't show blocked communities or nsfw communities if not enabled in profile - if self.local_user.is_some() { + if options.local_user.is_some() { query = query.filter(community_block::person_id.is_null()); query = query.filter(community::nsfw.eq(false).or(local_user::show_nsfw.eq(true))); } else { // No person in request, only show nsfw communities if show_nsfw is passed into request - if !self.show_nsfw.unwrap_or(false) { + if !options.show_nsfw.unwrap_or(false) { query = query.filter(community::nsfw.eq(false)); } } - let (limit, offset) = limit_and_offset(self.page, self.limit)?; - let res = query + let (limit, offset) = limit_and_offset(options.page, options.limit)?; + query .limit(limit) .offset(offset) - .load::<CommunityViewTuple>(conn) - .await?; + .load::<CommunityViewTuple>(&mut conn) + .await + }; + + Queries::new(read, list) +} + +impl CommunityView { + pub async fn read( + pool: &mut DbPool<'_>, + community_id: CommunityId, + my_person_id: Option<PersonId>, + is_mod_or_admin: Option<bool>, + ) -> Result<Self, Error> { + queries() + .read(pool, (community_id, my_person_id, is_mod_or_admin)) + .await + } + + pub async fn is_mod_or_admin( + pool: &mut DbPool<'_>, + person_id: PersonId, + community_id: CommunityId, + ) -> Result<bool, Error> { + let is_mod = + CommunityModeratorView::is_community_moderator(pool, community_id, person_id).await?; + if is_mod { + return Ok(true); + } - Ok(res.into_iter().map(CommunityView::from_tuple).collect()) + PersonView::is_admin(pool, person_id).await + } +} + +#[derive(Default)] +pub struct CommunityQuery<'a> { + pub listing_type: Option<ListingType>, + pub sort: Option<SortType>, + pub local_user: Option<&'a LocalUser>, + pub search_term: Option<String>, + pub is_mod_or_admin: Option<bool>, + pub show_nsfw: Option<bool>, + pub page: Option<i64>, + pub limit: Option<i64>, +} + +impl<'a> CommunityQuery<'a> { + pub async fn list(self, pool: &mut DbPool<'_>) -> Result<Vec<CommunityView>, Error> { + queries().list(pool, self).await } } diff --git a/crates/db_views_actor/src/person_mention_view.rs b/crates/db_views_actor/src/person_mention_view.rs index 6bf107a3..6528ab5d 100644 --- a/crates/db_views_actor/src/person_mention_view.rs +++ b/crates/db_views_actor/src/person_mention_view.rs @@ -1,6 +1,7 @@ use crate::structs::PersonMentionView; use diesel::{ dsl::now, + pg::Pg, result::Error, BoolExpressionMethods, ExpressionMethods, @@ -11,6 +12,7 @@ use diesel::{ use diesel_async::RunQueryDsl; use lemmy_db_schema::{ aggregates::structs::CommentAggregates, + aliases, newtypes::{PersonId, PersonMentionId}, schema::{ comment, @@ -34,7 +36,7 @@ use lemmy_db_schema::{ post::Post, }, traits::JoinView, - utils::{get_conn, limit_and_offset, DbPool}, + utils::{get_conn, limit_and_offset, DbConn, DbPool, ListFn, Queries, ReadFn}, CommentSortType, }; @@ -53,46 +55,21 @@ type PersonMentionViewTuple = ( Option<i16>, ); -impl PersonMentionView { - pub async fn read( - pool: &mut DbPool<'_>, - person_mention_id: PersonMentionId, - my_person_id: Option<PersonId>, - ) -> Result<Self, Error> { - let conn = &mut get_conn(pool).await?; - let person_alias_1 = diesel::alias!(person as person1); - +fn queries<'a>() -> Queries< + impl ReadFn<'a, PersonMentionView, (PersonMentionId, Option<PersonId>)>, + impl ListFn<'a, PersonMentionView, PersonMentionQuery>, +> { + let all_joins = |query: person_mention::BoxedQuery<'a, Pg>, my_person_id: Option<PersonId>| { // The left join below will return None in this case let person_id_join = my_person_id.unwrap_or(PersonId(-1)); - let ( - person_mention, - comment, - creator, - post, - community, - recipient, - counts, - creator_banned_from_community, - follower, - saved, - creator_blocked, - my_vote, - ) = person_mention::table - .find(person_mention_id) + query .inner_join(comment::table) .inner_join(person::table.on(comment::creator_id.eq(person::id))) .inner_join(post::table.on(comment::post_id.eq(post::id))) .inner_join(community::table.on(post::community_id.eq(community::id))) - .inner_join(person_alias_1) + .inner_join(aliases::person1) .inner_join(comment_aggregates::table.on(comment::id.eq(comment_aggregates::comment_id))) - .left_join( - community_person_ban::table.on( - community::id - .eq(community_person_ban::community_id) - .and(community_person_ban::person_id.eq(comment::creator_id)), - ), - ) .left_join( community_follower::table.on( post::community_id @@ -121,86 +98,44 @@ impl PersonMentionView { .and(comment_like::person_id.eq(person_id_join)), ), ) - .select(( - person_mention::all_columns, - comment::all_columns, - person::all_columns, - post::all_columns, - community::all_columns, - person_alias_1.fields(person::all_columns), - comment_aggregates::all_columns, - community_person_ban::all_columns.nullable(), - community_follower::all_columns.nullable(), - comment_saved::all_columns.nullable(), - person_block::all_columns.nullable(), - comment_like::score.nullable(), - )) - .first::<PersonMentionViewTuple>(conn) - .await?; - - Ok(PersonMentionView { - person_mention, - comment, - creator, - post, - community, - recipient, - counts, - creator_banned_from_community: creator_banned_from_community.is_some(), - subscribed: CommunityFollower::to_subscribed_type(&follower), - saved: saved.is_some(), - creator_blocked: creator_blocked.is_some(), - my_vote, - }) - } - - /// Gets the number of unread mentions - pub async fn get_unread_mentions( - pool: &mut DbPool<'_>, - my_person_id: PersonId, - ) -> Result<i64, Error> { - use diesel::dsl::count; - let conn = &mut get_conn(pool).await?; - - person_mention::table - .inner_join(comment::table) - .filter(person_mention::recipient_id.eq(my_person_id)) - .filter(person_mention::read.eq(false)) - .filter(comment::deleted.eq(false)) - .filter(comment::removed.eq(false)) - .select(count(person_mention::id)) - .first::<i64>(conn) + }; + + let selection = ( + person_mention::all_columns, + comment::all_columns, + person::all_columns, + post::all_columns, + community::all_columns, + aliases::person1.fields(person::all_columns), + comment_aggregates::all_columns, + community_person_ban::all_columns.nullable(), + community_follower::all_columns.nullable(), + comment_saved::all_columns.nullable(), + person_block::all_columns.nullable(), + comment_like::score.nullable(), + ); + + let read = + move |mut conn: DbConn<'a>, + (person_mention_id, my_person_id): (PersonMentionId, Option<PersonId>)| async move { + all_joins( + person_mention::table.find(person_mention_id).into_boxed(), + my_person_id, + ) + .left_join( + community_person_ban::table.on( + community::id + .eq(community_person_ban::community_id) + .and(community_person_ban::person_id.eq(comment::creator_id)), + ), + ) + .select(selection) + .first::<PersonMentionViewTuple>(&mut conn) .await - } -} - -#[derive(Default)] -pub struct PersonMentionQuery { - pub my_person_id: Option<PersonId>, - pub recipient_id: Option<PersonId>, - pub sort: Option<CommentSortType>, - pub unread_only: Option<bool>, - pub show_bot_accounts: Option<bool>, - pub page: Option<i64>, - pub limit: Option<i64>, -} - -impl PersonMentionQuery { - pub async fn list(self, pool: &mut DbPool<'_>) -> Result<Vec<PersonMentionView>, Error> { - let conn = &mut get_conn(pool).await?; - - let person_alias_1 = diesel::alias!(person as person1); - - // The left join below will return None in this case - let person_id_join = self.my_person_id.unwrap_or(PersonId(-1)); + }; - let mut query = person_mention::table - .inner_join(comment::table) - .inner_join(person::table.on(comment::creator_id.eq(person::id))) - .inner_join(post::table.on(comment::post_id.eq(post::id))) - .inner_join(community::table.on(post::community_id.eq(community::id))) - .inner_join(person_alias_1) - .inner_join(comment_aggregates::table.on(comment::id.eq(comment_aggregates::comment_id))) + let list = move |mut conn: DbConn<'a>, options: PersonMentionQuery| async move { + let mut query = all_joins(person_mention::table.into_boxed(), options.my_person_id) .left_join( community_person_ban::table.on( community::id @@ -213,63 +148,21 @@ impl PersonMentionQuery { ), ), ) - .left_join( - community_follower::table.on( - post::community_id - .eq(community_follower::community_id) - .and(community_follower::person_id.eq(person_id_join)), - ), - ) - .left_join( - comment_saved::table.on( - comment::id - .eq(comment_saved::comment_id) - .and(comment_saved::person_id.eq(person_id_join)), - ), - ) - .left_join( - person_block::table.on( - comment::creator_id - .eq(person_block::target_id) - .and(person_block::person_id.eq(person_id_join)), - ), - ) - .left_join( - comment_like::table.on( - comment::id - .eq(comment_like::comment_id) - .and(comment_like::person_id.eq(person_id_join)), - ), - ) - .select(( - person_mention::all_columns, - comment::all_columns, - person::all_columns, - post::all_columns, - community::all_columns, - person_alias_1.fields(person::all_columns), - comment_aggregates::all_columns, - community_person_ban::all_columns.nullable(), - community_follower::all_columns.nullable(), - comment_saved::all_columns.nullable(), - person_block::all_columns.nullable(), - comment_like::score.nullable(), - )) - .into_boxed(); + .select(selection); - if let Some(recipient_id) = self.recipient_id { + if let Some(recipient_id) = options.recipient_id { query = query.filter(person_mention::recipient_id.eq(recipient_id)); } - if self.unread_only.unwrap_or(false) { + if options.unread_only.unwrap_or(false) { query = query.filter(person_mention::read.eq(false)); } - if !self.show_bot_accounts.unwrap_or(true) { + if !options.show_bot_accounts.unwrap_or(true) { query = query.filter(person::bot_account.eq(false)); }; - query = match self.sort.unwrap_or(CommentSortType::Hot) { + query = match options.sort.unwrap_or(CommentSortType::Hot) { CommentSortType::Hot => query.then_order_by(comment_aggregates::hot_rank.desc()), CommentSortType::Controversial => { query.then_order_by(comment_aggregates::controversy_rank.desc()) @@ -279,15 +172,63 @@ impl PersonMentionQuery { CommentSortType::Top => query.order_by(comment_aggregates::score.desc()), }; - let (limit, offset) = limit_and_offset(self.page, self.limit)?; + let (limit, offset) = limit_and_offset(options.page, options.limit)?; - let res = query + query .limit(limit) .offset(offset) - .load::<PersonMentionViewTuple>(conn) - .await?; + .load::<PersonMentionViewTuple>(&mut conn) + .await + }; + + Queries::new(read, list) +} + +impl PersonMentionView { + pub async fn read( + pool: &mut DbPool<'_>, + person_mention_id: PersonMentionId, + my_person_id: Option<PersonId>, + ) -> Result<Self, Error> { + queries() + .read(pool, (person_mention_id, my_person_id)) + .await + } + + /// Gets the number of unread mentions + pub async fn get_unread_mentions( + pool: &mut DbPool<'_>, + my_person_id: PersonId, + ) -> Result<i64, Error> { + use diesel::dsl::count; + let conn = &mut get_conn(pool).await?; - Ok(res.into_iter().map(PersonMentionView::from_tuple).collect()) + person_mention::table + .inner_join(comment::table) + .filter(person_mention::recipient_id.eq(my_person_id)) + .filter(person_mention::read.eq(false)) + .filter(comment::deleted.eq(false)) + .filter(comment::removed.eq(false)) + .select(count(person_mention::id)) + .first::<i64>(conn) + .await + } +} + +#[derive(Default)] +pub struct PersonMentionQuery { + pub my_person_id: Option<PersonId>, + pub recipient_id: Option<PersonId>, + pub sort: Option<CommentSortType>, + pub unread_only: Option<bool>, + pub show_bot_accounts: Option<bool>, + pub page: Option<i64>, + pub limit: Option<i64>, +} + +impl PersonMentionQuery { + pub async fn list(self, pool: &mut DbPool<'_>) -> Result<Vec<PersonMentionView>, Error> { + queries().list(pool, self).await } } diff --git a/crates/db_views_actor/src/person_view.rs b/crates/db_views_actor/src/person_view.rs index 908fbaab..dd4edcaf 100644 --- a/crates/db_views_actor/src/person_view.rs +++ b/crates/db_views_actor/src/person_view.rs @@ -1,6 +1,7 @@ use crate::structs::PersonView; use diesel::{ dsl::now, + pg::Pg, result::Error, BoolExpressionMethods, ExpressionMethods, @@ -15,23 +16,82 @@ use lemmy_db_schema::{ schema::{person, person_aggregates}, source::person::Person, traits::JoinView, - utils::{fuzzy_search, get_conn, limit_and_offset, DbPool}, + utils::{fuzzy_search, get_conn, limit_and_offset, DbConn, DbPool, ListFn, Queries, ReadFn}, PersonSortType, }; -use std::iter::Iterator; type PersonViewTuple = (Person, PersonAggregates); -impl PersonView { - pub async fn read(pool: &mut DbPool<'_>, person_id: PersonId) -> Result<Self, Error> { - let conn = &mut get_conn(pool).await?; - let res = person::table - .find(person_id) +enum ListMode { + Admins, + Banned, + Query(PersonQuery), +} + +fn queries<'a>( +) -> Queries<impl ReadFn<'a, PersonView, PersonId>, impl ListFn<'a, PersonView, ListMode>> { + let all_joins = |query: person::BoxedQuery<'a, Pg>| { + query .inner_join(person_aggregates::table) .select((person::all_columns, person_aggregates::all_columns)) - .first::<PersonViewTuple>(conn) - .await?; - Ok(Self::from_tuple(res)) + }; + + let read = move |mut conn: DbConn<'a>, person_id: PersonId| async move { + all_joins(person::table.find(person_id).into_boxed()) + .first::<PersonViewTuple>(&mut conn) + .await + }; + + let list = move |mut conn: DbConn<'a>, mode: ListMode| async move { + let mut query = all_joins(person::table.into_boxed()); + match mode { + ListMode::Admins => { + query = query + .filter(person::admin.eq(true)) + .filter(person::deleted.eq(false)) + .order_by(person::published); + } + ListMode::Banned => { + query = query + .filter( + person::banned.eq(true).and( + person::ban_expires + .is_null() + .or(person::ban_expires.gt(now)), + ), + ) + .filter(person::deleted.eq(false)); + } + ListMode::Query(options) => { + if let Some(search_term) = options.search_term { + let searcher = fuzzy_search(&search_term); + query = query + .filter(person::name.ilike(searcher.clone())) + .or_filter(person::display_name.ilike(searcher)); + } + + query = match options.sort.unwrap_or(PersonSortType::CommentScore) { + PersonSortType::New => query.order_by(person::published.desc()), + PersonSortType::Old => query.order_by(person::published.asc()), + PersonSortType::MostComments => query.order_by(person_aggregates::comment_count.desc()), + PersonSortType::CommentScore => query.order_by(person_aggregates::comment_score.desc()), + PersonSortType::PostScore => query.order_by(person_aggregates::post_score.desc()), + PersonSortType::PostCount => query.order_by(person_aggregates::post_count.desc()), + }; + + let (limit, offset) = limit_and_offset(options.page, options.limit)?; + query = query.limit(limit).offset(offset); + } + } + query.load::<PersonViewTuple>(&mut conn).await + }; + + Queries::new(read, list) +} + +impl PersonView { + pub async fn read(pool: &mut DbPool<'_>, person_id: PersonId) -> Result<Self, Error> { + queries().read(pool, person_id).await } pub async fn is_admin(pool: &mut DbPool<'_>, person_id: PersonId) -> Result<bool, Error> { @@ -44,37 +104,13 @@ impl PersonView { .await?; Ok(is_admin) } - pub async fn admins(pool: &mut DbPool<'_>) -> Result<Vec<Self>, Error> { - let conn = &mut get_conn(pool).await?; - let admins = person::table - .inner_join(person_aggregates::table) - .select((person::all_columns, person_aggregates::all_columns)) - .filter(person::admin.eq(true)) - .filter(person::deleted.eq(false)) - .order_by(person::published) - .load::<PersonViewTuple>(conn) - .await?; - Ok(admins.into_iter().map(Self::from_tuple).collect()) + pub async fn admins(pool: &mut DbPool<'_>) -> Result<Vec<Self>, Error> { + queries().list(pool, ListMode::Admins).await } pub async fn banned(pool: &mut DbPool<'_>) -> Result<Vec<Self>, Error> { - let conn = &mut get_conn(pool).await?; - let banned = person::table - .inner_join(person_aggregates::table) - .select((person::all_columns, person_aggregates::all_columns)) - .filter( - person::banned.eq(true).and( - person::ban_expires - .is_null() - .or(person::ban_expires.gt(now)), - ), - ) - .filter(person::deleted.eq(false)) - .load::<PersonViewTuple>(conn) - .await?; - - Ok(banned.into_iter().map(Self::from_tuple).collect()) + queries().list(pool, ListMode::Banned).await } } @@ -88,34 +124,7 @@ pub struct PersonQuery { impl PersonQuery { pub async fn list(self, pool: &mut DbPool<'_>) -> Result<Vec<PersonView>, Error> { - let conn = &mut get_conn(pool).await?; - let mut query = person::table - .inner_join(person_aggregates::table) - .select((person::all_columns, person_aggregates::all_columns)) - .into_boxed(); - - if let Some(search_term) = self.search_term { - let searcher = fuzzy_search(&search_term); - query = query - .filter(person::name.ilike(searcher.clone())) - .or_filter(person::display_name.ilike(searcher)); - } - - query = match self.sort.unwrap_or(PersonSortType::CommentScore) { - PersonSortType::New => query.order_by(person::published.desc()), - PersonSortType::Old => query.order_by(person::published.asc()), - PersonSortType::MostComments => query.order_by(person_aggregates::comment_count.desc()), - PersonSortType::CommentScore => query.order_by(person_aggregates::comment_score.desc()), - PersonSortType::PostScore => query.order_by(person_aggregates::post_score.desc()), - PersonSortType::PostCount => query.order_by(person_aggregates::post_count.desc()), - }; - - let (limit, offset) = limit_and_offset(self.page, self.limit)?; - query = query.limit(limit).offset(offset); - - let res = query.load::<PersonViewTuple>(conn).await?; - - Ok(res.into_iter().map(PersonView::from_tuple).collect()) + queries().list(pool, ListMode::Query(self)).await } } -- 2.44.1