systemprompt_content/repository/search/
mod.rs

1use crate::error::ContentError;
2use crate::models::SearchResult;
3use sqlx::PgPool;
4use std::sync::Arc;
5use systemprompt_database::DbPool;
6use systemprompt_identifiers::CategoryId;
7
8#[derive(Debug)]
9pub struct SearchRepository {
10    pool: Arc<PgPool>,
11}
12
13impl SearchRepository {
14    pub fn new(db: &DbPool) -> Result<Self, ContentError> {
15        let pool = db
16            .pool_arc()
17            .map_err(|e| ContentError::InvalidRequest(format!("Database pool error: {e}")))?;
18        Ok(Self { pool })
19    }
20
21    pub async fn search_by_category(
22        &self,
23        category_id: &CategoryId,
24        limit: i64,
25    ) -> Result<Vec<SearchResult>, sqlx::Error> {
26        sqlx::query_as!(
27            SearchResult,
28            r#"
29            SELECT c.id as "id: _", c.slug as "slug!", c.title as "title!",
30                   c.description as "description!", c.image,
31                   c.source_id as "source_id: _", c.category_id as "category_id: _",
32                   COALESCE(m.total_views, 0) as "view_count!"
33            FROM markdown_content c
34            LEFT JOIN content_performance_metrics m ON c.id = m.content_id
35            WHERE c.category_id = $1
36            ORDER BY m.total_views DESC NULLS LAST
37            LIMIT $2
38            "#,
39            category_id.as_str(),
40            limit
41        )
42        .fetch_all(&*self.pool)
43        .await
44    }
45
46    pub async fn search_by_keyword(
47        &self,
48        keyword: &str,
49        limit: i64,
50    ) -> Result<Vec<SearchResult>, sqlx::Error> {
51        let pattern = format!("%{}%", keyword);
52        sqlx::query_as!(
53            SearchResult,
54            r#"
55            SELECT c.id as "id: _", c.slug as "slug!", c.title as "title!",
56                   c.description as "description!", c.image,
57                   c.source_id as "source_id: _", c.category_id as "category_id: _",
58                   COALESCE(m.total_views, 0) as "view_count!"
59            FROM markdown_content c
60            LEFT JOIN content_performance_metrics m ON c.id = m.content_id
61            WHERE (c.title ILIKE $1 OR c.description ILIKE $1 OR c.body ILIKE $1)
62            ORDER BY m.total_views DESC NULLS LAST
63            LIMIT $2
64            "#,
65            pattern,
66            limit
67        )
68        .fetch_all(&*self.pool)
69        .await
70    }
71}
systemprompt_content/repository/search/mod.rs

systemprompt_content/repository/search/
mod.rs
