max / makenotwork

3.6 KB · 102 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 both the
36	/// unread count and the mention flag count only live posts (`p.is_active`,
37	/// migration 034), so neither badge can be inflated by removed content and both
38	/// can use the `idx_posts_active` index.
39	///
40	/// The two carry the same filter deliberately. Both point the reader at content,
41	/// so a badge that outlives the post it names sends them to a thread with
42	/// nothing to find.
43	#[tracing::instrument(skip_all)]
44	pub async fn list_tracked_threads(
45	pool: &PgPool,
46	user_id: Uuid,
47	limit: i64,
48	offset: i64,
49	) -> Result<Vec<TrackedThreadRow>, sqlx::Error> {
50	sqlx::query_as!(
51	TrackedThreadRow,
52	r#"SELECT tt.thread_id,
53	t.title AS thread_title,
54	co.name AS community_name,
55	co.slug AS community_slug,
56	cat.slug AS category_slug,
57	(SELECT COUNT(*) FROM posts p
58	WHERE p.thread_id = tt.thread_id
59	AND p.is_active
60	AND (lrp.created_at IS NULL OR p.created_at > lrp.created_at)
61	) AS "unread_count!",
62	EXISTS (
63	SELECT 1 FROM post_mentions pm
64	JOIN posts p ON p.id = pm.post_id
65	WHERE pm.mentioned_user_id = tt.user_id
66	AND p.thread_id = tt.thread_id
67	AND p.is_active
68	) AS "has_mention!",
69	tt.tracked_at AS "tracked_at: chrono::DateTime<chrono::Utc>"
70	FROM tracked_threads tt
71	JOIN threads t ON t.id = tt.thread_id
72	JOIN categories cat ON cat.id = t.category_id
73	JOIN communities co ON co.id = cat.community_id
74	LEFT JOIN posts lrp ON lrp.id = tt.last_read_post_id
75	WHERE tt.user_id = $1 AND t.deleted_at IS NULL AND co.suspended_at IS NULL
76	ORDER BY t.last_activity_at DESC
77	LIMIT $2 OFFSET $3"#,
78	user_id,
79	limit,
80	offset,
81	)
82	.fetch_all(pool)
83	.await
84	}
85
86	/// Count a user's visible tracked threads (for pagination). Mirrors the
87	/// visibility filters of `list_tracked_threads`.
88	#[tracing::instrument(skip_all)]
89	pub async fn count_tracked_threads(pool: &PgPool, user_id: Uuid) -> Result<i64, sqlx::Error> {
90	sqlx::query_scalar!(
91	r#"SELECT COUNT(*) AS "count!"
92	FROM tracked_threads tt
93	JOIN threads t ON t.id = tt.thread_id
94	JOIN categories cat ON cat.id = t.category_id
95	JOIN communities co ON co.id = cat.community_id
96	WHERE tt.user_id = $1 AND t.deleted_at IS NULL AND co.suspended_at IS NULL"#,
97	user_id,
98	)
99	.fetch_one(pool)
100	.await
101	}
102