I need help with Lemmy code, Diesel object - can I remove these SQL joins that aren't needed?
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.
No comments yet. You could be first!