max / makenotwork

5.5 KB · 173 lines History Blame Raw

1	use super::{CommunityState, DateTime, PgPool, Utc, Uuid};
2
3	#[derive(sqlx::FromRow)]
4	pub struct CommunityRow {
5	pub id: Uuid,
6	pub name: String,
7	pub slug: String,
8	pub description: Option<String>,
9	pub suspended_at: Option<DateTime<Utc>>,
10	pub auto_hide_threshold: Option<i32>,
11	pub state: CommunityState,
12	}
13
14	#[derive(sqlx::FromRow)]
15	pub struct CommunityListRow {
16	pub name: String,
17	pub slug: String,
18	pub description: Option<String>,
19	pub category_count: i64,
20	pub thread_count: i64,
21	}
22
23	/// List non-suspended, non-archived communities with category and thread counts (paginated).
24	///
25	/// Archived communities are hidden from the default listing, see
26	/// [`list_archived_communities`] for the explicit archived view.
27	#[tracing::instrument(skip_all)]
28	pub async fn list_communities(
29	pool: &PgPool,
30	limit: i64,
31	offset: i64,
32	) -> Result<Vec<CommunityListRow>, sqlx::Error> {
33	sqlx::query_as!(
34	CommunityListRow,
35	// Per-community counts as scalar subqueries rather than a
36	// categories⋈threads join + GROUP BY COUNT(DISTINCT): the join
37	// multiplied rows (community × categories × threads) and forced
38	// aggregating every community's threads before LIMIT. As scalar
39	// subqueries in the target list they're evaluated only for the page's
40	// output rows (after ORDER BY + LIMIT), bounding the work to one page.
41	r#"SELECT co.name, co.slug, co.description,
42	(SELECT COUNT(*) FROM categories c WHERE c.community_id = co.id) AS "category_count!",
43	(SELECT COUNT(*) FROM threads t
44	JOIN categories c2 ON c2.id = t.category_id
45	WHERE c2.community_id = co.id
46	AND t.deleted_at IS NULL) AS "thread_count!"
47	FROM communities co
48	WHERE co.suspended_at IS NULL
49	AND co.state <> 'archived'
50	ORDER BY co.name
51	LIMIT $1 OFFSET $2"#,
52	limit,
53	offset,
54	)
55	.fetch_all(pool)
56	.await
57	}
58
59	/// Count non-suspended, non-archived communities.
60	#[tracing::instrument(skip_all)]
61	pub async fn count_communities(pool: &PgPool) -> Result<i64, sqlx::Error> {
62	sqlx::query_scalar!(
63	r#"SELECT COUNT(*) AS "count!" FROM communities WHERE suspended_at IS NULL AND state <> 'archived'"#,
64	)
65	.fetch_one(pool)
66	.await
67	}
68
69	/// List archived communities. Used by the explicit `?filter=archived` view; never
70	/// merged with the default listing.
71	#[tracing::instrument(skip_all)]
72	pub async fn list_archived_communities(
73	pool: &PgPool,
74	limit: i64,
75	offset: i64,
76	) -> Result<Vec<CommunityListRow>, sqlx::Error> {
77	sqlx::query_as!(
78	CommunityListRow,
79	// Scalar-subquery counts (see `list_communities`), bounded to the page.
80	r#"SELECT co.name, co.slug, co.description,
81	(SELECT COUNT(*) FROM categories c WHERE c.community_id = co.id) AS "category_count!",
82	(SELECT COUNT(*) FROM threads t
83	JOIN categories c2 ON c2.id = t.category_id
84	WHERE c2.community_id = co.id
85	AND t.deleted_at IS NULL) AS "thread_count!"
86	FROM communities co
87	WHERE co.suspended_at IS NULL
88	AND co.state = 'archived'
89	ORDER BY co.name
90	LIMIT $1 OFFSET $2"#,
91	limit,
92	offset,
93	)
94	.fetch_all(pool)
95	.await
96	}
97
98	#[tracing::instrument(skip_all)]
99	pub async fn count_archived_communities(pool: &PgPool) -> Result<i64, sqlx::Error> {
100	sqlx::query_scalar!(
101	r#"SELECT COUNT(*) AS "count!" FROM communities WHERE suspended_at IS NULL AND state = 'archived'"#,
102	)
103	.fetch_one(pool)
104	.await
105	}
106
107	#[tracing::instrument(skip_all)]
108	pub async fn get_community_by_slug(
109	pool: &PgPool,
110	slug: &str,
111	) -> Result<Option<CommunityRow>, sqlx::Error> {
112	sqlx::query_as!(
113	CommunityRow,
114	r#"SELECT id, name, slug, description,
115	suspended_at AS "suspended_at: chrono::DateTime<chrono::Utc>",
116	auto_hide_threshold,
117	state AS "state: CommunityState"
118	FROM communities WHERE slug = $1"#,
119	slug,
120	)
121	.fetch_optional(pool)
122	.await
123	}
124
125	#[tracing::instrument(skip_all)]
126	pub async fn get_community_by_id(
127	pool: &PgPool,
128	id: Uuid,
129	) -> Result<Option<CommunityRow>, sqlx::Error> {
130	sqlx::query_as!(
131	CommunityRow,
132	r#"SELECT id, name, slug, description,
133	suspended_at AS "suspended_at: chrono::DateTime<chrono::Utc>",
134	auto_hide_threshold,
135	state AS "state: CommunityState"
136	FROM communities WHERE id = $1"#,
137	id,
138	)
139	.fetch_optional(pool)
140	.await
141	}
142
143	/// Count all threads in a community (across every category, including
144	/// soft-deleted, matches the admin dashboard's historical total).
145	#[tracing::instrument(skip_all)]
146	pub async fn count_threads_in_community(
147	pool: &PgPool,
148	community_id: Uuid,
149	) -> Result<i64, sqlx::Error> {
150	sqlx::query_scalar!(
151	r#"SELECT COUNT(*) AS "count!" FROM threads t
152	JOIN categories c ON c.id = t.category_id
153	WHERE c.community_id = $1"#,
154	community_id,
155	)
156	.fetch_one(pool)
157	.await
158	}
159
160	/// The stored suspension reason for a community, if any.
161	#[tracing::instrument(skip_all)]
162	pub async fn get_community_suspension_reason(
163	pool: &PgPool,
164	community_id: Uuid,
165	) -> Result<Option<String>, sqlx::Error> {
166	sqlx::query_scalar!(
167	"SELECT suspension_reason FROM communities WHERE id = $1",
168	community_id,
169	)
170	.fetch_one(pool)
171	.await
172	}
173