Skip to main content

max / makenotwork

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