8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
|
# File 'app/models/topic_hot_score.rb', line 8
def self.update_scores(max = DEFAULT_BATCH_SIZE)
now = Time.zone.now
args = {
now: now,
gravity: SiteSetting.hot_topics_gravity,
max: max,
private_message: Archetype.private_message,
recent_cutoff: now - SiteSetting.hot_topics_recent_days.days,
regular: Post.types[:regular],
}
DB.exec(<<~SQL, args)
INSERT INTO topic_hot_scores (
topic_id,
score,
recent_likes,
recent_posters,
created_at,
updated_at
)
SELECT
topics.id,
0.0,
0,
0,
:now,
:now
FROM topics
LEFT OUTER JOIN topic_hot_scores ON topic_hot_scores.topic_id = topics.id
WHERE topic_hot_scores.topic_id IS NULL
AND topics.deleted_at IS NULL
AND topics.archetype <> :private_message
AND topics.created_at <= :now
ORDER BY
CASE WHEN topics.pinned_at IS NOT NULL THEN 0 ELSE 1 END ASC,
topics.bumped_at desc
LIMIT :max
SQL
DB.exec(<<~SQL, args)
UPDATE topic_hot_scores thsOrig
SET
recent_likes = COALESCE(new_values.likes_count, 0),
recent_posters = COALESCE(new_values.unique_participants, 0),
recent_first_bumped_at = COALESCE(new_values.first_bumped_at, ths.recent_first_bumped_at)
FROM
topic_hot_scores ths
LEFT OUTER JOIN
(
SELECT
t.id AS topic_id,
COUNT(DISTINCT p.user_id) AS unique_participants,
(
SELECT COUNT(distinct pa.user_id)
FROM post_actions pa
JOIN posts p2 ON p2.id = pa.post_id
WHERE p2.topic_id = t.id
AND p2.post_type = :regular
AND p2.deleted_at IS NULL
AND p2.user_deleted = false
AND pa.post_action_type_id = 2 -- action_type for 'like'
AND pa.created_at >= :recent_cutoff
AND pa.deleted_at IS NULL
) AS likes_count,
MIN(p.created_at) AS first_bumped_at
FROM
topics t
JOIN
posts p ON t.id = p.topic_id
WHERE
p.created_at >= :recent_cutoff
AND t.archetype <> 'private_message'
AND t.deleted_at IS NULL
AND p.deleted_at IS NULL
AND p.user_deleted = false
AND t.created_at <= :now
AND t.bumped_at >= :recent_cutoff
AND p.created_at < :now
AND p.created_at >= :recent_cutoff
AND p.post_type = :regular
GROUP BY
t.id
) AS new_values
ON ths.topic_id = new_values.topic_id
WHERE thsOrig.topic_id = ths.topic_id
SQL
sql = <<~SQL
WITH topic_ids AS (
SELECT topic_id FROM (
SELECT th3.topic_id FROM topic_hot_scores th3
JOIN topics t3 on t3.id = th3.topic_id
ORDER BY t3.bumped_at DESC
LIMIT :max
) Y
UNION ALL
SELECT topic_id FROM (
SELECT th2.topic_id FROM topic_hot_scores th2
ORDER BY th2.score DESC, th2.recent_first_bumped_at DESC NULLS LAST
LIMIT :max
) X
)
UPDATE topic_hot_scores ths
SET score = (
CASE WHEN topics.created_at > :recent_cutoff
THEN ths.recent_likes ELSE topics.like_count END
) /
(EXTRACT(EPOCH FROM (:now - topics.created_at)) / 3600 + 2) ^ :gravity
+
CASE WHEN ths.recent_first_bumped_at IS NULL THEN 0 ELSE
(ths.recent_likes + ths.recent_posters - 1) /
(EXTRACT(EPOCH FROM (:now - recent_first_bumped_at)) / 3600 + 2) ^ :gravity
END
,
updated_at = :now
FROM topics
WHERE topics.id IN (
SELECT topic_id FROM topic_ids
) AND ths.topic_id = topics.id AND topics.created_at <= :now
SQL
DB.exec(sql, args)
DiscourseEvent.trigger(:topic_hot_scores_updated)
end
|