tdm_server_rust/repository/
member_episode_assemble.rs

1//! 组员话数列表组装（SeaORM 多表批量加载，对齐 episode_mapper 联查结果）
2
3use crate::entity::member::MemberEpisodeVo;
4use crate::sea_entity::{mangaepisodedetail, mangaepisodetb, mangatb, membertb, oss};
5use chrono::{DateTime, Utc};
6use sea_orm::{
7    ColumnTrait, ConnectionTrait, DatabaseConnection, DbBackend, EntityTrait, QueryFilter,
8    Statement, Value,
9};
10use std::collections::HashMap;
11
12/// mangaepisodetb 全列 SELECT 片段（供 UNION 子查询复用）
13const EPISODE_UNION_SELECT: &str = r#""Id", "mangaId", "mangaEpisode", "mangaEpisodeName",
14    "providerId", "translatorId", "proofreaderId", "lettererId", "timerId", "reviewerId",
15    "setupTime", "updateTime", "translatorFile", "proofreaderFile", "timerFile", "publishLink",
16    provider_file_oss_id, translator_file_oss_id, proofreader_file_oss_id,
17    letterer_file_oss_id, timer_file_oss_id"#;
18
19/// 六岗位 UNION 子查询（每支路走单列索引，UNION 去重）
20fn member_episode_union_sql() -> String {
21    let roles = [
22        r#""providerId""#,
23        r#""translatorId""#,
24        r#""proofreaderId""#,
25        r#""lettererId""#,
26        r#""timerId""#,
27        r#""reviewerId""#,
28    ];
29    roles
30        .iter()
31        .map(|col| format!("SELECT {EPISODE_UNION_SELECT} FROM mangaepisodetb WHERE {col} = $1"))
32        .collect::<Vec<_>>()
33        .join(" UNION ")
34}
35
36/// 查询组员相关话数总数（UNION 子查询 COUNT）
37pub async fn count_member_episodes(
38    db: &DatabaseConnection,
39    member_id: i32,
40) -> crate::error::ApiResult<i64> {
41    let union_sql = member_episode_union_sql();
42    let sql = format!("SELECT COUNT(*)::bigint AS cnt FROM ({union_sql}) t");
43    let row = db
44        .query_one(Statement::from_sql_and_values(
45            DbBackend::Postgres,
46            &sql,
47            [Value::from(member_id)],
48        ))
49        .await?
50        .ok_or_else(|| crate::error::AppError::business("统计组员话数失败喵"))?;
51    Ok(row.try_get("", "cnt")?)
52}
53
54/// 分页查询组员话数 VO 列表（UNION + ORDER BY + LIMIT）
55pub async fn page_member_episode_vos(
56    db: &DatabaseConnection,
57    member_id: i32,
58    page: i32,
59    page_size: i32,
60) -> crate::error::ApiResult<Vec<MemberEpisodeVo>> {
61    let page = page.max(1);
62    let page_size = page_size.max(1);
63    let offset = ((page - 1) * page_size) as i64;
64    let union_sql = member_episode_union_sql();
65    let sql =
66        format!("SELECT * FROM ({union_sql}) episodes ORDER BY \"Id\" DESC LIMIT $2 OFFSET $3");
67    let rows = db
68        .query_all(Statement::from_sql_and_values(
69            DbBackend::Postgres,
70            &sql,
71            [
72                Value::from(member_id),
73                Value::from(page_size),
74                Value::from(offset),
75            ],
76        ))
77        .await?;
78    let episodes: Vec<mangaepisodetb::Model> = rows
79        .into_iter()
80        .map(row_to_episode_model)
81        .collect::<crate::error::ApiResult<_>>()?;
82    assemble_member_episode_vos(db, episodes).await
83}
84
85/// 全量查询组员话数 VO 列表
86pub async fn list_member_episode_vos(
87    db: &DatabaseConnection,
88    member_id: i32,
89) -> crate::error::ApiResult<Vec<MemberEpisodeVo>> {
90    let union_sql = member_episode_union_sql();
91    let sql = format!("SELECT * FROM ({union_sql}) episodes ORDER BY \"Id\" DESC");
92    let rows = db
93        .query_all(Statement::from_sql_and_values(
94            DbBackend::Postgres,
95            &sql,
96            [Value::from(member_id)],
97        ))
98        .await?;
99    let episodes: Vec<mangaepisodetb::Model> = rows
100        .into_iter()
101        .map(row_to_episode_model)
102        .collect::<crate::error::ApiResult<_>>()?;
103    assemble_member_episode_vos(db, episodes).await
104}
105
106/// 按话数 ID 批量加载 mangaepisodetb 行
107pub async fn load_episode_models_by_ids(
108    db: &DatabaseConnection,
109    ids: &[i32],
110) -> crate::error::ApiResult<Vec<mangaepisodetb::Model>> {
111    if ids.is_empty() {
112        return Ok(vec![]);
113    }
114    use sea_orm::{ColumnTrait, EntityTrait, QueryFilter};
115    Ok(mangaepisodetb::Entity::find()
116        .filter(mangaepisodetb::Column::Id.is_in(ids.to_vec()))
117        .all(db)
118        .await?)
119}
120
121/// 将 UNION 查询行映射为 mangaepisodetb::Model
122fn row_to_episode_model(
123    row: sea_orm::QueryResult,
124) -> crate::error::ApiResult<mangaepisodetb::Model> {
125    Ok(mangaepisodetb::Model {
126        id: row.try_get("", "Id")?,
127        manga_id: row.try_get("", "mangaId")?,
128        manga_episode: row.try_get("", "mangaEpisode")?,
129        episode_type: row
130            .try_get("", "episodeType")
131            .unwrap_or_else(|_| "MAIN".to_string()),
132        manga_episode_name: row.try_get("", "mangaEpisodeName").ok(),
133        provider_id: row.try_get("", "providerId").ok(),
134        translator_id: row.try_get("", "translatorId").ok(),
135        proofreader_id: row.try_get("", "proofreaderId").ok(),
136        letterer_id: row.try_get("", "lettererId").ok(),
137        timer_id: row.try_get("", "timerId").ok(),
138        reviewer_id: row.try_get("", "reviewerId").ok(),
139        setup_time: row.try_get("", "setupTime")?,
140        update_time: row.try_get("", "updateTime")?,
141        translator_file: row.try_get("", "translatorFile").ok(),
142        proofreader_file: row.try_get("", "proofreaderFile").ok(),
143        timer_file: row.try_get("", "timerFile").ok(),
144        publish_link: row.try_get("", "publishLink").ok(),
145        provider_file_oss_id: row.try_get("", "provider_file_oss_id").ok(),
146        translator_file_oss_id: row.try_get("", "translator_file_oss_id").ok(),
147        proofreader_file_oss_id: row.try_get("", "proofreader_file_oss_id").ok(),
148        letterer_file_oss_id: row.try_get("", "letterer_file_oss_id").ok(),
149        timer_file_oss_id: row.try_get("", "timer_file_oss_id").ok(),
150    })
151}
152
153/// 将话数行批量组装为 VO
154pub async fn assemble_member_episode_vos(
155    db: &DatabaseConnection,
156    episodes: Vec<mangaepisodetb::Model>,
157) -> crate::error::ApiResult<Vec<MemberEpisodeVo>> {
158    if episodes.is_empty() {
159        return Ok(vec![]);
160    }
161    let ep_ids: Vec<i32> = episodes.iter().map(|e| e.id).collect();
162    let manga_ids: Vec<i32> = episodes.iter().map(|e| e.manga_id).collect();
163
164    let mut member_ids = Vec::new();
165    let mut oss_ids = Vec::new();
166    for ep in &episodes {
167        for mid in [
168            ep.provider_id,
169            ep.translator_id,
170            ep.proofreader_id,
171            ep.letterer_id,
172            ep.timer_id,
173            ep.reviewer_id,
174        ] {
175            if let Some(id) = mid {
176                member_ids.push(id);
177            }
178        }
179        for oid in [
180            ep.provider_file_oss_id,
181            ep.translator_file_oss_id,
182            ep.proofreader_file_oss_id,
183            ep.letterer_file_oss_id,
184            ep.timer_file_oss_id,
185        ] {
186            if let Some(id) = oid {
187                oss_ids.push(id);
188            }
189        }
190    }
191    member_ids.sort_unstable();
192    member_ids.dedup();
193    oss_ids.sort_unstable();
194    oss_ids.dedup();
195
196    let ep_ids_for_details = ep_ids.clone();
197    let manga_ids_for_query = manga_ids.clone();
198    let member_ids_for_query = member_ids.clone();
199    let oss_ids_for_query = oss_ids.clone();
200
201    let (details, mangas, members, oss_names) = tokio::try_join!(
202        async {
203            Ok::<_, crate::error::AppError>(
204                mangaepisodedetail::Entity::find()
205                    .filter(mangaepisodedetail::Column::EpisodeId.is_in(ep_ids_for_details))
206                    .all(db)
207                    .await?
208                    .into_iter()
209                    .map(|d| (d.episode_id, d))
210                    .collect::<HashMap<_, _>>(),
211            )
212        },
213        async {
214            Ok(mangatb::Entity::find()
215                .filter(mangatb::Column::Id.is_in(manga_ids_for_query))
216                .all(db)
217                .await?
218                .into_iter()
219                .map(|m| (m.id, m))
220                .collect::<HashMap<_, _>>())
221        },
222        async {
223            if member_ids_for_query.is_empty() {
224                Ok(HashMap::new())
225            } else {
226                Ok(membertb::Entity::find()
227                    .filter(membertb::Column::Id.is_in(member_ids_for_query))
228                    .all(db)
229                    .await?
230                    .into_iter()
231                    .map(|m| (m.id, m.username))
232                    .collect())
233            }
234        },
235        async {
236            if oss_ids_for_query.is_empty() {
237                Ok(HashMap::new())
238            } else {
239                Ok(oss::Entity::find()
240                    .filter(oss::Column::Id.is_in(oss_ids_for_query))
241                    .all(db)
242                    .await?
243                    .into_iter()
244                    .map(|o| (o.id, o.filename))
245                    .collect())
246            }
247        },
248    )?;
249
250    let mut out = Vec::with_capacity(episodes.len());
251    for ep in episodes {
252        let med = details.get(&ep.id);
253        let manga = mangas.get(&ep.manga_id);
254        let vo = MemberEpisodeVo {
255            id: ep.id,
256            manga_id: ep.manga_id,
257            manga_name: manga.map(|m| m.manga_tran_name.clone()),
258            manga_episode: Some(ep.manga_episode.clone()),
259            manga_episode_name: ep.manga_episode_name.clone(),
260            setup_time: dt_legacy(ep.setup_time),
261            update_time: dt_legacy(ep.update_time),
262            provider_id: ep.provider_id,
263            provider_name: ep.provider_id.and_then(|id| members.get(&id).cloned()),
264            provider_setup_time: None,
265            provider_update_time: None,
266            translator_id: ep.translator_id,
267            translator_name: ep.translator_id.and_then(|id| members.get(&id).cloned()),
268            translator_setup_time: med.and_then(|d| dt_legacy_opt(d.translator_setup_time)),
269            translator_update_time: med.and_then(|d| dt_legacy_opt(d.translator_update_time)),
270            proofreader_id: ep.proofreader_id,
271            proofreader_name: ep.proofreader_id.and_then(|id| members.get(&id).cloned()),
272            proofreader_setup_time: med.and_then(|d| dt_legacy_opt(d.proofreader_setup_time)),
273            proofreader_update_time: med.and_then(|d| dt_legacy_opt(d.proofreader_update_time)),
274            letterer_id: ep.letterer_id,
275            letterer_name: ep.letterer_id.and_then(|id| members.get(&id).cloned()),
276            letterer_setup_time: med.and_then(|d| dt_legacy_opt(d.letterer_setup_time)),
277            letterer_update_time: med.and_then(|d| dt_legacy_opt(d.letterer_update_time)),
278            timer_id: ep.timer_id,
279            timer_name: ep.timer_id.and_then(|id| members.get(&id).cloned()),
280            timer_setup_time: med.and_then(|d| dt_legacy_opt(d.timer_setup_time)),
281            timer_update_time: med.and_then(|d| dt_legacy_opt(d.timer_update_time)),
282            reviewer_id: ep.reviewer_id,
283            reviewer_name: ep.reviewer_id.and_then(|id| members.get(&id).cloned()),
284            reviewer_setup_time: med.and_then(|d| dt_legacy_opt(d.reviewer_setup_time)),
285            reviewer_update_time: med.and_then(|d| dt_legacy_opt(d.reviewer_update_time)),
286            provider_file_oss_id: ep.provider_file_oss_id,
287            provider_filename: ep
288                .provider_file_oss_id
289                .and_then(|id| oss_names.get(&id).cloned()),
290            translator_file_oss_id: ep.translator_file_oss_id,
291            translator_filename: ep
292                .translator_file_oss_id
293                .and_then(|id| oss_names.get(&id).cloned()),
294            proofreader_file_oss_id: ep.proofreader_file_oss_id,
295            proofreader_filename: ep
296                .proofreader_file_oss_id
297                .and_then(|id| oss_names.get(&id).cloned()),
298            letterer_file_oss_id: ep.letterer_file_oss_id,
299            letterer_filename: ep
300                .letterer_file_oss_id
301                .and_then(|id| oss_names.get(&id).cloned()),
302            timer_file_oss_id: ep.timer_file_oss_id,
303            timer_filename: ep
304                .timer_file_oss_id
305                .and_then(|id| oss_names.get(&id).cloned()),
306            translator_file: ep.translator_file.clone(),
307            proofreader_file: ep.proofreader_file.clone(),
308            timer_file: ep.timer_file.clone(),
309            publish_link: ep.publish_link.clone(),
310            workbench_time: None,
311        };
312        out.push(vo);
313    }
314    Ok(out)
315}
316
317/// naive 时间转 API UTC（legacy 校正）
318fn dt_legacy(n: chrono::NaiveDateTime) -> Option<DateTime<Utc>> {
319    Some(crate::utils::legacy_time::naive_db_to_api_utc(n))
320}
321
322/// 可空 naive 时间转 API UTC
323fn dt_legacy_opt(n: Option<chrono::NaiveDateTime>) -> Option<DateTime<Utc>> {
324    n.map(crate::utils::legacy_time::naive_db_to_api_utc)
325}
tdm_server_rust/repository/member_episode_assemble.rs

tdm_server_rust/repository/
member_episode_assemble.rs
