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
|
# File 'app/models/group_user.rb', line 29
def self.update_first_unread_pm(last_seen, limit: 10_000)
whisperers_group_ids = SiteSetting.whispers_allowed_group_ids
DB.exec(
<<~SQL,
UPDATE group_users gu
SET first_unread_pm_at = Y.min_date
FROM (
SELECT
X.group_id,
X.user_id,
X.min_date
FROM (
SELECT
gu.group_id,
gu.user_id,
COALESCE(Z.min_date, :now) min_date
FROM group_users gu
LEFT JOIN (
SELECT
gu2.group_id,
gu2.user_id,
MIN(t.updated_at) min_date
FROM group_users gu2
INNER JOIN topic_allowed_groups tag ON tag.group_id = gu2.group_id
INNER JOIN topics t ON t.id = tag.topic_id
INNER JOIN users u ON u.id = gu2.user_id
LEFT JOIN topic_users tu ON t.id = tu.topic_id AND tu.user_id = gu2.user_id
WHERE t.deleted_at IS NULL
AND t.archetype = :archetype
AND tu.last_read_post_number < CASE
WHEN u.admin OR u.moderator #{whisperers_group_ids.present? ? "OR gu2.group_id IN (:whisperers_group_ids)" : ""}
THEN t.highest_staff_post_number
ELSE t.highest_post_number
END
AND (COALESCE(tu.notification_level, 1) >= 2)
GROUP BY gu2.user_id, gu2.group_id
) AS Z ON Z.user_id = gu.user_id AND Z.group_id = gu.group_id
) AS X
WHERE X.user_id IN (
SELECT id
FROM users
WHERE last_seen_at IS NOT NULL
AND last_seen_at > :last_seen
ORDER BY last_seen_at DESC
LIMIT :limit
)
) Y
WHERE gu.user_id = Y.user_id AND gu.group_id = Y.group_id
SQL
archetype: Archetype.private_message,
last_seen: last_seen,
limit: limit,
now: 10.minutes.ago,
whisperers_group_ids: whisperers_group_ids,
)
end
|