Skip to main content

max / makenotwork

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