yggdrasil/api/assets/
list.rs

1//! 素材分页列表接口。
2//!
3//! 按引用状态（全部/引用中/孤儿）筛选、按文件名/alt 搜索、按时间或大小排序。
4//! 同时返回各筛选维度计数与可清理孤儿统计，供 tabs 与「清理孤儿」按钮展示。
5//! Leptos server function，注册在 `/api` 路径下，仅 admin 可用。
6
7use crate::api::error::ApiError;
8use leptos::prelude::*;
9
10use super::types::AssetListResponse;
11use crate::models::asset::{AssetFilter, AssetSort};
12
13/// 每页素材数（网格 6 列 x 10 行）。
14#[cfg(feature = "server")]
15const PER_PAGE: i64 = 60;
16
17/// 孤儿清理保护窗：上传不满 7 天的无引用素材不可一键清理
18/// （保护尚未首次保存的草稿引用）。
19#[cfg(feature = "server")]
20pub(crate) const PURGE_GRACE_DAYS: i32 = 7;
21
22/// 获取素材分页列表。
23#[server(ListAssets, endpoint = "list_assets", input = leptos::server_fn::codec::Json)]
24pub async fn list_assets(
25    filter: AssetFilter,
26    query: String,
27    sort: AssetSort,
28    page: i32,
29) -> Result<AssetListResponse, ApiError> {
30    crate::api::auth::get_current_admin_user().await?;
31    list_assets_impl(filter, query, sort, page).await
32}
33
34/// 共享素材操作；调用方须先完成管理员鉴权。
35#[cfg(feature = "server")]
36pub(crate) async fn list_assets_impl(
37    filter: AssetFilter,
38    query: String,
39    sort: AssetSort,
40    page: i32,
41) -> Result<AssetListResponse, ApiError> {
42    use crate::api::error::AppError;
43    use crate::db::pool::get_conn;
44    use crate::models::asset::{Asset, AssetDto, AssetRef, AssetRefPostStatus};
45
46    let client = get_conn().await.map_err(AppError::db_conn)?;
47
48    let page = page.max(1);
49    let offset: i64 = (page as i64 - 1) * PER_PAGE;
50    let query = query.trim().to_string();
51
52    // 筛选/搜索条件统一拼进 WHERE；参数按出现顺序编号。
53    // 引用状态用 EXISTS 子查询，搜索用 ILIKE 转义通配符。
54    let mut conditions: Vec<String> = Vec::new();
55    let mut params: Vec<&(dyn tokio_postgres::types::ToSql + Sync)> = Vec::new();
56    match filter {
57        AssetFilter::Used => {
58            conditions.push(super::ASSET_REF_CLAUSE.to_string());
59        }
60        AssetFilter::Orphan => {
61            conditions.push(format!("NOT {}", super::ASSET_REF_CLAUSE));
62        }
63        AssetFilter::All => {}
64    }
65    if !query.is_empty() {
66        params.push(&query);
67        conditions.push(format!(
68            "(a.filename ILIKE '%' || ${} || '%' OR a.alt ILIKE '%' || ${} || '%')",
69            params.len(),
70            params.len()
71        ));
72    }
73    let where_clause = if conditions.is_empty() {
74        String::new()
75    } else {
76        format!("WHERE {}", conditions.join(" AND "))
77    };
78
79    let order_clause = match sort {
80        AssetSort::CreatedDesc => "a.created_at DESC, a.id",
81        AssetSort::SizeDesc => "a.size_bytes DESC, a.id",
82    };
83
84    // 列表查询：ref_count 由下方本页引用明细在 Rust 侧派生，不在每行重复跑子查询。
85    // const 不能取引用（内联后借临时值会垂悬），绑定到局部变量再进参数列表。
86    let per_page = PER_PAGE;
87    params.push(&per_page);
88    let limit_idx = params.len();
89    params.push(&offset);
90    let offset_idx = params.len();
91    let rows = client
92        .query(
93            &format!(
94                "SELECT a.id AS id, a.path, a.filename, a.mime, a.size_bytes, \
95                        a.width, a.height, a.alt, a.created_at \
96                 FROM assets a {where_clause} \
97                 ORDER BY {order_clause} LIMIT ${limit_idx} OFFSET ${offset_idx}"
98            ),
99            &params,
100        )
101        .await
102        .map_err(AppError::query)?;
103
104    let total: i64 = client
105        .query_one(
106            &format!("SELECT COUNT(*) FROM assets a {where_clause}"),
107            &params[..params.len() - 2],
108        )
109        .await
110        .map_err(AppError::query)?
111        .get(0);
112
113    // 汇总计数：tabs 与「清理孤儿」按钮徽标。不受筛选/搜索影响，始终全局。
114    // 孤儿 = 不存在任何文章、评论或头像引用（与 AssetFilter::Orphan 同语义）。
115    // MATERIALIZED 阻止规划器把 used 展开回各 FILTER，确保每个素材只判定一次。
116    let summary = client
117        .query_one(
118            &format!(
119                "WITH s AS MATERIALIZED ( \
120                    SELECT a.size_bytes, \
121                           a.created_at < NOW() - make_interval(days => $1) AS purge_due, \
122                           {asset_ref} AS used \
123                    FROM assets a) \
124                 SELECT \
125                    COUNT(*) FILTER (WHERE used), \
126                    COUNT(*) FILTER (WHERE NOT used), \
127                    COUNT(*) FILTER (WHERE NOT used AND purge_due), \
128                    COALESCE(SUM(size_bytes) FILTER (WHERE NOT used AND purge_due), 0)::bigint \
129                 FROM s",
130                asset_ref = super::ASSET_REF_CLAUSE
131            ),
132            &[&PURGE_GRACE_DAYS],
133        )
134        .await
135        .map_err(AppError::query)?;
136
137    // 本页素材的引用明细（第二组查询，避免 JOIN fan-out 与分页错位）。
138    // 四个来源与 ASSET_REF_CLAUSE 一一对应；按 文章→评论→用户头像→友链头像
139    // 的顺序插入，前端即按此分组顺序渲染。
140    let ids: Vec<uuid::Uuid> = rows.iter().map(|r| r.get::<_, uuid::Uuid>("id")).collect();
141    let mut refs_map: std::collections::HashMap<String, Vec<AssetRef>> =
142        std::collections::HashMap::new();
143    if !ids.is_empty() {
144        let note_rows = client.query(
145            "SELECT DISTINCT r.asset_id, n.id, CASE WHEN v.title='' THEN left(v.summary,48) ELSE v.title END AS title FROM note_asset_refs r JOIN notes n ON n.id=r.note_id JOIN note_revisions v ON v.note_id=n.id AND v.version=n.version WHERE r.asset_id=ANY($1) ORDER BY n.id",
146            &[&ids],
147        ).await.map_err(AppError::query)?;
148        for row in note_rows {
149            refs_map
150                .entry(row.get::<_, uuid::Uuid>("asset_id").to_string())
151                .or_default()
152                .push(AssetRef::Note {
153                    note_id: row.get("id"),
154                    title: row.get("title"),
155                });
156        }
157        // 1. 文章（asset_refs 表，含草稿与回收站——slug/status/deleted_at 供前端
158        //    决定链接走向：已发布→前台新标签，其余→后台编辑页）。
159        let ref_rows = client
160            .query(
161                "SELECT r.asset_id AS asset_id, p.id AS post_id, p.title, p.slug, \
162                        p.status, p.deleted_at \
163                 FROM asset_refs r JOIN posts p ON p.id = r.post_id \
164                 WHERE r.asset_id = ANY($1) \
165                 ORDER BY p.id",
166                &[&ids],
167            )
168            .await
169            .map_err(AppError::query)?;
170        for rr in ref_rows {
171            let asset_key: String = rr.get::<_, uuid::Uuid>("asset_id").to_string();
172            let status = AssetRefPostStatus::resolve(
173                rr.get::<_, &str>("status"),
174                rr.get::<_, Option<chrono::DateTime<chrono::Utc>>>("deleted_at"),
175            );
176            refs_map.entry(asset_key).or_default().push(AssetRef::Post {
177                post_id: rr.get("post_id"),
178                title: rr.get("title"),
179                slug: rr.get("slug"),
180                status,
181            });
182        }
183
184        // 2. 评论（comment_asset_refs，含回收站评论；所属文章信息供链接与展示）。
185        let comment_rows = client
186            .query(
187                "SELECT r.asset_id AS asset_id, c.id AS comment_id, c.author_name, \
188                        p.id AS post_id, p.title AS post_title, p.slug AS post_slug, \
189                        p.status AS post_status, p.deleted_at AS post_deleted_at \
190                 FROM comment_asset_refs r \
191                 JOIN comments c ON c.id = r.comment_id \
192                 JOIN posts p ON p.id = c.post_id \
193                 WHERE r.asset_id = ANY($1) \
194                 ORDER BY c.id",
195                &[&ids],
196            )
197            .await
198            .map_err(AppError::query)?;
199        for rr in comment_rows {
200            let asset_key: String = rr.get::<_, uuid::Uuid>("asset_id").to_string();
201            let post_status = AssetRefPostStatus::resolve(
202                rr.get::<_, &str>("post_status"),
203                rr.get::<_, Option<chrono::DateTime<chrono::Utc>>>("post_deleted_at"),
204            );
205            refs_map
206                .entry(asset_key)
207                .or_default()
208                .push(AssetRef::Comment {
209                    comment_id: rr.get("comment_id"),
210                    author_name: rr.get("author_name"),
211                    post_id: rr.get("post_id"),
212                    post_title: rr.get("post_title"),
213                    post_slug: rr.get("post_slug"),
214                    post_status,
215                });
216        }
217
218        // 3. 用户头像（label = display_name 回退 username）。
219        let user_rows = client
220            .query(
221                "SELECT r.asset_id AS asset_id, u.id AS user_id, u.username, u.display_name \
222                 FROM user_avatar_asset_refs r \
223                 JOIN users u ON u.id = r.user_id \
224                 WHERE r.asset_id = ANY($1) \
225                 ORDER BY u.id",
226                &[&ids],
227            )
228            .await
229            .map_err(AppError::query)?;
230        for rr in user_rows {
231            let asset_key: String = rr.get::<_, uuid::Uuid>("asset_id").to_string();
232            let username: String = rr.get("username");
233            let display_name: Option<String> = rr.get("display_name");
234            let label = display_name
235                .filter(|s| !s.trim().is_empty())
236                .unwrap_or(username);
237            refs_map
238                .entry(asset_key)
239                .or_default()
240                .push(AssetRef::UserAvatar {
241                    user_id: rr.get("user_id"),
242                    label,
243                });
244        }
245
246        // 4. 友链头像。
247        let friend_rows = client
248            .query(
249                "SELECT r.asset_id AS asset_id, f.id AS friend_id, f.name \
250                 FROM friend_avatar_asset_refs r \
251                 JOIN friend_links f ON f.id = r.friend_id \
252                 WHERE r.asset_id = ANY($1) \
253                 ORDER BY f.id",
254                &[&ids],
255            )
256            .await
257            .map_err(AppError::query)?;
258        for rr in friend_rows {
259            let asset_key: String = rr.get::<_, uuid::Uuid>("asset_id").to_string();
260            refs_map
261                .entry(asset_key)
262                .or_default()
263                .push(AssetRef::FriendAvatar {
264                    friend_id: rr.get("friend_id"),
265                    name: rr.get("name"),
266                });
267        }
268    }
269
270    let assets = rows
271        .into_iter()
272        .map(|row| {
273            let id: String = row.get::<_, uuid::Uuid>("id").to_string();
274            let refs = refs_map.remove(&id).unwrap_or_default();
275            let ref_count = refs.len() as i64;
276            AssetDto {
277                asset: Asset {
278                    id,
279                    path: row.get("path"),
280                    filename: row.get("filename"),
281                    mime: row.get("mime"),
282                    size_bytes: row.get("size_bytes"),
283                    width: row.get("width"),
284                    height: row.get("height"),
285                    alt: row.get("alt"),
286                    created_at: row.get("created_at"),
287                },
288                ref_count,
289                refs,
290            }
291        })
292        .collect();
293
294    Ok(AssetListResponse {
295        assets,
296        total,
297        used_count: summary.get(0),
298        orphan_count: summary.get(1),
299        purgeable_count: summary.get(2),
300        purgeable_bytes: summary.get(3),
301    })
302}
yggdrasil/api/assets/list.rs

yggdrasil/api/assets/
list.rs
