shithub Public

Watch 1 Fork 0 Star 0
MySQL · 7852 bytes Raw Blame History
1 -- ─── activity feed / trending ─────────────────────────────────────
2
3 -- name: ListDashboardFeedEvents :many
4 SELECT
5 de.id, de.actor_user_id, de.kind, de.repo_id, de.source_kind,
6 de.source_id, de.public, de.payload, de.created_at,
7 actor.username AS actor_username,
8 actor.display_name AS actor_display_name,
9 COALESCE(r.name::text, '')::text AS repo_name,
10 COALESCE(r.description, '') AS repo_description,
11 COALESCE(r.primary_language, '') AS repo_primary_language,
12 COALESCE(r.star_count, 0)::bigint AS repo_star_count,
13 COALESCE(r.fork_count, 0)::bigint AS repo_fork_count,
14 COALESCE(owner_user.username::text, owner_org.slug::text, '')::text AS repo_owner,
15 COALESCE(source_user.username::text, source_org.slug::text, '')::text AS source_name
16 FROM domain_events de
17 JOIN users actor ON actor.id = de.actor_user_id
18 LEFT JOIN repos r ON r.id = de.repo_id AND r.deleted_at IS NULL
19 LEFT JOIN users owner_user ON owner_user.id = r.owner_user_id
20 LEFT JOIN orgs owner_org ON owner_org.id = r.owner_org_id
21 LEFT JOIN users source_user ON de.source_kind = 'user' AND source_user.id = de.source_id
22 LEFT JOIN orgs source_org ON de.source_kind = 'org' AND source_org.id = de.source_id
23 WHERE de.public = true
24 AND de.kind <> 'unstar'
25 AND actor.suspended_at IS NULL
26 AND actor.deleted_at IS NULL
27 AND (
28 de.repo_id IS NULL
29 OR (r.id IS NOT NULL AND r.visibility = 'public')
30 )
31 AND (
32 de.actor_user_id = sqlc.arg(viewer_user_id)::bigint
33 OR de.actor_user_id IN (
34 SELECT followee_user_id FROM follows
35 WHERE follower_user_id = sqlc.arg(viewer_user_id)::bigint
36 AND followee_user_id IS NOT NULL
37 )
38 OR de.repo_id IN (
39 SELECT repo_id FROM watches
40 WHERE user_id = sqlc.arg(viewer_user_id)::bigint
41 AND level <> 'ignore'
42 )
43 OR (
44 r.owner_org_id IN (
45 SELECT followee_org_id FROM follows
46 WHERE follower_user_id = sqlc.arg(viewer_user_id)::bigint
47 AND followee_org_id IS NOT NULL
48 )
49 )
50 OR (
51 de.source_kind = 'org'
52 AND de.source_id IN (
53 SELECT followee_org_id FROM follows
54 WHERE follower_user_id = sqlc.arg(viewer_user_id)::bigint
55 AND followee_org_id IS NOT NULL
56 )
57 )
58 )
59 AND (
60 sqlc.narg(before_created_at)::timestamptz IS NULL
61 OR (de.created_at, de.id) < (
62 sqlc.narg(before_created_at)::timestamptz,
63 sqlc.narg(before_id)::bigint
64 )
65 )
66 ORDER BY de.created_at DESC, de.id DESC
67 LIMIT sqlc.arg(limit_count)::int;
68
69 -- name: ListPublicFeedEvents :many
70 SELECT
71 de.id, de.actor_user_id, de.kind, de.repo_id, de.source_kind,
72 de.source_id, de.public, de.payload, de.created_at,
73 actor.username AS actor_username,
74 actor.display_name AS actor_display_name,
75 COALESCE(r.name::text, '')::text AS repo_name,
76 COALESCE(r.description, '') AS repo_description,
77 COALESCE(r.primary_language, '') AS repo_primary_language,
78 COALESCE(r.star_count, 0)::bigint AS repo_star_count,
79 COALESCE(r.fork_count, 0)::bigint AS repo_fork_count,
80 COALESCE(owner_user.username::text, owner_org.slug::text, '')::text AS repo_owner,
81 COALESCE(source_user.username::text, source_org.slug::text, '')::text AS source_name
82 FROM domain_events de
83 JOIN users actor ON actor.id = de.actor_user_id
84 LEFT JOIN repos r ON r.id = de.repo_id AND r.deleted_at IS NULL
85 LEFT JOIN users owner_user ON owner_user.id = r.owner_user_id
86 LEFT JOIN orgs owner_org ON owner_org.id = r.owner_org_id
87 LEFT JOIN users source_user ON de.source_kind = 'user' AND source_user.id = de.source_id
88 LEFT JOIN orgs source_org ON de.source_kind = 'org' AND source_org.id = de.source_id
89 WHERE de.public = true
90 AND de.kind <> 'unstar'
91 AND actor.suspended_at IS NULL
92 AND actor.deleted_at IS NULL
93 AND (
94 de.repo_id IS NULL
95 OR (r.id IS NOT NULL AND r.visibility = 'public')
96 )
97 AND (
98 sqlc.narg(before_created_at)::timestamptz IS NULL
99 OR (de.created_at, de.id) < (
100 sqlc.narg(before_created_at)::timestamptz,
101 sqlc.narg(before_id)::bigint
102 )
103 )
104 ORDER BY de.created_at DESC, de.id DESC
105 LIMIT sqlc.arg(limit_count)::int;
106
107 -- name: ListTrendingRepos :many
108 WITH recent AS (
109 SELECT
110 repo_id,
111 (
112 COUNT(*) FILTER (WHERE kind = 'star') * 3
113 + COUNT(*) FILTER (WHERE kind = 'forked') * 2
114 + COUNT(DISTINCT actor_user_id) FILTER (WHERE kind = 'push')
115 )::bigint AS score
116 FROM domain_events
117 WHERE public = true
118 AND repo_id IS NOT NULL
119 AND created_at >= now() - make_interval(days => sqlc.arg(window_days)::int)
120 GROUP BY repo_id
121 )
122 SELECT
123 r.id AS repo_id,
124 COALESCE(owner_user.username::text, owner_org.slug::text, '')::text AS owner,
125 r.name::text AS name,
126 r.description,
127 COALESCE(r.primary_language, '') AS primary_language,
128 r.star_count,
129 r.fork_count,
130 COALESCE(recent.score, 0)::bigint AS score,
131 r.updated_at
132 FROM repos r
133 LEFT JOIN recent ON recent.repo_id = r.id
134 LEFT JOIN users owner_user ON owner_user.id = r.owner_user_id
135 LEFT JOIN orgs owner_org ON owner_org.id = r.owner_org_id
136 WHERE r.visibility = 'public'
137 AND r.deleted_at IS NULL
138 AND r.is_archived = false
139 ORDER BY COALESCE(recent.score, 0) DESC, r.star_count DESC, r.updated_at DESC
140 LIMIT sqlc.arg(limit_count)::int;
141
142 -- name: ListTrendingUsers :many
143 WITH recent_events AS (
144 SELECT actor_user_id AS user_id, COUNT(*)::bigint AS event_count
145 FROM domain_events
146 WHERE public = true
147 AND actor_user_id IS NOT NULL
148 AND created_at >= now() - make_interval(days => sqlc.arg(window_days)::int)
149 GROUP BY actor_user_id
150 ),
151 recent_followers AS (
152 SELECT followee_user_id AS user_id, COUNT(*)::bigint AS follower_count
153 FROM follows
154 WHERE followee_user_id IS NOT NULL
155 AND followed_at >= now() - make_interval(days => sqlc.arg(window_days)::int)
156 GROUP BY followee_user_id
157 )
158 SELECT
159 u.id AS user_id,
160 u.username,
161 u.display_name,
162 (COALESCE(recent_followers.follower_count, 0) * 2 + COALESCE(recent_events.event_count, 0))::bigint AS score,
163 COALESCE(recent_followers.follower_count, 0)::bigint AS follower_delta,
164 COALESCE(recent_events.event_count, 0)::bigint AS event_count
165 FROM users u
166 LEFT JOIN recent_events ON recent_events.user_id = u.id
167 LEFT JOIN recent_followers ON recent_followers.user_id = u.id
168 WHERE u.suspended_at IS NULL
169 AND u.deleted_at IS NULL
170 AND (COALESCE(recent_followers.follower_count, 0) > 0 OR COALESCE(recent_events.event_count, 0) > 0)
171 ORDER BY score DESC, u.created_at DESC
172 LIMIT sqlc.arg(limit_count)::int;
173
174 -- name: ListDashboardReposForUser :many
175 SELECT
176 r.id AS repo_id,
177 COALESCE(owner_user.username::text, owner_org.slug::text, '')::text AS owner,
178 r.name::text AS name,
179 r.description,
180 r.visibility,
181 COALESCE(r.primary_language, '') AS primary_language,
182 r.star_count,
183 r.fork_count,
184 r.updated_at
185 FROM repos r
186 LEFT JOIN users owner_user ON owner_user.id = r.owner_user_id
187 LEFT JOIN orgs owner_org ON owner_org.id = r.owner_org_id
188 WHERE (
189 r.owner_user_id = sqlc.arg(viewer_user_id)::bigint
190 OR r.owner_org_id IN (
191 SELECT org_id FROM org_members
192 WHERE user_id = sqlc.arg(viewer_user_id)::bigint
193 )
194 )
195 AND r.deleted_at IS NULL
196 ORDER BY r.updated_at DESC
197 LIMIT sqlc.arg(limit_count)::int;
198
199 -- name: InsertTrendingSnapshot :one
200 INSERT INTO trending_snapshots (scope, kind, payload)
201 VALUES ($1, $2, $3)
202 RETURNING id, scope, kind, captured_at, payload;
203
204 -- name: LatestTrendingSnapshot :one
205 SELECT id, scope, kind, captured_at, payload
206 FROM trending_snapshots
207 WHERE scope = $1
208 AND kind = $2
209 ORDER BY captured_at DESC
210 LIMIT 1;
211