tdm_server_rust/repository/
search_repo.rs

1//! 全局搜索仓储。
2//!
3//! 使用 PostgreSQL `pg_trgm` 的 `similarity()` 与 `ILIKE` 完成分区模糊搜索。
4
5use crate::entity::search::GlobalSearchHit;
6use sqlx::{postgres::PgPool, Row};
7
8/// 全局搜索分区。
9#[derive(Debug, Clone, Copy, PartialEq, Eq)]
10pub enum SearchKind {
11    /// 全部分区。
12    All,
13    /// 漫画。
14    Manga,
15    /// 话数。
16    Episode,
17    /// 组员。
18    Member,
19    /// 术语。
20    Glossary,
21    /// 评价。
22    Evaluation,
23    /// 问卷。
24    Questionnaire,
25    /// 奖励。
26    Reward,
27}
28
29impl SearchKind {
30    /// 从前端分区参数解析搜索分区。
31    pub fn parse(raw: Option<&str>) -> Self {
32        match raw.unwrap_or("all").trim().to_ascii_lowercase().as_str() {
33            "manga" => Self::Manga,
34            "episode" => Self::Episode,
35            "member" => Self::Member,
36            "glossary" => Self::Glossary,
37            "evaluation" => Self::Evaluation,
38            "questionnaire" => Self::Questionnaire,
39            "reward" => Self::Reward,
40            _ => Self::All,
41        }
42    }
43
44    /// 分区标识。
45    pub fn as_str(self) -> &'static str {
46        match self {
47            Self::All => "all",
48            Self::Manga => "manga",
49            Self::Episode => "episode",
50            Self::Member => "member",
51            Self::Glossary => "glossary",
52            Self::Evaluation => "evaluation",
53            Self::Questionnaire => "questionnaire",
54            Self::Reward => "reward",
55        }
56    }
57
58    /// 分区展示名称。
59    pub fn label(self) -> &'static str {
60        match self {
61            Self::All => "全部",
62            Self::Manga => "漫画",
63            Self::Episode => "话数",
64            Self::Member => "组员",
65            Self::Glossary => "术语",
66            Self::Evaluation => "评价",
67            Self::Questionnaire => "问卷",
68            Self::Reward => "奖励",
69        }
70    }
71}
72
73/// 搜索权限上下文。
74#[derive(Debug, Clone, Copy)]
75pub struct SearchPermission {
76    /// 当前登录组员 ID。
77    pub member_id: Option<i32>,
78    /// 是否具备管理员权限。
79    pub is_admin: bool,
80}
81
82/// 分区搜索结果。
83pub struct SearchRepoSectionResult {
84    /// 分区类型。
85    pub kind: SearchKind,
86    /// 分区命中数。
87    pub total: i64,
88    /// 当前页命中结果。
89    pub hits: Vec<GlobalSearchHit>,
90}
91
92/// 全局搜索仓储。
93pub struct SearchRepository {
94    /// 数据库连接池。
95    db: PgPool,
96}
97
98impl SearchRepository {
99    /// 从 `PgPool` 构造仓储。
100    pub fn new(db: PgPool) -> Self {
101        Self { db }
102    }
103
104    /// 搜索指定分区。
105    pub async fn search_section(
106        &self,
107        kind: SearchKind,
108        keyword: &str,
109        limit: i64,
110        offset: i64,
111        permission: SearchPermission,
112    ) -> crate::error::ApiResult<SearchRepoSectionResult> {
113        match kind {
114            SearchKind::Manga => self.search_manga(keyword, limit, offset).await,
115            SearchKind::Episode => self.search_episode(keyword, limit, offset).await,
116            SearchKind::Member => self.search_member(keyword, limit, offset).await,
117            SearchKind::Glossary => self.search_glossary(keyword, limit, offset).await,
118            SearchKind::Evaluation => {
119                self.search_evaluation(keyword, limit, offset, permission)
120                    .await
121            }
122            SearchKind::Questionnaire => {
123                self.search_questionnaire(keyword, limit, offset, permission)
124                    .await
125            }
126            SearchKind::Reward => self.search_reward(keyword, limit, offset).await,
127            SearchKind::All => Ok(SearchRepoSectionResult {
128                kind,
129                total: 0,
130                hits: Vec::new(),
131            }),
132        }
133    }
134
135    /// 搜索漫画。
136    async fn search_manga(
137        &self,
138        keyword: &str,
139        limit: i64,
140        offset: i64,
141    ) -> crate::error::ApiResult<SearchRepoSectionResult> {
142        let rows = query_rows(
143            &self.db,
144            SearchKind::Manga,
145            r#"
146            WITH author_names AS (
147                SELECT ma."mangaId" AS manga_id, string_agg(a."authorName", '、') AS names
148                FROM mangaauthor ma
149                JOIN authortb a ON a."Id" = ma."authorId"
150                GROUP BY ma."mangaId"
151            ),
152            magazine_names AS (
153                SELECT mm."mangaId" AS manga_id, string_agg(mag."magazineName", '、') AS names
154                FROM mangamagazine mm
155                JOIN magazine mag ON mag.id = mm."magazineId"
156                GROUP BY mm."mangaId"
157            ),
158            matched AS (
159                SELECT
160                    m."Id"::text AS id,
161                    m."mangaTranName" AS title,
162                    NULLIF(m."mangaOriName", '') AS subtitle,
163                    COALESCE(NULLIF(m.introduction, ''), an.names, mn.names, m."mangaOriName") AS highlight,
164                    '/manga/' || m."Id"::text AS route,
165                    m.image AS image_url,
166                    NULL::text AS avatar_url,
167                    GREATEST(
168                        similarity(m."mangaTranName", $2),
169                        similarity(m."mangaOriName", $2),
170                        similarity(COALESCE(m.introduction, ''), $2),
171                        similarity(COALESCE(an.names, ''), $2),
172                        similarity(COALESCE(mn.names, ''), $2)
173                    )::float8 AS score,
174                    to_char(m."updateTime", 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AS updated_at
175                FROM mangatb m
176                LEFT JOIN author_names an ON an.manga_id = m."Id"
177                LEFT JOIN magazine_names mn ON mn.manga_id = m."Id"
178                WHERE m."mangaTranName" ILIKE $1
179                   OR m."mangaOriName" ILIKE $1
180                   OR COALESCE(m.introduction, '') ILIKE $1
181                   OR COALESCE(an.names, '') ILIKE $1
182                   OR COALESCE(mn.names, '') ILIKE $1
183                   OR similarity(m."mangaTranName", $2) > 0.18
184                   OR similarity(m."mangaOriName", $2) > 0.18
185                   OR similarity(COALESCE(an.names, ''), $2) > 0.18
186            )
187            SELECT *, COUNT(*) OVER() AS section_total
188            FROM matched
189            ORDER BY score DESC, updated_at DESC NULLS LAST
190            LIMIT $3 OFFSET $4
191            "#,
192            keyword,
193            limit,
194            offset,
195        )
196        .await?;
197        Ok(rows)
198    }
199
200    /// 搜索话数。
201    async fn search_episode(
202        &self,
203        keyword: &str,
204        limit: i64,
205        offset: i64,
206    ) -> crate::error::ApiResult<SearchRepoSectionResult> {
207        query_rows(
208            &self.db,
209            SearchKind::Episode,
210            r#"
211            WITH matched AS (
212                SELECT
213                    e."Id"::text AS id,
214                    mt."mangaTranName" || ' 第' || e."mangaEpisode" || '话' AS title,
215                    e."mangaEpisodeName" AS subtitle,
216                    mt."mangaTranName" AS highlight,
217                    '/manga/' || e."mangaId"::text AS route,
218                    mt.image AS image_url,
219                    NULL::text AS avatar_url,
220                    GREATEST(
221                        similarity(e."mangaEpisode", $2),
222                        similarity(COALESCE(e."mangaEpisodeName", ''), $2),
223                        similarity(mt."mangaTranName", $2)
224                    )::float8 AS score,
225                    to_char(e."updateTime", 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AS updated_at
226                FROM mangaepisodetb e
227                JOIN mangatb mt ON mt."Id" = e."mangaId"
228                WHERE e."mangaEpisode" ILIKE $1
229                   OR COALESCE(e."mangaEpisodeName", '') ILIKE $1
230                   OR mt."mangaTranName" ILIKE $1
231                   OR similarity(COALESCE(e."mangaEpisodeName", ''), $2) > 0.18
232                   OR similarity(mt."mangaTranName", $2) > 0.18
233            )
234            SELECT *, COUNT(*) OVER() AS section_total
235            FROM matched
236            ORDER BY score DESC, updated_at DESC NULLS LAST
237            LIMIT $3 OFFSET $4
238            "#,
239            keyword,
240            limit,
241            offset,
242        )
243        .await
244    }
245
246    /// 搜索组员。
247    async fn search_member(
248        &self,
249        keyword: &str,
250        limit: i64,
251        offset: i64,
252    ) -> crate::error::ApiResult<SearchRepoSectionResult> {
253        query_rows(
254            &self.db,
255            SearchKind::Member,
256            r#"
257            WITH matched AS (
258                SELECT
259                    m."Id"::text AS id,
260                    m.username AS title,
261                    m.email AS subtitle,
262                    CASE WHEN m.email ILIKE $1 THEN m.email ELSE m.username END AS highlight,
263                    '/member/' || m."Id"::text AS route,
264                    NULL::text AS image_url,
265                    m."avatarUrl" AS avatar_url,
266                    GREATEST(
267                        similarity(m.username, $2),
268                        similarity(COALESCE(m.email, ''), $2)
269                    )::float8 AS score,
270                    to_char(m."lastSubmitTime", 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AS updated_at
271                FROM membertb m
272                WHERE m.username ILIKE $1
273                   OR COALESCE(m.email, '') ILIKE $1
274                   OR similarity(m.username, $2) > 0.18
275            )
276            SELECT *, COUNT(*) OVER() AS section_total
277            FROM matched
278            ORDER BY score DESC, updated_at DESC NULLS LAST
279            LIMIT $3 OFFSET $4
280            "#,
281            keyword,
282            limit,
283            offset,
284        )
285        .await
286    }
287
288    /// 搜索术语。
289    async fn search_glossary(
290        &self,
291        keyword: &str,
292        limit: i64,
293        offset: i64,
294    ) -> crate::error::ApiResult<SearchRepoSectionResult> {
295        query_rows(
296            &self.db,
297            SearchKind::Glossary,
298            r#"
299            WITH matched AS (
300                SELECT
301                    g."Id"::text AS id,
302                    COALESCE(g.title, '未命名术语') AS title,
303                    g.content AS subtitle,
304                    mt."mangaTranName" AS highlight,
305                    CASE WHEN g."mangaId" IS NULL THEN '/manga/manage' ELSE '/manga/' || g."mangaId"::text END AS route,
306                    g.image AS image_url,
307                    NULL::text AS avatar_url,
308                    GREATEST(
309                        similarity(COALESCE(g.title, ''), $2),
310                        similarity(COALESCE(g.content, ''), $2),
311                        similarity(COALESCE(mt."mangaTranName", ''), $2)
312                    )::float8 AS score,
313                    to_char(g."updateTime", 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AS updated_at
314                FROM glossary g
315                LEFT JOIN mangatb mt ON mt."Id" = g."mangaId"
316                WHERE COALESCE(g.title, '') ILIKE $1
317                   OR COALESCE(g.content, '') ILIKE $1
318                   OR COALESCE(mt."mangaTranName", '') ILIKE $1
319                   OR similarity(COALESCE(g.title, ''), $2) > 0.18
320                   OR similarity(COALESCE(g.content, ''), $2) > 0.18
321            )
322            SELECT *, COUNT(*) OVER() AS section_total
323            FROM matched
324            ORDER BY score DESC, updated_at DESC NULLS LAST
325            LIMIT $3 OFFSET $4
326            "#,
327            keyword,
328            limit,
329            offset,
330        )
331        .await
332    }
333
334    /// 搜索评价。
335    async fn search_evaluation(
336        &self,
337        keyword: &str,
338        limit: i64,
339        offset: i64,
340        permission: SearchPermission,
341    ) -> crate::error::ApiResult<SearchRepoSectionResult> {
342        if permission.member_id.is_none() {
343            return Ok(empty_section(SearchKind::Evaluation));
344        }
345        let member_id = permission.member_id.unwrap_or_default() as i64;
346        let role_filter = if permission.is_admin {
347            "AND ($5 = $5)"
348        } else {
349            "AND (ev.member_id = $5 OR ev.evaluator_id = $5)"
350        };
351        let sql = format!(
352            r#"
353            WITH matched AS (
354                SELECT
355                    ev.id::text AS id,
356                    ev.target AS title,
357                    '评价人：' || ev.evaluator AS subtitle,
358                    ev.content AS highlight,
359                    '/evaluation' AS route,
360                    NULL::text AS image_url,
361                    NULL::text AS avatar_url,
362                    GREATEST(
363                        similarity(ev.target, $2),
364                        similarity(ev.evaluator, $2),
365                        similarity(COALESCE(ev.content, ''), $2)
366                    )::float8 AS score,
367                    to_char(ev.updated_at, 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AS updated_at
368                FROM evaluation ev
369                WHERE (
370                    ev.target ILIKE $1
371                    OR ev.evaluator ILIKE $1
372                    OR COALESCE(ev.content, '') ILIKE $1
373                    OR similarity(COALESCE(ev.content, ''), $2) > 0.18
374                )
375                {role_filter}
376            )
377            SELECT *, COUNT(*) OVER() AS section_total
378            FROM matched
379            ORDER BY score DESC, updated_at DESC NULLS LAST
380            LIMIT $3 OFFSET $4
381            "#
382        );
383        query_rows_with_member(
384            &self.db,
385            SearchKind::Evaluation,
386            &sql,
387            keyword,
388            limit,
389            offset,
390            member_id,
391        )
392        .await
393    }
394
395    /// 搜索问卷。
396    async fn search_questionnaire(
397        &self,
398        keyword: &str,
399        limit: i64,
400        offset: i64,
401        permission: SearchPermission,
402    ) -> crate::error::ApiResult<SearchRepoSectionResult> {
403        if !permission.is_admin {
404            return Ok(empty_section(SearchKind::Questionnaire));
405        }
406        query_rows(
407            &self.db,
408            SearchKind::Questionnaire,
409            r#"
410            WITH matched AS (
411                SELECT
412                    q.id::text AS id,
413                    COALESCE(m.username, '问卷 #' || q.id::text) AS title,
414                    '接稿意愿问卷' AS subtitle,
415                    COALESCE(q.note, q.remark, q.preferred_genres, q.avoid_topics) AS highlight,
416                    '/questionnaire' AS route,
417                    NULL::text AS image_url,
418                    m."avatarUrl" AS avatar_url,
419                    GREATEST(
420                        similarity(COALESCE(m.username, ''), $2),
421                        similarity(COALESCE(q.preferred_genres, ''), $2),
422                        similarity(COALESCE(q.avoid_topics, ''), $2),
423                        similarity(COALESCE(q.note, ''), $2),
424                        similarity(COALESCE(q.remark, ''), $2)
425                    )::float8 AS score,
426                    to_char(q.updated_at, 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AS updated_at
427                FROM questionnaire q
428                LEFT JOIN membertb m ON m."Id" = q.member_id
429                WHERE COALESCE(m.username, '') ILIKE $1
430                   OR COALESCE(q.preferred_genres, '') ILIKE $1
431                   OR COALESCE(q.avoid_topics, '') ILIKE $1
432                   OR COALESCE(q.note, '') ILIKE $1
433                   OR COALESCE(q.remark, '') ILIKE $1
434                   OR similarity(COALESCE(q.note, ''), $2) > 0.18
435                   OR similarity(COALESCE(q.remark, ''), $2) > 0.18
436            )
437            SELECT *, COUNT(*) OVER() AS section_total
438            FROM matched
439            ORDER BY score DESC, updated_at DESC NULLS LAST
440            LIMIT $3 OFFSET $4
441            "#,
442            keyword,
443            limit,
444            offset,
445        )
446        .await
447    }
448
449    /// 搜索奖励。
450    async fn search_reward(
451        &self,
452        keyword: &str,
453        limit: i64,
454        offset: i64,
455    ) -> crate::error::ApiResult<SearchRepoSectionResult> {
456        query_rows(
457            &self.db,
458            SearchKind::Reward,
459            r#"
460            WITH matched AS (
461                SELECT
462                    r."rewardId"::text AS id,
463                    r.name AS title,
464                    r."fromEvent" AS subtitle,
465                    r.sponsor AS highlight,
466                    '/member/souvenir' AS route,
467                    r.image AS image_url,
468                    NULL::text AS avatar_url,
469                    GREATEST(
470                        similarity(r.name, $2),
471                        similarity(COALESCE(r."fromEvent", ''), $2),
472                        similarity(COALESCE(r.sponsor, ''), $2)
473                    )::float8 AS score,
474                    to_char(r."deadLine", 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AS updated_at
475                FROM rewardtb r
476                WHERE r.name ILIKE $1
477                   OR COALESCE(r."fromEvent", '') ILIKE $1
478                   OR COALESCE(r.sponsor, '') ILIKE $1
479                   OR similarity(r.name, $2) > 0.18
480                   OR similarity(COALESCE(r."fromEvent", ''), $2) > 0.18
481            )
482            SELECT *, COUNT(*) OVER() AS section_total
483            FROM matched
484            ORDER BY score DESC, updated_at DESC NULLS LAST
485            LIMIT $3 OFFSET $4
486            "#,
487            keyword,
488            limit,
489            offset,
490        )
491        .await
492    }
493}
494
495/// 查询标准化分区结果。
496async fn query_rows(
497    db: &PgPool,
498    kind: SearchKind,
499    sql: &str,
500    keyword: &str,
501    limit: i64,
502    offset: i64,
503) -> crate::error::ApiResult<SearchRepoSectionResult> {
504    let pattern = format!("%{}%", keyword);
505    let rows = sqlx::query(sql)
506        .bind(pattern)
507        .bind(keyword)
508        .bind(limit)
509        .bind(offset)
510        .fetch_all(db)
511        .await?;
512    rows_to_section(kind, rows)
513}
514
515/// 查询带当前组员 ID 的标准化分区结果。
516async fn query_rows_with_member(
517    db: &PgPool,
518    kind: SearchKind,
519    sql: &str,
520    keyword: &str,
521    limit: i64,
522    offset: i64,
523    member_id: i64,
524) -> crate::error::ApiResult<SearchRepoSectionResult> {
525    let pattern = format!("%{}%", keyword);
526    let rows = sqlx::query(sql)
527        .bind(pattern)
528        .bind(keyword)
529        .bind(limit)
530        .bind(offset)
531        .bind(member_id)
532        .fetch_all(db)
533        .await?;
534    rows_to_section(kind, rows)
535}
536
537/// 将数据库行转换为分区结果。
538fn rows_to_section(
539    kind: SearchKind,
540    rows: Vec<sqlx::postgres::PgRow>,
541) -> crate::error::ApiResult<SearchRepoSectionResult> {
542    let total = rows
543        .first()
544        .and_then(|row| row.try_get::<i64, _>("section_total").ok())
545        .unwrap_or(0);
546    let mut hits = Vec::with_capacity(rows.len());
547    for row in rows {
548        hits.push(GlobalSearchHit {
549            id: row.try_get::<String, _>("id")?,
550            kind: kind.as_str().to_string(),
551            kind_label: kind.label().to_string(),
552            title: row.try_get::<String, _>("title")?,
553            subtitle: row.try_get::<Option<String>, _>("subtitle").ok().flatten(),
554            highlight: row.try_get::<Option<String>, _>("highlight").ok().flatten(),
555            route: row.try_get::<String, _>("route")?,
556            image_url: row.try_get::<Option<String>, _>("image_url").ok().flatten(),
557            avatar_url: row
558                .try_get::<Option<String>, _>("avatar_url")
559                .ok()
560                .flatten(),
561            score: row.try_get::<f64, _>("score").unwrap_or(0.0),
562            updated_at: row
563                .try_get::<Option<String>, _>("updated_at")
564                .ok()
565                .flatten(),
566        });
567    }
568    Ok(SearchRepoSectionResult { kind, total, hits })
569}
570
571/// 空分区结果。
572fn empty_section(kind: SearchKind) -> SearchRepoSectionResult {
573    SearchRepoSectionResult {
574        kind,
575        total: 0,
576        hits: Vec::new(),
577    }
578}
tdm_server_rust/repository/search_repo.rs

tdm_server_rust/repository/
search_repo.rs
