Tenho um cliente com um caso de uso bizarro que deseja que o TOP possa realizar um concurso.
Comecei com discourse/app/models/top_topic.rb at main · discourse/discourse · GitHub e criei o código abaixo, mas ele não está correspondendo ao TOP. Estou perdendo algo óbvio? Minha ideia de fallback é simplesmente buscar os arquivos JSON diariamente com um job cron e criar um script de JSON para CSV para os dados que eles precisam.
-- [params]
-- date :start_date = '2019-05-01'
-- date :stop_date = '2019-05-30'
-- int :log_views_multiplier = 2
-- int :least_likes_per_post_multiplier = 3
-- int :num_topics = 100
WITH top_topics AS (
SELECT t.like_count AS likes_count,
p.like_count AS score,
t.id AS topic_id,
t.posts_count AS posts_count,
p.like_count AS op_likes_count,
t.title AS topic_name,
t.views AS views_count,
p.user_id AS user_id,
t.category_id AS category_id
FROM topics t
JOIN posts p ON p.topic_id = t.id AND p.post_number = 1
WHERE t.created_at > to_date(:start_date, 'YYYY-MM-DD')
AND t.created_at < to_date(:stop_date, 'YYYY-MM-DD')
),
top AS (
SELECT log(GREATEST(views_count, 1)) * :log_views_multiplier +
op_likes_count * 0.5 +
CASE WHEN top_topics.likes_count > 0 AND top_topics.posts_count > 0
THEN
LEAST(top_topics.likes_count / top_topics.posts_count, :least_likes_per_post_multiplier)
ELSE 0
END +
CASE WHEN topics.posts_count < 10 THEN
0 - ((10 - topics.posts_count) / 20) * op_likes_count
ELSE
10
END +
log(GREATEST(top_topics.posts_count, 1))
AS score,
topic_id
FROM top_topics
LEFT JOIN topics ON topics.id = top_topics.topic_id AND
topics.deleted_at IS NULL
)
SELECT topic_id, topic_name, t.user_id, u.username, likes_count, category_id
FROM top_topics t
LEFT JOIN users u
ON t.user_id = u.id
ORDER BY likes_count DESC
LIMIT :num_topics