• RoundSparrow @ BTOPM
    1•1 year ago

    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?

    • RoundSparrow @ BTOPM
      1•1 year ago

      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()),
      • RoundSparrow @ BTOPM
        1•1 year ago
            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)
        • RoundSparrow @ BTOPM
          1•1 year ago
          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'
          • RoundSparrow @ BTOPM
            1•1 year ago
            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;
$$;
        • RoundSparrow @ BTOPM
          1•1 year ago

          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)"