Skip to main content

max / makenotwork

5.4 KB · 171 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) AS "thread_count!"
46 FROM communities co
47 WHERE co.suspended_at IS NULL
48 AND co.state <> 'archived'
49 ORDER BY co.name
50 LIMIT $1 OFFSET $2"#,
51 limit,
52 offset,
53 )
54 .fetch_all(pool)
55 .await
56 }
57
58 /// Count non-suspended, non-archived communities.
59 #[tracing::instrument(skip_all)]
60 pub async fn count_communities(pool: &PgPool) -> Result<i64, sqlx::Error> {
61 sqlx::query_scalar!(
62 r#"SELECT COUNT(*) AS "count!" FROM communities WHERE suspended_at IS NULL AND state <> 'archived'"#,
63 )
64 .fetch_one(pool)
65 .await
66 }
67
68 /// List archived communities. Used by the explicit `?filter=archived` view; never
69 /// merged with the default listing.
70 #[tracing::instrument(skip_all)]
71 pub async fn list_archived_communities(
72 pool: &PgPool,
73 limit: i64,
74 offset: i64,
75 ) -> Result<Vec<CommunityListRow>, sqlx::Error> {
76 sqlx::query_as!(
77 CommunityListRow,
78 // Scalar-subquery counts (see `list_communities`), bounded to the page.
79 r#"SELECT co.name, co.slug, co.description,
80 (SELECT COUNT(*) FROM categories c WHERE c.community_id = co.id) AS "category_count!",
81 (SELECT COUNT(*) FROM threads t
82 JOIN categories c2 ON c2.id = t.category_id
83 WHERE c2.community_id = co.id) AS "thread_count!"
84 FROM communities co
85 WHERE co.suspended_at IS NULL
86 AND co.state = 'archived'
87 ORDER BY co.name
88 LIMIT $1 OFFSET $2"#,
89 limit,
90 offset,
91 )
92 .fetch_all(pool)
93 .await
94 }
95
96 #[tracing::instrument(skip_all)]
97 pub async fn count_archived_communities(pool: &PgPool) -> Result<i64, sqlx::Error> {
98 sqlx::query_scalar!(
99 r#"SELECT COUNT(*) AS "count!" FROM communities WHERE suspended_at IS NULL AND state = 'archived'"#,
100 )
101 .fetch_one(pool)
102 .await
103 }
104
105 #[tracing::instrument(skip_all)]
106 pub async fn get_community_by_slug(
107 pool: &PgPool,
108 slug: &str,
109 ) -> Result<Option<CommunityRow>, sqlx::Error> {
110 sqlx::query_as!(
111 CommunityRow,
112 r#"SELECT id, name, slug, description,
113 suspended_at AS "suspended_at: chrono::DateTime<chrono::Utc>",
114 auto_hide_threshold,
115 state AS "state: CommunityState"
116 FROM communities WHERE slug = $1"#,
117 slug,
118 )
119 .fetch_optional(pool)
120 .await
121 }
122
123 #[tracing::instrument(skip_all)]
124 pub async fn get_community_by_id(
125 pool: &PgPool,
126 id: Uuid,
127 ) -> Result<Option<CommunityRow>, sqlx::Error> {
128 sqlx::query_as!(
129 CommunityRow,
130 r#"SELECT id, name, slug, description,
131 suspended_at AS "suspended_at: chrono::DateTime<chrono::Utc>",
132 auto_hide_threshold,
133 state AS "state: CommunityState"
134 FROM communities WHERE id = $1"#,
135 id,
136 )
137 .fetch_optional(pool)
138 .await
139 }
140
141 /// Count all threads in a community (across every category, including
142 /// soft-deleted, matches the admin dashboard's historical total).
143 #[tracing::instrument(skip_all)]
144 pub async fn count_threads_in_community(
145 pool: &PgPool,
146 community_id: Uuid,
147 ) -> Result<i64, sqlx::Error> {
148 sqlx::query_scalar!(
149 r#"SELECT COUNT(*) AS "count!" FROM threads t
150 JOIN categories c ON c.id = t.category_id
151 WHERE c.community_id = $1"#,
152 community_id,
153 )
154 .fetch_one(pool)
155 .await
156 }
157
158 /// The stored suspension reason for a community, if any.
159 #[tracing::instrument(skip_all)]
160 pub async fn get_community_suspension_reason(
161 pool: &PgPool,
162 community_id: Uuid,
163 ) -> Result<Option<String>, sqlx::Error> {
164 sqlx::query_scalar!(
165 "SELECT suspension_reason FROM communities WHERE id = $1",
166 community_id,
167 )
168 .fetch_one(pool)
169 .await
170 }
171