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
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
|
# File 'app/models/directory_item.rb', line 32
def self.refresh_period!(period_type, force: false)
DiscourseEvent.trigger("before_directory_refresh")
Discourse.redis.set("directory_#{period_types[period_type]}", Time.zone.now.to_i)
return unless SiteSetting.enable_user_directory? || force
since =
case period_type
when :daily
1.day.ago
when :weekly
1.week.ago
when :monthly
1.month.ago
when :quarterly
3.months.ago
when :yearly
1.year.ago
else
1000.years.ago
end
ActiveRecord::Base.transaction do
DB.exec(
"DELETE FROM directory_items
USING directory_items di
LEFT JOIN users u ON (u.id = user_id AND u.active AND u.silenced_till IS NULL AND u.id > 0)
WHERE di.id = directory_items.id AND
u.id IS NULL AND
di.period_type = :period_type",
period_type: period_types[period_type],
)
column_names =
DirectoryColumn.automatic_column_names + DirectoryColumn.plugin_directory_columns
DB.exec(
"INSERT INTO directory_items(period_type, user_id, #{column_names.map(&:to_s).join(", ")})
SELECT
:period_type,
u.id,
#{Array.new(column_names.count) { |_| 0 }.join(", ")}
FROM users u
LEFT JOIN directory_items di ON di.user_id = u.id AND di.period_type = :period_type
WHERE di.id IS NULL AND u.id > 0 AND u.silenced_till IS NULL AND u.active
#{SiteSetting.must_approve_users ? "AND u.approved" : ""}
",
period_type: period_types[period_type],
)
query_args = {
period_type: period_types[period_type],
since: since,
like_type: UserAction::LIKE,
was_liked_type: UserAction::WAS_LIKED,
new_topic_type: UserAction::NEW_TOPIC,
reply_type: UserAction::REPLY,
regular_post_type: Post.types[:regular],
}
DB.exec(
"WITH x AS (SELECT
u.id user_id,
SUM(CASE WHEN p.id IS NOT NULL AND t.id IS NOT NULL AND ua.action_type = :was_liked_type THEN 1 ELSE 0 END) likes_received,
SUM(CASE WHEN p.id IS NOT NULL AND t.id IS NOT NULL AND ua.action_type = :like_type THEN 1 ELSE 0 END) likes_given,
COALESCE((SELECT COUNT(topic_id) FROM topic_views AS v WHERE v.user_id = u.id AND v.viewed_at > :since), 0) topics_entered,
COALESCE((SELECT COUNT(id) FROM user_visits AS uv WHERE uv.user_id = u.id AND uv.visited_at > :since), 0) days_visited,
COALESCE((SELECT SUM(posts_read) FROM user_visits AS uv2 WHERE uv2.user_id = u.id AND uv2.visited_at > :since), 0) posts_read,
SUM(CASE WHEN t2.id IS NOT NULL AND ua.action_type = :new_topic_type THEN 1 ELSE 0 END) topic_count,
SUM(CASE WHEN p.id IS NOT NULL AND t.id IS NOT NULL AND ua.action_type = :reply_type THEN 1 ELSE 0 END) post_count
FROM users AS u
LEFT OUTER JOIN user_actions AS ua ON ua.user_id = u.id AND COALESCE(ua.created_at, :since) > :since
LEFT OUTER JOIN posts AS p ON ua.target_post_id = p.id AND p.deleted_at IS NULL AND p.post_type = :regular_post_type AND NOT p.hidden
LEFT OUTER JOIN topics AS t ON p.topic_id = t.id AND t.archetype = 'regular' AND t.deleted_at IS NULL AND t.visible
LEFT OUTER JOIN topics AS t2 ON t2.id = ua.target_topic_id AND t2.archetype = 'regular' AND t2.deleted_at IS NULL AND t2.visible
LEFT OUTER JOIN categories AS c ON t.category_id = c.id
WHERE u.active
AND u.silenced_till IS NULL
AND u.id > 0
GROUP BY u.id)
UPDATE directory_items di SET
likes_received = x.likes_received,
likes_given = x.likes_given,
topics_entered = x.topics_entered,
days_visited = x.days_visited,
posts_read = x.posts_read,
topic_count = x.topic_count,
post_count = x.post_count
FROM x
WHERE
x.user_id = di.user_id AND
di.period_type = :period_type AND (
di.likes_received <> x.likes_received OR
di.likes_given <> x.likes_given OR
di.topics_entered <> x.topics_entered OR
di.days_visited <> x.days_visited OR
di.posts_read <> x.posts_read OR
di.topic_count <> x.topic_count OR
di.post_count <> x.post_count )
",
query_args,
)
@@plugin_queries.each { |plugin_query| DB.exec(plugin_query, query_args) }
DB.exec <<~SQL if period_type == :all
UPDATE user_stats s
SET likes_given = d.likes_given,
likes_received = d.likes_received,
topic_count = d.topic_count,
post_count = d.post_count
FROM directory_items d
WHERE s.user_id = d.user_id AND
d.period_type = 1 AND
( s.likes_given <> d.likes_given OR
s.likes_received <> d.likes_received OR
s.topic_count <> d.topic_count OR
s.post_count <> d.post_count
)
SQL
end
end
|