yggdrasil/api/assets/
list.rs

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

yggdrasil/api/assets/
list.rs
