Repository navigation
Expand file tree
/
Copy pathNotificationGroupsQuery.php
More file actions
271 lines (241 loc) · 13 KB
/
Copy pathNotificationGroupsQuery.php
File metadata and controls
271 lines (241 loc) · 13 KB
1
2
3
4
5
6
7
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
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
<?php
declare(strict_types=1);
namespace App\Queries;
use App\Enums\NotificationType;
use App\Models\Notification;
use App\Models\Profile;
use App\Models\User;
use Carbon\CarbonImmutable;
use Carbon\CarbonInterface;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Collection;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\HasOne;
use Illuminate\Database\Query\Builder as QueryBuilder;
use Illuminate\Pagination\CursorPaginator;
use Illuminate\Support\Facades\DB;
final readonly class NotificationGroupsQuery
{
public const int PER_PAGE = 20;
private const int STACKED_ACTORS = 3;
/** @return CursorPaginator<int, Notification> */
public function paginate(User $user, string $type = 'all', ?CarbonImmutable $through = null): CursorPaginator
{
$order = 'PARTITION BY group_id ORDER BY CASE WHEN actor_position = 1 THEN 0 ELSE 1 END, created_at DESC, id DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING';
$events = $this->events($user, $type)->select('notifications.*')
->selectRaw("NTH_VALUE(actor_id, 2) OVER ({$order}) AS second_actor_id")
->selectRaw("NTH_VALUE(actor_id, 3) OVER ({$order}) AS third_actor_id");
$query = Notification::query()->fromSub($events, 'notifications')
->where('group_rank', 1)
->with([
'actor.user' => fn (HasOne $query): HasOne => $query
->withExists(['followers as is_followed' => fn (Builder $query): Builder => $query->whereKey($user->id)]),
'commit',
'timeout',
'organization',
])
->latest()->latest('id');
$perPage = $through instanceof CarbonImmutable
? max(self::PER_PAGE, $this->groupsSince($user, $through, $type)->where('group_rank', 1)->count())
: self::PER_PAGE;
$groups = $query->cursorPaginate($perPage)->appends(request()->except('through'));
$this->loadStackedActors($groups->items());
return $groups;
}
/**
* The inbox shows a quote with the commit it quotes, which is always the recipient's own or the organization's they
* heard about it for, so only those commits load there, as far as the recipient may see them: a reply that quotes
* someone else never brings along a commit the recipient may not see, nor does an organization's rejected one.
*
* @param CursorPaginator<int, Notification> $groups
* @return CursorPaginator<int, Notification>
*/
public function withQuotedCommits(User $user, CursorPaginator $groups): CursorPaginator
{
$items = new Collection($groups->items());
$authorIds = $items->pluck('organization_id')->filter()->push($user->id)->unique()->values()->all();
$items->loadMissing([
'commit.quotedCommit' => fn (BelongsTo $query): BelongsTo => $query->withTrashed()->whereIn('author_id', $authorIds)
->where(fn (CommitBuilder $query): CommitBuilder => $query->visibleTo($user)),
]);
return $groups;
}
/**
* A star is always on the recipient's own commit, or the organization's they heard about it for, so its images may
* show beside it: an image-only commit has nothing else to show.
*
* @param CursorPaginator<int, Notification> $groups
* @return CursorPaginator<int, Notification>
*/
public function withStarredCommitImages(CursorPaginator $groups): CursorPaginator
{
new Collection($groups->items())->where('type', NotificationType::Starred)->loadMissing('commit.images');
return $groups;
}
/**
* Whether two unread notifications share a group only depends on what happened between them, so grouping from the
* oldest unread one onward counts the same groups as grouping the whole history, which every page would pay for.
*/
public function unreadCount(User $user, string $type = 'all'): int
{
if ($type === 'none') {
return 0;
}
$oldestUnread = $this->visible($user)->unread()->oldest()->first();
return $oldestUnread instanceof Notification
? $this->groupsSince($user, $oldestUnread->created_at, $type)->where('group_rank', 1)->where('unread_count', '>', 0)->count()
: 0;
}
public function members(User $user, Notification $notification, string $type = 'all'): NotificationBuilder
{
return $this->group($this->visible($user), $type, $notification)
->with([
'actor.user' => fn (HasOne $query): HasOne => $query
->withExists(['followers as is_followed' => fn (Builder $query): Builder => $query->whereKey($user->id)]),
'organization',
'timeout',
]);
}
public function events(User $user, string $type = 'all'): NotificationBuilder
{
return $this->group($this->visible($user), $type);
}
/**
* Grouping only the notifications from some point onwards puts every one of them in the same group as grouping all
* of them does, apart from the oldest group of each topic missing what came before that point. So it finds just as
* many groups with notifications from then on.
*/
private function groupsSince(User $user, CarbonInterface $since, string $type = 'all'): NotificationBuilder
{
return $this->group($this->visible($user)->where('created_at', '>=', $since), $type);
}
private function visible(User $user): NotificationBuilder
{
return Notification::query()->where('recipient_id', $user->id)->relevant()->select('notifications.*');
}
private function group(NotificationBuilder $visible, string $type = 'all', ?Notification $member = null): NotificationBuilder
{
$filtered = Notification::query()->from('visible')->select('visible.*', 'identified.group_id')
->join('identified', 'identified.id', '=', 'visible.id')
->when($member, fn (NotificationBuilder $query, Notification $member): NotificationBuilder => $query->where(
'identified.group_id',
fn (QueryBuilder $query): QueryBuilder => $query->from('identified')->where('id', $member->id)->select('group_id'),
))
->when($type !== 'all', fn (NotificationBuilder $query): NotificationBuilder => $query->whereIn('type', array_merge(...array_map(fn (string $category): array => match ($category) {
'replies' => ['replied'],
'quotes' => ['quoted'],
'stars' => ['starred'],
'mentions' => ['mentioned'],
'follows' => ['followed'],
'moderation' => ['approved', 'rejected', 'timed_out'],
default => [],
}, explode(',', $type)))));
$gap = match (DB::connection($visible->getModel()->getConnectionName())->getDriverName()) {
'mysql', 'mariadb' => 'TIMESTAMPDIFF(SECOND, previous_match_at, created_at)',
'pgsql' => 'EXTRACT(EPOCH FROM (created_at - previous_match_at))',
default => 'unixepoch(created_at) - unixepoch(previous_match_at)',
};
// Deleted ancestors supply grouping identity only; content comes from relevant notifications. A commit's
// recorded root is always one of its ancestors, so the walk up the thread only continues past it when the
// recorded root is itself a reply. Follows never group across days, as a busy account gets followed so often
// that its follows would otherwise never stop joining a single group. What a member hears about for an
// organization only groups with what they hear about for that organization: each names whose it is.
$sql = <<<SQL
WITH RECURSIVE visible AS ({$visible->toSql()}),
threads AS (
SELECT visible.id AS notification_id, roots.id, roots.parent_id
FROM visible JOIN commits ON commits.id = visible.commit_id
JOIN commits AS roots ON roots.id = COALESCE(commits.root_id, commits.id)
WHERE visible.type = 'mentioned'
),
ancestry AS (
SELECT threads.notification_id, parent.id, parent.parent_id
FROM threads JOIN commits AS parent ON parent.id = threads.parent_id
UNION ALL
SELECT ancestry.notification_id, parent.id, parent.parent_id
FROM ancestry JOIN commits AS parent ON parent.id = ancestry.parent_id
),
topics AS (
SELECT visible.*,
CASE WHEN visible.type = 'starred' THEN 'starred'
WHEN visible.type = 'replied' THEN 'replied'
WHEN visible.type = 'timed_out' THEN 'timed_out'
WHEN visible.organization_id IS NOT NULL THEN CONCAT('organization:', visible.organization_id)
ELSE 'activity' END AS topic_kind,
CASE WHEN visible.type = 'followed' THEN visible.recipient_id
WHEN visible.type = 'starred' THEN visible.commit_id
WHEN visible.type = 'replied' THEN visible.id
ELSE COALESCE(ancestry.id, threads.id, visible.id) END AS topic_id,
CASE WHEN visible.type = 'followed' THEN DATE(visible.created_at) END AS topic_day
FROM visible LEFT JOIN threads ON threads.notification_id = visible.id
LEFT JOIN ancestry ON ancestry.notification_id = visible.id AND ancestry.parent_id IS NULL
),
predecessors AS (
SELECT id, topic_kind, topic_id, topic_day, created_at,
LAG(created_at) OVER (PARTITION BY topic_kind, topic_id, topic_day ORDER BY created_at, id) AS previous_match_at,
LAG(topic_kind) OVER (ORDER BY created_at, id) AS previous_topic_kind,
LAG(topic_id) OVER (ORDER BY created_at, id) AS previous_topic_id
FROM topics
),
sessions AS (
SELECT id, topic_kind, topic_id, topic_day, created_at,
SUM(CASE WHEN topic_kind = 'starred' THEN 0
WHEN previous_match_at IS NULL
OR ({$gap} > 3600 AND (previous_topic_kind <> topic_kind OR previous_topic_id <> topic_id))
THEN 1 ELSE 0 END)
OVER (PARTITION BY topic_kind, topic_id, topic_day ORDER BY created_at, id ROWS UNBOUNDED PRECEDING) AS session_number
FROM predecessors
),
identified AS (
SELECT id,
FIRST_VALUE(id) OVER (PARTITION BY topic_kind, topic_id, topic_day, session_number ORDER BY created_at, id) AS group_id
FROM sessions
),
filtered AS (
{$filtered->toSql()}
),
ranked_actors AS (
SELECT filtered.*,
ROW_NUMBER() OVER (PARTITION BY group_id, actor_id ORDER BY created_at DESC, id DESC) AS actor_position
FROM filtered
)
SELECT ranked_actors.*,
ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY created_at DESC, id DESC) AS group_rank,
COUNT(*) OVER (PARTITION BY group_id) AS event_count,
SUM(CASE WHEN actor_position = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY group_id) AS actors_count,
SUM(CASE WHEN read_at IS NULL THEN 1 ELSE 0 END) OVER (PARTITION BY group_id) AS unread_count
FROM ranked_actors
SQL;
return Notification::query()->fromSub($sql, 'notifications')->addBinding($visible->getBindings(), 'from')
->addBinding($filtered->getBindings(), 'from')
->withCasts(['event_count' => 'integer']);
}
/** @param array<int, Notification> $groups */
private function loadStackedActors(array $groups): void
{
$grouped = array_filter($groups, fn (Notification $group): bool => $group->actors_count > 1);
$actors = $grouped === [] ? new Collection() : Profile::query()->with(['user', 'organization'])
->findMany(array_merge(...array_map($this->stackedActorIds(...), $grouped)));
foreach ($groups as $group) {
$groupActors = $group->actors_count === 1 ? [$group->actor] : array_filter(array_map(
$actors->find(...),
$this->stackedActorIds($group),
));
$group->setRelation('actors', new Collection(array_values($groupActors)));
}
}
/**
* The window lists the newest notification of each actor first, so past the group's actors it repeats them.
*
* @return list<string>
*/
private function stackedActorIds(Notification $group): array
{
return array_slice(
array_values(array_filter([$group->actor_id, $group->second_actor_id, $group->third_actor_id])),
0,
min(self::STACKED_ACTORS, $group->actors_count),
);
}
}