max / makenotwork

3.4 KB · 96 lines History Blame Raw

1	use super::{DateTime, PgPool, Utc, Uuid};
2
3	/// Check if a user is tracking a specific thread.
4	#[tracing::instrument(skip_all)]
5	pub async fn is_thread_tracked(
6	pool: &PgPool,
7	user_id: Uuid,
8	thread_id: Uuid,
9	) -> Result<bool, sqlx::Error> {
10	sqlx::query_scalar!(
11	r#"SELECT EXISTS(SELECT 1 FROM tracked_threads WHERE user_id = $1 AND thread_id = $2) AS "exists!""#,
12	user_id,
13	thread_id,
14	)
15	.fetch_one(pool)
16	.await
17	}
18
19	#[derive(sqlx::FromRow)]
20	pub struct TrackedThreadRow {
21	pub thread_id: Uuid,
22	pub thread_title: String,
23	pub community_name: String,
24	pub community_slug: String,
25	pub category_slug: String,
26	pub unread_count: i64,
27	pub has_mention: bool,
28	pub tracked_at: DateTime<Utc>,
29	}
30
31	/// List a user's tracked threads with unread post counts, newest activity first.
32	///
33	/// Paginated (`limit`/`offset`) so a user tracking hundreds of threads doesn't
34	/// render (and recompute) an unbounded page. The last-read cutoff is resolved
35	/// with a single LEFT JOIN rather than a per-row nested subquery, and the unread
36	/// count excludes mod-removed posts (`p.removed_at IS NULL`) so the badge can't
37	/// be inflated by removed content and can use the `idx_posts_not_removed` index.
38	#[tracing::instrument(skip_all)]
39	pub async fn list_tracked_threads(
40	pool: &PgPool,
41	user_id: Uuid,
42	limit: i64,
43	offset: i64,
44	) -> Result<Vec<TrackedThreadRow>, sqlx::Error> {
45	sqlx::query_as!(
46	TrackedThreadRow,
47	r#"SELECT tt.thread_id,
48	t.title AS thread_title,
49	co.name AS community_name,
50	co.slug AS community_slug,
51	cat.slug AS category_slug,
52	(SELECT COUNT(*) FROM posts p
53	WHERE p.thread_id = tt.thread_id
54	AND p.removed_at IS NULL
55	AND (lrp.created_at IS NULL OR p.created_at > lrp.created_at)
56	) AS "unread_count!",
57	EXISTS (
58	SELECT 1 FROM post_mentions pm
59	JOIN posts p ON p.id = pm.post_id
60	WHERE pm.mentioned_user_id = tt.user_id
61	AND p.thread_id = tt.thread_id
62	) AS "has_mention!",
63	tt.tracked_at AS "tracked_at: chrono::DateTime<chrono::Utc>"
64	FROM tracked_threads tt
65	JOIN threads t ON t.id = tt.thread_id
66	JOIN categories cat ON cat.id = t.category_id
67	JOIN communities co ON co.id = cat.community_id
68	LEFT JOIN posts lrp ON lrp.id = tt.last_read_post_id
69	WHERE tt.user_id = $1 AND t.deleted_at IS NULL AND co.suspended_at IS NULL
70	ORDER BY t.last_activity_at DESC
71	LIMIT $2 OFFSET $3"#,
72	user_id,
73	limit,
74	offset,
75	)
76	.fetch_all(pool)
77	.await
78	}
79
80	/// Count a user's visible tracked threads (for pagination). Mirrors the
81	/// visibility filters of `list_tracked_threads`.
82	#[tracing::instrument(skip_all)]
83	pub async fn count_tracked_threads(pool: &PgPool, user_id: Uuid) -> Result<i64, sqlx::Error> {
84	sqlx::query_scalar!(
85	r#"SELECT COUNT(*) AS "count!"
86	FROM tracked_threads tt
87	JOIN threads t ON t.id = tt.thread_id
88	JOIN categories cat ON cat.id = t.category_id
89	JOIN communities co ON co.id = cat.community_id
90	WHERE tt.user_id = $1 AND t.deleted_at IS NULL AND co.suspended_at IS NULL"#,
91	user_id,
92	)
93	.fetch_one(pool)
94	.await
95	}
96