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

yggdrasil/api/assets/
list.rs
