Skip to main content

max / makenotwork

5.1 KB · 127 lines History Blame Raw
1 use super::{DateTime, PgPool, Utc, Uuid};
2
3 #[derive(sqlx::FromRow)]
4 pub struct SearchResultRow {
5 pub thread_id: Uuid,
6 pub thread_title: String,
7 pub author_username: String,
8 pub community_name: String,
9 pub community_slug: String,
10 pub category_name: String,
11 pub category_slug: String,
12 pub snippet: String,
13 pub last_activity_at: DateTime<Utc>,
14 pub rank: f64,
15 }
16
17 /// Full-text search across threads and posts. Combines tsvector ranking with
18 /// trigram similarity for typo tolerance. Title matches ranked above body.
19 /// Optionally scoped to a single community by slug.
20 #[tracing::instrument(skip_all)]
21 pub async fn search_threads(
22 pool: &PgPool,
23 query: &str,
24 community_slug: Option<&str>,
25 limit: i64,
26 ) -> Result<Vec<SearchResultRow>, sqlx::Error> {
27 // The trigram fuzzy branch uses the `%` operator (not `similarity(...) > k`)
28 // so `idx_threads_title_trgm` (gin_trgm_ops) can serve it and the planner can
29 // BitmapOr it with the tsvector GIN scan instead of seq-scanning every live
30 // thread. `%` compares against `pg_trgm.similarity_threshold`, so we pin that
31 // to 0.1 (the old inline constant) with `SET LOCAL` inside a transaction:
32 // session-scoped, so it can't leak to other pooled connections. `similarity()`
33 // still appears in the rank expression, but only computed for matched rows.
34 let mut tx = pool.begin().await?;
35 sqlx::query("SET LOCAL pg_trgm.similarity_threshold = 0.1")
36 .execute(&mut *tx)
37 .await?;
38 let rows = sqlx::query_as!(
39 SearchResultRow,
40 // `q` parses the tsquery once (it was parsed 5× inline); cross-joining
41 // the single-row CTE feeds it to every rank/match without re-parsing.
42 // post_matches uses NOT EXISTS rather than NOT IN (better anti-join,
43 // and NULL-safe).
44 r#"WITH q AS (
45 SELECT websearch_to_tsquery('english', $1) AS tsq
46 ),
47 thread_matches AS (
48 SELECT t.id AS thread_id,
49 t.title AS thread_title,
50 COALESCE(u.display_name, u.username) AS author_username,
51 co.name AS community_name,
52 co.slug AS community_slug,
53 c.name AS category_name,
54 c.slug AS category_slug,
55 LEFT(t.title, 200) AS snippet,
56 t.last_activity_at,
57 (ts_rank(t.search_tsv, q.tsq) * 2.0
58 + similarity(t.title, $2)) AS rank
59 FROM threads t
60 JOIN categories c ON c.id = t.category_id
61 JOIN communities co ON co.id = c.community_id
62 JOIN users u ON u.mnw_account_id = t.author_id
63 CROSS JOIN q
64 WHERE t.deleted_at IS NULL
65 AND co.suspended_at IS NULL
66 AND (t.search_tsv @@ q.tsq
67 OR t.title % $2)
68 AND ($3::text IS NULL OR co.slug = $3)
69 ),
70 post_matches AS (
71 SELECT DISTINCT ON (t.id)
72 t.id AS thread_id,
73 t.title AS thread_title,
74 COALESCE(pu.display_name, pu.username) AS author_username,
75 co.name AS community_name,
76 co.slug AS community_slug,
77 c.name AS category_name,
78 c.slug AS category_slug,
79 LEFT(p.body_markdown, 200) AS snippet,
80 t.last_activity_at,
81 ts_rank(p.search_tsv, q.tsq) AS rank
82 FROM posts p
83 JOIN threads t ON t.id = p.thread_id
84 JOIN categories c ON c.id = t.category_id
85 JOIN communities co ON co.id = c.community_id
86 -- Author of the matched *reply* (`p.author_id`), not the thread OP
87 -- (`t.author_id`): the snippet is the reply's body, so it must be
88 -- attributed to whoever wrote it.
89 JOIN users pu ON pu.mnw_account_id = p.author_id
90 CROSS JOIN q
91 WHERE t.deleted_at IS NULL
92 AND co.suspended_at IS NULL
93 AND p.removed_at IS NULL
94 AND p.search_tsv @@ q.tsq
95 AND ($3::text IS NULL OR co.slug = $3)
96 AND NOT EXISTS (SELECT 1 FROM thread_matches tm WHERE tm.thread_id = t.id)
97 ORDER BY t.id, ts_rank(p.search_tsv, q.tsq) DESC
98 )
99 SELECT
100 thread_id AS "thread_id!",
101 thread_title AS "thread_title!",
102 author_username AS "author_username!",
103 community_name AS "community_name!",
104 community_slug AS "community_slug!",
105 category_name AS "category_name!",
106 category_slug AS "category_slug!",
107 snippet AS "snippet!",
108 last_activity_at AS "last_activity_at!: chrono::DateTime<chrono::Utc>",
109 rank AS "rank!"
110 FROM (
111 SELECT * FROM thread_matches
112 UNION ALL
113 SELECT * FROM post_matches
114 ) results
115 ORDER BY rank DESC, last_activity_at DESC
116 LIMIT $4"#,
117 query,
118 query,
119 community_slug,
120 limit,
121 )
122 .fetch_all(&mut *tx)
123 .await?;
124 tx.commit().await?;
125 Ok(rows)
126 }
127