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.
who would have predicted that Elon Musk would do all the wild things he did with Twitter. Reddit pissing everyone off in June... pretty odd how audiences are behaving in 2023 towards all this. Oh yha, Threads, that coming on the scene too. 2023 has really been odd for audiences.
The SQL speaks for itself, but I don't know what's going on in terms of why people are treating social media platforms like Lemmy, Twitter, Threads, Reddit this year so unusually. This SQL statement kind of thing has been covered in so many books, conferences, etc. It's like forgotten history now in the era of Elon Musk X and Reddit Apollo times.
I don't know what to say other than I can try to hire a translator or teacher to explain how this SQL problem is obvious and well understood 13 years ago. I mean, there was a whole "NoSQL movement" because of this kind of thing. But I clearly can't get people to hear past all the Elon Musk, Threads, Lemmy from Reddit ... and I'm left describing it as 'social hazing' or whatever is gong on with social media.
Lemmy has like 5 different Rust programming communities, but nobody fixing Lemmy. It's surreal in 2023 the Elon Musk X days. I think it's making all of us uncomfortable. The social movement underway.