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.
Lemmy's internal data performance is so horribly slow and crash-causing that I think the last thing they want is even more popular data.
Video is more data, popularity is more data. For whatever reason, at every turn, I've seen developers turn away from scaling options like Memcache, Redis, or just abandoning ORM data management and rewriting the data interfaces by hand....
That's already true for images that are hot linked routinely, so I don't think video really changes it.
I've been baffled since June why data and fixing lemmy's data coding hasn't been front and center. It's pretty wild to witness so many come to Lemmy and then turn away... Elon Musk has been flocking people, Reddit, etc. It's as if the project wants to make code that won't work on any data. It's baffeling.