We know the sort order and the page number we are loading before we start building any SQL statement at all. Regardless of any customized user preferences, these are foundational in terms of filtering out data.
For /c/All - page number is useful. It is when you get into /c/Subscribed with a blend of communities that the trouble begins.
There is also the behavior people might expect with “Top 1 hour” and running out of posts to read. On that specific choice, they are likely to accept it… but with Hot and Active…
So what exactly are Hot and Active… and how far back in time do they go?
hot_rank: 1728,
hot_rank_active: 1728,
-- Note: 1728 is the result of the hot_rank function, with a score of 1, posted now
-- hot_rank = 10000*log10(1 + 3)/Power(2, 1.8)
Brainstorming…
We know the sort order and the page number we are loading before we start building any SQL statement at all. Regardless of any customized user preferences, these are foundational in terms of filtering out data.
For /c/All - page number is useful. It is when you get into /c/Subscribed with a blend of communities that the trouble begins.
There is also the behavior people might expect with “Top 1 hour” and running out of posts to read. On that specific choice, they are likely to accept it… but with Hot and Active…
So what exactly are Hot and Active… and how far back in time do they go?
For post listing, these are the three that aren’t simple to grasp:
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()),
Difference between hot_rank and hot_rank_active
SET hot_rank = hot_rank(a.score, a.published), hot_rank_active = hot_rank(a.score, a.newest_comment_time_necro)"
lemmy@lemmy_alpha LOG: duration: 50.220 ms execute : WITH batch AS (SELECT a.id FROM post_aggregates a WHERE a.published > $1 AND (a.hot_rank != 0 OR a.hot_rank_active != 0) ORDER BY a.published LIMIT $2 FOR UPDATE SKIP LOCKED) UPDATE post_aggregates a SET hot_rank = hot_rank(a.score, a.published), hot_rank_active = hot_rank(a.score, a.newest_comment_time_necro) FROM batch WHERE a.id = batch.id RETURNING a.published; 2023-08-18 09:00:34.578 MST [1877420] lemmy@lemmy_alpha DETAIL: parameters: $1 = '2023-08-16 23:40:31.149267', $2 = '1000'
CREATE FUNCTION public.hot_rank(score numeric, published timestamp without time zone) RETURNS integer LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE AS $$ DECLARE hours_diff numeric := EXTRACT(EPOCH FROM (timezone('utc', now()) - published)) / 3600; BEGIN IF (hours_diff > 0) THEN RETURN floor(10000 * log(greatest (1, score + 3)) / power((hours_diff + 2), 1.8))::integer; ELSE RETURN 0; END IF; END; $$;