A platform for community discussion. Free, open, simple.
# frozen_string_literal: true
class TopicFeaturedUsers
FREQUENT_POSTER_COUNT = 2
attr_reader :topic
def initialize(topic)
@topic = topic
end
def self.count
4
end
def self.recent_poster_count
count - FREQUENT_POSTER_COUNT
end
# Chooses which topic users to feature
def choose(args = {})
self.class.ensure_consistency!(topic.id.to_i)
update_participant_count
end
def user_ids
slot_user_ids.uniq.compact
end
def recent_user_ids
slot_user_ids.last(self.class.recent_poster_count).compact
end
def self.ensure_consistency!(topic_id = nil)
filter = "#{"AND t.id = #{topic_id.to_i}" if topic_id}"
filter2 = "#{"AND tt.id = #{topic_id.to_i}" if topic_id}"
# The topic list shows up to five posters in this order: the OP, two frequent posters,
# and two recent posters. Recent posters are excluded from frequent posters, and the OP
# is excluded from both. The latest poster is handled separately and replaces a recent
# poster unless the OP is the latest poster.
sql = <<SQL
WITH poster_stats as (
SELECT
t.id,
t.user_id as topic_user_id,
t.last_post_user_id,
p.user_id,
COUNT(*) post_count,
MAX(p.created_at) last_post_date,
MAX(p.id) last_post_id,
ROW_NUMBER() OVER(PARTITION BY t.id ORDER BY MAX(p.created_at) DESC, MAX(p.id) DESC) as recent_rank
FROM topics t
JOIN posts p ON p.topic_id = t.id
WHERE p.deleted_at IS NULL AND
NOT p.hidden AND
p.post_type in (#{Topic.visible_post_types.join(",")}) AND
p.user_id <> t.user_id AND
p.user_id <> t.last_post_user_id
#{filter}
GROUP BY t.id, t.user_id, t.last_post_user_id, p.user_id
),
selected_recent_posters as (
SELECT id, user_id, recent_rank + #{FREQUENT_POSTER_COUNT} as rank
FROM poster_stats
WHERE topic_user_id = last_post_user_id AND recent_rank <= #{recent_poster_count}
UNION ALL
SELECT id, user_id, recent_rank + #{FREQUENT_POSTER_COUNT} as rank
FROM poster_stats
WHERE topic_user_id <> last_post_user_id AND recent_rank <= #{recent_poster_count - 1}
),
selected_frequent_posters as (
SELECT
id,
user_id,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY post_count DESC, last_post_date DESC, last_post_id DESC) as rank
FROM poster_stats
WHERE topic_user_id = last_post_user_id AND recent_rank > #{recent_poster_count}
UNION ALL
SELECT
id,
user_id,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY post_count DESC, last_post_date DESC, last_post_id DESC) as rank
FROM poster_stats
WHERE topic_user_id <> last_post_user_id AND recent_rank > #{recent_poster_count - 1}
),
selected_topic_posters as (
SELECT id, user_id, rank
FROM selected_frequent_posters
WHERE rank <= #{FREQUENT_POSTER_COUNT}
UNION ALL
SELECT id, user_id, rank
FROM selected_recent_posters
)
UPDATE topics tt
SET
featured_user1_id = x.featured_user1,
featured_user2_id = x.featured_user2,
featured_user3_id = x.featured_user3,
featured_user4_id = x.featured_user4
FROM topics AS tt2
LEFT OUTER JOIN (
SELECT
selected_topic_posters.id,
MAX(case when selected_topic_posters.rank = 1 then selected_topic_posters.user_id end) featured_user1,
MAX(case when selected_topic_posters.rank = 2 then selected_topic_posters.user_id end) featured_user2,
MAX(case when selected_topic_posters.rank = 3 then selected_topic_posters.user_id end) featured_user3,
MAX(case when selected_topic_posters.rank = 4 then selected_topic_posters.user_id end) featured_user4
FROM selected_topic_posters
GROUP BY selected_topic_posters.id
) x ON x.id = tt2.id
WHERE tt.id = tt2.id AND
(
COALESCE(tt.featured_user1_id,-99) <> COALESCE(x.featured_user1,-99) OR
COALESCE(tt.featured_user2_id,-99) <> COALESCE(x.featured_user2,-99) OR
COALESCE(tt.featured_user3_id,-99) <> COALESCE(x.featured_user3,-99) OR
COALESCE(tt.featured_user4_id,-99) <> COALESCE(x.featured_user4,-99)
)
#{filter2}
SQL
DB.exec(sql)
end
private
def slot_user_ids
[
topic.featured_user1_id,
topic.featured_user2_id,
topic.featured_user3_id,
topic.featured_user4_id,
]
end
def update_participant_count
DB.exec(<<~SQL, topic_id: topic.id)
UPDATE topics
SET participant_count = (
SELECT COUNT(DISTINCT user_id)
FROM posts
WHERE topic_id = :topic_id
AND NOT hidden
AND post_type IN (#{Topic.visible_post_types.join(",")})
AND deleted_at IS NULL
)
WHERE id = :topic_id
SQL
end
end