• RoundSparrow @ BT@bulletintree.comOPM
    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 @ BT@bulletintree.comOPM
      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 @ BT@bulletintree.comOPM
        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 @ BT@bulletintree.comOPM
          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 @ BT@bulletintree.comOPM
        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)"