I'm trying to wrangle in and get 'back to basics' with Lemmy's Diesel code and at every turn I run into not understanding the complexity of the Rust code.
You may want to do a GitHub checkout of this branch if you want to see what I'm attempting: https://github.com/LemmyNet/lemmy/pull/3865
I'm basing my experiments off the code in that pull request, which links to this branch on this repository: https://github.com/dullbananas/lemmy/tree/post-view-same-joins
Right now Lemmy's Diesel code spins up many SQL table joins and for an anonymous user it just passes a -1 user id to all the joins - and it really makes for difficult to read SQL statements. So I decided to experiment with removing as much logic as I could to get the bare-bones behavior on generating the desired SQL statement....
I copied/pasted the queries function/method and gave it a new name, kept removing as much as I could see that referenced the user being logged-in vs. anonymous, and got to this point:
fn queries_anonymous<'a>() -> Queries<
impl ReadFn<'a, PostView, (PostId, Option, bool)>,
impl ListFn<'a, PostView, PostQuery<'a>>,
> {
let is_creator_banned_from_community = exists(
community_person_ban::table.filter(
post_aggregates::community_id
.eq(community_person_ban::community_id)
.and(community_person_ban::person_id.eq(post_aggregates::creator_id)),
),
);
// how do we eliminate these next 3 assignments, this is anonymous user, not needed
let is_saved = |person_id_join| {
exists(
post_saved::table.filter(
post_aggregates::post_id
.eq(post_saved::post_id)
.and(post_saved::person_id.eq(person_id_join)),
),
)
};
let is_read = |person_id_join| {
exists(
post_read::table.filter(
post_aggregates::post_id
.eq(post_read::post_id)
.and(post_read::person_id.eq(person_id_join)),
),
)
};
let is_creator_blocked = |person_id_join| {
exists(
person_block::table.filter(
post_aggregates::creator_id
.eq(person_block::target_id)
.and(person_block::person_id.eq(person_id_join)),
),
)
};
let all_joins = move |query: post_aggregates::BoxedQuery<'a, Pg>, my_person_id: Option| {
// The left join below will return None in this case
let person_id_join = my_person_id.unwrap_or(PersonId(-1));
query
.inner_join(person::table)
.inner_join(community::table)
.inner_join(post::table)
// how do we eliminate these next 3 joins that are user/person references?
.left_join(
community_follower::table.on(
post_aggregates::community_id
.eq(community_follower::community_id)
),
)
.left_join(
community_moderator::table.on(
post::community_id
.eq(community_moderator::community_id)
),
)
.left_join(
post_like::table.on(
post_aggregates::post_id
.eq(post_like::post_id)
),
)
.left_join(
person_post_aggregates::table.on(
post_aggregates::post_id
.eq(person_post_aggregates::post_id)
),
)
.select((
post::all_columns,
person::all_columns,
community::all_columns,
is_creator_banned_from_community,
post_aggregates::all_columns,
CommunityFollower::select_subscribed_type(),
// how do we eliminate these next 3 for anonymous?
is_saved(person_id_join),
is_read(person_id_join),
is_creator_blocked(person_id_join),
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, bool)| async move {
let mut query = all_joins(
post_aggregates::table
.filter(post_aggregates::post_id.eq(post_id))
.into_boxed(),
my_person_id,
);
query = query
.filter(community::removed.eq(false))
.filter(post::removed.eq(false))
;
query.first::(&mut conn).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 mut query = all_joins(post_aggregates::table.into_boxed(), person_id);
query = query
.filter(community::deleted.eq(false))
.filter(post::deleted.eq(false));
// every SELECT has to labor away on removed filtering
query = query
.filter(community::removed.eq(false))
.filter(post::removed.eq(false));
if options.community_id.is_none() {
query = query.then_order_by(post_aggregates::featured_local.desc());
} 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) = options.creator_id {
query = query.filter(post_aggregates::creator_id.eq(creator_id));
}
if let Some(url_search) = options.url_search {
query = query.filter(post::url.eq(url_search));
}
if let Some(search_term) = options.search_term {
let searcher = fuzzy_search(&search_term);
query = query.filter(
post::name
.ilike(searcher.clone())
.or(post::body.ilike(searcher)),
);
}
query = query
.filter(post::nsfw.eq(false))
.filter(community::nsfw.eq(false));
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()),
SortType::Hot => query
.then_order_by(post_aggregates::hot_rank.desc())
.then_order_by(post_aggregates::published.desc()),
SortType::Controversial => query.then_order_by(post_aggregates::controversy_rank.desc()),
SortType::New => query.then_order_by(post_aggregates::published.desc()),
SortType::Old => query.then_order_by(post_aggregates::published.asc()),
SortType::NewComments => query.then_order_by(post_aggregates::newest_comment_time.desc()),
SortType::MostComments => query
.then_order_by(post_aggregates::comments.desc())
.then_order_by(post_aggregates::published.desc()),
SortType::TopAll => query
.then_order_by(post_aggregates::score.desc())
.then_order_by(post_aggregates::published.desc()),
SortType::TopYear => query
.filter(post_aggregates::published.gt(now - 1.years()))
.then_order_by(post_aggregates::score.desc())
.then_order_by(post_aggregates::published.desc()),
SortType::TopMonth => query
.filter(post_aggregates::published.gt(now - 1.months()))
.then_order_by(post_aggregates::score.desc())
.then_order_by(post_aggregates::published.desc()),
SortType::TopWeek => query
.filter(post_aggregates::published.gt(now - 1.weeks()))
.then_order_by(post_aggregates::score.desc())
.then_order_by(post_aggregates::published.desc()),
SortType::TopDay => query
.filter(post_aggregates::published.gt(now - 1.days()))
.then_order_by(post_aggregates::score.desc())
.then_order_by(post_aggregates::published.desc()),
SortType::TopHour => query
.filter(post_aggregates::published.gt(now - 1.hours()))
.then_order_by(post_aggregates::score.desc())
.then_order_by(post_aggregates::published.desc()),
SortType::TopSixHour => query
.filter(post_aggregates::published.gt(now - 6.hours()))
.then_order_by(post_aggregates::score.desc())
.then_order_by(post_aggregates::published.desc()),
SortType::TopTwelveHour => query
.filter(post_aggregates::published.gt(now - 12.hours()))
.then_order_by(post_aggregates::score.desc())
.then_order_by(post_aggregates::published.desc()),
SortType::TopThreeMonths => query
.filter(post_aggregates::published.gt(now - 3.months()))
.then_order_by(post_aggregates::score.desc())
.then_order_by(post_aggregates::published.desc()),
SortType::TopSixMonths => query
.filter(post_aggregates::published.gt(now - 6.months()))
.then_order_by(post_aggregates::score.desc())
.then_order_by(post_aggregates::published.desc()),
SortType::TopNineMonths => query
.filter(post_aggregates::published.gt(now - 9.months()))
.then_order_by(post_aggregates::score.desc())
.then_order_by(post_aggregates::published.desc()),
};
let (limit, offset) = limit_and_offset(options.page, options.limit)?;
query = query.limit(limit).offset(offset);
debug!("Post View Query: {:?}", debug_query::(&query));
query.load::(&mut conn).await
};
Queries::new(read, list)
}
This compiles, but I can not progress further. There are 3 joins more that aren't really needed for an anonymous user... but the interdependent Rust object structures I can't unravel enough to remove them from the code.
For example, Lemmy allows you to "save" a post, but an anonymous user doesn't have that ability - so how can I remove the JOIN + select related to that while still satisfying the object requirements? I even tried creating a variation of PostViewTuple object without one of the bool fields, but it all cascades into 50 lines of compiler errors. Thank you.
several people have confirmed it... I haven't seen them explain how exactly, but they seem convinced it is causing crashes so they blocked it. Lemmy is practically in the realm of voodoo PostgreSQL at this point. Since April or May it's been scaling very poorly as data gets added.