sz_orm_query/
find_with_related.rs

1//! find_with_related 关联查询流畅 API
2//!
3//! 对应 SeaORM 的 `Entity::find().find_with_related(RelatedEntity).all(db).await` API。
4//! 在 SZ-ORM 中，由于 QueryBuilder 主要生成 SQL（不直接执行），本模块提供
5//! "生成关联查询 SQL" 的辅助 API：
6//!
7//! 1. `find_with_related_join`：以 JOIN 方式生成单条 SQL（适合 1:1 / N:1 关联）
8//! 2. `find_with_related_subquery`：以子查询方式生成 SQL（适合 1:N 关联，避免行膨胀）
9//! 3. `find_with_related_eager_sql`：生成 eager load 的两条 SQL（先主表，后关联表 WHERE IN）
10//!
11//! # 用法
12//!
13//! ```no_run
14//! use sz_orm_query::find_with_related::find_with_related_join;
15//! use sz_orm_model::{get_dialect, DbType, Relation, HasMany};
16//! use std::collections::HashMap;
17//!
18//! let mut relations = HashMap::new();
19//! relations.insert("orders", Relation::HasMany(HasMany {
20//!     foreign_key: "user_id".to_string(),
21//!     child_model: "orders".to_string(),
22//!     child_pk: "id".to_string(),
23//! }));
24//!
25//! let dialect = get_dialect(DbType::MySQL).unwrap();
26//! let sql = find_with_related_join(
27//!     &*dialect,          // 方言（Box<dyn Dialect> 解引用为 &dyn Dialect）
28//!     "users",            // 主表
29//!     "orders",           // 关联表
30//!     "user_id",          // 外键
31//!     "id",               // 主表主键
32//!     true,               // LEFT JOIN
33//! )
34//!     .unwrap()
35//!     .where_cond("users.id = 1")
36//!     .build();
37//! ```
38
39use std::collections::HashMap;
40use sz_orm_model::Dialect;
41use sz_orm_model::Relation;
42
43/// find_with_related 关联查询构造器（JOIN 模式）
44///
45/// 适合 1:1 / N:1 关联（BelongsTo / HasOne）。
46/// 对于 1:N 关联，JOIN 会导致主表行膨胀，应使用 `find_with_related_eager_sql`。
47pub struct FindWithRelated<'a> {
48    dialect: &'a dyn Dialect,
49    main_table: String,
50    related_table: String,
51    foreign_key: String,
52    primary_key: String,
53    left_join: bool,
54    where_conds: Vec<String>,
55    order_by: Vec<(String, bool)>, // (field, is_desc)
56    limit: Option<usize>,
57    offset: Option<usize>,
58}
59
60impl<'a> FindWithRelated<'a> {
61    /// 创建关联查询构造器
62    ///
63    /// - `dialect`：方言引用
64    /// - `main_table`：主表名
65    /// - `related_table`：关联表名
66    /// - `foreign_key`：外键列名（在 related_table 中，指向 main_table.primary_key）
67    /// - `primary_key`：主表主键列名
68    /// - `left_join`：true = LEFT JOIN，false = INNER JOIN
69    pub fn new(
70        dialect: &'a dyn Dialect,
71        main_table: impl Into<String>,
72        related_table: impl Into<String>,
73        foreign_key: impl Into<String>,
74        primary_key: impl Into<String>,
75        left_join: bool,
76    ) -> Result<Self, sz_orm_model::DbError> {
77        let main_table = main_table.into();
78        let related_table = related_table.into();
79        let foreign_key = foreign_key.into();
80        let primary_key = primary_key.into();
81        // H-2 修复：构造时校验所有标识符
82        validate_find_identifiers(&[&main_table, &related_table, &foreign_key, &primary_key])
83            .map_err(sz_orm_model::DbError::InvalidInput)?;
84        Ok(Self {
85            dialect,
86            main_table,
87            related_table,
88            foreign_key,
89            primary_key,
90            left_join,
91            where_conds: Vec::new(),
92            order_by: Vec::new(),
93            limit: None,
94            offset: None,
95        })
96    }
97
98    /// 追加 WHERE 条件（AND 连接）
99    #[must_use]
100    pub fn where_cond(mut self, cond: impl Into<String>) -> Self {
101        self.where_conds.push(cond.into());
102        self
103    }
104
105    /// 追加 ORDER BY（ASC）
106    #[must_use]
107    pub fn order_by(mut self, field: impl Into<String>) -> Self {
108        self.order_by.push((field.into(), false));
109        self
110    }
111
112    /// 追加 ORDER BY（DESC）
113    #[must_use]
114    pub fn order_desc(mut self, field: impl Into<String>) -> Self {
115        self.order_by.push((field.into(), true));
116        self
117    }
118
119    /// 设置 LIMIT
120    #[must_use]
121    pub fn limit(mut self, n: usize) -> Self {
122        self.limit = Some(n);
123        self
124    }
125
126    /// 设置 OFFSET
127    #[must_use]
128    pub fn offset(mut self, n: usize) -> Self {
129        self.offset = Some(n);
130        self
131    }
132
133    /// 构建 SELECT SQL（JOIN 模式）
134    ///
135    /// 生成的 SQL 形如：
136    /// ```sql
137    /// SELECT `main`.*, `related`.*
138    /// FROM `main`
139    /// LEFT JOIN `related` ON `related`.`fk` = `main`.`pk`
140    /// WHERE <conds>
141    /// ORDER BY <field> [DESC]
142    /// LIMIT <n> OFFSET <n>
143    /// ```
144    pub fn build(&self) -> String {
145        let join_type = if self.left_join {
146            "LEFT JOIN"
147        } else {
148            "INNER JOIN"
149        };
150        let mut sql = format!(
151            "SELECT {}.*, {}.* FROM {} {} {} ON {}.{} = {}.{}",
152            self.dialect.quote(&self.main_table),
153            self.dialect.quote(&self.related_table),
154            self.dialect.quote(&self.main_table),
155            join_type,
156            self.dialect.quote(&self.related_table),
157            self.dialect.quote(&self.related_table),
158            self.dialect.quote(&self.foreign_key),
159            self.dialect.quote(&self.main_table),
160            self.dialect.quote(&self.primary_key),
161        );
162
163        if !self.where_conds.is_empty() {
164            sql.push_str(" WHERE ");
165            sql.push_str(&self.where_conds.join(" AND "));
166        }
167
168        if !self.order_by.is_empty() {
169            let parts: Vec<String> = self
170                .order_by
171                .iter()
172                .map(|(f, desc)| {
173                    let d = if *desc { " DESC" } else { "" };
174                    format!("{}{}", self.dialect.quote(f), d)
175                })
176                .collect();
177            sql.push_str(" ORDER BY ");
178            sql.push_str(&parts.join(", "));
179        }
180
181        if let Some(n) = self.limit {
182            sql.push_str(&format!(" LIMIT {}", n));
183        }
184        if let Some(n) = self.offset {
185            sql.push_str(&format!(" OFFSET {}", n));
186        }
187
188        sql
189    }
190}
191
192/// 高级辅助：从 relations map 中提取关联表的元数据
193///
194/// 返回 `(related_table, foreign_key, primary_key, is_many)` 四元组。
195/// `is_many` 为 true 表示 HasMany / BelongsToMany / MorphMany，建议使用 eager load。
196pub fn inspect_relation<'a>(
197    relations: &'a HashMap<&'a str, Relation>,
198    name: &'a str,
199) -> Option<(&'a str, &'a str, &'a str, bool)> {
200    let rel = relations.get(name)?;
201    match rel {
202        Relation::HasMany(h) => Some((
203            h.child_model.as_str(),
204            h.foreign_key.as_str(),
205            h.child_pk.as_str(),
206            true,
207        )),
208        Relation::HasOne(h) => Some((
209            h.child_model.as_str(),
210            h.foreign_key.as_str(),
211            h.child_pk.as_str(),
212            false,
213        )),
214        Relation::BelongsTo(b) => Some((
215            b.parent_model.as_str(),
216            b.foreign_key.as_str(),
217            b.parent_pk.as_str(),
218            false,
219        )),
220        Relation::BelongsToMany(b) => Some((
221            b.target_model.as_str(),
222            b.foreign_key.as_str(),
223            b.other_key.as_str(),
224            true,
225        )),
226        Relation::MorphMany(m) => Some((
227            m.child_model.as_str(),
228            m.morph_id_column.as_str(),
229            "id",
230            true,
231        )),
232        Relation::MorphTo(m) => Some(("", m.morph_id_column.as_str(), "id", false)),
233    }
234}
235
236/// 生成 JOIN 模式 SQL（便捷函数）
237///
238/// 等价于 `FindWithRelated::new(...).build()`。
239pub fn find_with_related_join<'a>(
240    dialect: &'a dyn Dialect,
241    main_table: &'a str,
242    related_table: &'a str,
243    foreign_key: &'a str,
244    primary_key: &'a str,
245    left_join: bool,
246) -> Result<FindWithRelated<'a>, sz_orm_model::DbError> {
247    FindWithRelated::new(
248        dialect,
249        main_table,
250        related_table,
251        foreign_key,
252        primary_key,
253        left_join,
254    )
255}
256
257/// 生成 eager load 的两条 SQL（适合 1:N 关联）
258///
259/// 返回 `(main_sql, related_sql_template)`：
260/// - `main_sql`：SELECT 主表所有行（带用户 WHERE）
261/// - `related_sql_template`：SELECT 关联表 WHERE foreign_key IN (?) — 占位符由调用方填充
262///
263/// # 参数
264/// - `dialect`：方言
265/// - `main_table` / `related_table`：表名
266/// - `foreign_key`：关联表中的外键列
267/// - `main_where`：主表 WHERE 条件（可为空）
268///
269/// # 安全
270/// - `main_table` / `related_table` / `foreign_key` 会校验为合法 SQL 标识符
271/// - **`main_where` 由调用方负责安全**：调用方必须使用参数化查询或 `WhereBuilder` 构造，
272///   严禁直接拼接用户输入（H-2 风险点）
273#[tracing::instrument(skip(dialect), fields(main_table = main_table, related_table = related_table, strategy = "eager_sql"))]
274pub fn find_with_related_eager_sql(
275    dialect: &dyn Dialect,
276    main_table: &str,
277    related_table: &str,
278    foreign_key: &str,
279    main_where: Option<&str>,
280) -> Result<(String, String), sz_orm_model::DbError> {
281    // H-2 修复：校验表名/列名为合法标识符
282    validate_find_identifiers(&[main_table, related_table, foreign_key])
283        .map_err(sz_orm_model::DbError::InvalidInput)?;
284
285    let main_sql = if let Some(w) = main_where {
286        format!("SELECT * FROM {} WHERE {}", dialect.quote(main_table), w)
287    } else {
288        format!("SELECT * FROM {}", dialect.quote(main_table))
289    };
290
291    // 关联表 SQL 模板：调用方应将 ? 替换为实际主键列表（如 1,2,3）
292    let related_sql = format!(
293        "SELECT * FROM {} WHERE {} IN (?)",
294        dialect.quote(related_table),
295        dialect.quote(foreign_key),
296    );
297
298    Ok((main_sql, related_sql))
299}
300
301/// 生成子查询模式 SQL（适合 1:N 关联，避免主表行膨胀）
302///
303/// 生成的 SQL 形如：
304/// ```sql
305/// SELECT * FROM `main`
306/// WHERE `pk` IN (
307///   SELECT `fk` FROM `related` WHERE <related_where>
308/// )
309/// ```
310#[tracing::instrument(skip(dialect), fields(main_table = main_table, related_table = related_table, strategy = "subquery"))]
311pub fn find_with_related_subquery(
312    dialect: &dyn Dialect,
313    main_table: &str,
314    related_table: &str,
315    foreign_key: &str,
316    primary_key: &str,
317    related_where: Option<&str>,
318) -> Result<String, sz_orm_model::DbError> {
319    // H-2 修复：校验表名/列名为合法标识符
320    validate_find_identifiers(&[main_table, related_table, foreign_key, primary_key])
321        .map_err(sz_orm_model::DbError::InvalidInput)?;
322
323    let inner = if let Some(w) = related_where {
324        format!(
325            "SELECT {} FROM {} WHERE {}",
326            dialect.quote(foreign_key),
327            dialect.quote(related_table),
328            w
329        )
330    } else {
331        format!(
332            "SELECT {} FROM {}",
333            dialect.quote(foreign_key),
334            dialect.quote(related_table)
335        )
336    };
337    Ok(format!(
338        "SELECT * FROM {} WHERE {} IN ({})",
339        dialect.quote(main_table),
340        dialect.quote(primary_key),
341        inner
342    ))
343}
344
345// ============================================================================
346// WithRelation::load() 风格 API（SeaORM find_with_related 对应）
347// ============================================================================
348
349/// 关联关系描述（内部用）
350#[derive(Debug, Clone)]
351enum WithRelationKind {
352    /// 一对多：主表.id ← 关联表.fk
353    HasMany {
354        foreign_key: String,
355        primary_key: String,
356    },
357    /// 一对一：主表.id ← 关联表.fk
358    HasOne {
359        foreign_key: String,
360        primary_key: String,
361    },
362    /// 多对一：关联表.id ← 主表.fk
363    BelongsTo {
364        foreign_key: String,
365        primary_key: String,
366    },
367}
368
369/// 关联配置项
370#[derive(Debug, Clone)]
371struct WithRelationItem {
372    related_table: String,
373    kind: WithRelationKind,
374}
375
376/// SeaORM find_with_related 风格的关联加载器
377///
378/// # 用法
379///
380/// ```ignore
381/// use sz_orm_query::find_with_related::WithRelation;
382/// use sz_orm_model::dialect::get_dialect;
383/// use sz_orm_model::DbType;
384///
385/// let dialect = get_dialect(DbType::MySQL).unwrap();
386/// let loader = WithRelation::new(&*dialect, "users")
387///     .with_has_many("orders", "user_id", "id")
388///     .with_has_one("profiles", "user_id", "id")
389///     .load_eager(Some("users.id IN (1, 2, 3)"));
390///
391/// println!("{}", loader.main_sql());        // 主表 SQL
392/// println!("{}", loader.related_sql("orders").unwrap());  // 关联表 SQL
393/// ```
394pub struct WithRelation<'a> {
395    dialect: &'a dyn Dialect,
396    main_table: String,
397    relations: Vec<(&'a str, WithRelationItem)>,
398    main_where: Option<String>,
399}
400
401impl<'a> WithRelation<'a> {
402    /// 创建关联加载器
403    pub fn new(
404        dialect: &'a dyn Dialect,
405        main_table: impl Into<String>,
406    ) -> Result<Self, sz_orm_model::DbError> {
407        let main_table = main_table.into();
408        // H-2 修复：校验主表名
409        validate_find_identifiers(&[&main_table]).map_err(sz_orm_model::DbError::InvalidInput)?;
410        Ok(Self {
411            dialect,
412            main_table,
413            relations: Vec::new(),
414            main_where: None,
415        })
416    }
417
418    /// 添加 HasMany 关联
419    pub fn with_has_many(
420        mut self,
421        related: &'a str,
422        foreign_key: impl Into<String>,
423        primary_key: impl Into<String>,
424    ) -> Result<Self, sz_orm_model::DbError> {
425        let foreign_key = foreign_key.into();
426        let primary_key = primary_key.into();
427        // H-2 修复：校验关联表名/列名
428        validate_find_identifiers(&[related, &foreign_key, &primary_key])
429            .map_err(sz_orm_model::DbError::InvalidInput)?;
430        self.relations.push((
431            related,
432            WithRelationItem {
433                related_table: related.to_string(),
434                kind: WithRelationKind::HasMany {
435                    foreign_key,
436                    primary_key,
437                },
438            },
439        ));
440        Ok(self)
441    }
442
443    /// 添加 HasOne 关联
444    pub fn with_has_one(
445        mut self,
446        related: &'a str,
447        foreign_key: impl Into<String>,
448        primary_key: impl Into<String>,
449    ) -> Result<Self, sz_orm_model::DbError> {
450        let foreign_key = foreign_key.into();
451        let primary_key = primary_key.into();
452        // H-2 修复：校验关联表名/列名
453        validate_find_identifiers(&[related, &foreign_key, &primary_key])
454            .map_err(sz_orm_model::DbError::InvalidInput)?;
455        self.relations.push((
456            related,
457            WithRelationItem {
458                related_table: related.to_string(),
459                kind: WithRelationKind::HasOne {
460                    foreign_key,
461                    primary_key,
462                },
463            },
464        ));
465        Ok(self)
466    }
467
468    /// 添加 BelongsTo 关联
469    pub fn with_belongs_to(
470        mut self,
471        related: &'a str,
472        foreign_key: impl Into<String>,
473        primary_key: impl Into<String>,
474    ) -> Result<Self, sz_orm_model::DbError> {
475        let foreign_key = foreign_key.into();
476        let primary_key = primary_key.into();
477        // H-2 修复：校验关联表名/列名
478        validate_find_identifiers(&[related, &foreign_key, &primary_key])
479            .map_err(sz_orm_model::DbError::InvalidInput)?;
480        self.relations.push((
481            related,
482            WithRelationItem {
483                related_table: related.to_string(),
484                kind: WithRelationKind::BelongsTo {
485                    foreign_key,
486                    primary_key,
487                },
488            },
489        ));
490        Ok(self)
491    }
492
493    /// 执行 eager load（生成主表 SQL + 各关联表 SQL）
494    ///
495    /// - `main_where`：主表 WHERE 条件（None 表示无 WHERE）
496    ///
497    /// 返回 `self` 后通过 [`main_sql`](Self::main_sql) 和
498    /// [`related_sql`](Self::related_sql) 获取生成的 SQL。
499    ///
500    /// **P2-7 循环引用检测**：调用此方法前会自动检测重复关联名，
501    /// 若发现重复则返回 `DbError::InvalidInput`。
502    #[tracing::instrument(skip(self), fields(strategy = "eager", main_table = &self.main_table))]
503    pub fn load_eager(mut self, main_where: Option<&str>) -> Result<Self, sz_orm_model::DbError> {
504        // P2-7：检测重复关联名
505        self.check_duplicate_relations()?;
506        self.main_where = main_where.map(String::from);
507        Ok(self)
508    }
509
510    /// P2-7：检测重复关联名
511    ///
512    /// 相同关联名（related 字符串）添加多次会导致 SQL 生成错误
513    /// （同名字段冲突、冗余查询），必须在加载前检测。
514    ///
515    /// 检测到重复时返回 `DbError::InvalidInput`，给出明确的错误信息（含重复的关联名列表）。
516    fn check_duplicate_relations(&self) -> Result<(), sz_orm_model::DbError> {
517        let mut seen = std::collections::HashSet::new();
518        let mut duplicates = Vec::new();
519        for (name, _) in &self.relations {
520            if !seen.insert(*name) {
521                duplicates.push(*name);
522            }
523        }
524        if !duplicates.is_empty() {
525            return Err(sz_orm_model::DbError::InvalidInput(format!(
526                "WithRelation 重复关联检测失败：关联名 {:?} 被添加多次。请使用不同的关联名或移除重复项。",
527                duplicates
528            )));
529        }
530        Ok(())
531    }
532
533    /// 执行 JOIN load（生成单条 JOIN SQL）
534    ///
535    /// HasMany / HasOne → LEFT JOIN
536    /// BelongsTo → INNER JOIN
537    ///
538    /// **P2-7 循环引用检测**：调用此方法前会自动检测重复关联名。
539    #[tracing::instrument(skip(self), fields(strategy = "join", main_table = &self.main_table))]
540    pub fn load_join(&self, main_where: Option<&str>) -> Result<String, sz_orm_model::DbError> {
541        // P2-7：检测重复关联名
542        self.check_duplicate_relations()?;
543        let mut sql = format!("SELECT {}.*", self.dialect.quote(&self.main_table));
544        // 添加所有关联表的列
545        for (_, item) in &self.relations {
546            sql.push_str(&format!(", {}.*", self.dialect.quote(&item.related_table)));
547        }
548        sql.push_str(&format!(" FROM {}", self.dialect.quote(&self.main_table)));
549
550        for (_, item) in &self.relations {
551            let (join_type, left_col, right_col) = match &item.kind {
552                WithRelationKind::HasMany {
553                    foreign_key,
554                    primary_key,
555                }
556                | WithRelationKind::HasOne {
557                    foreign_key,
558                    primary_key,
559                } => (
560                    "LEFT JOIN",
561                    format!("{}.{}", item.related_table, foreign_key),
562                    format!("{}.{}", self.main_table, primary_key),
563                ),
564                // BelongsTo: 主表.fk 引用关联表.pk
565                // JOIN 条件: main.fk = related.pk（语义直观，等值连接可交换）
566                WithRelationKind::BelongsTo {
567                    foreign_key,
568                    primary_key,
569                } => (
570                    "INNER JOIN",
571                    format!("{}.{}", self.main_table, foreign_key),
572                    format!("{}.{}", item.related_table, primary_key),
573                ),
574            };
575            // 拆分 table.column 形式，分别 quote
576            let (l_table, l_col) = split_qualified(&left_col);
577            let (r_table, r_col) = split_qualified(&right_col);
578            sql.push_str(&format!(
579                " {} {} ON {}.{} = {}.{}",
580                join_type,
581                self.dialect.quote(&item.related_table),
582                self.dialect.quote(l_table),
583                self.dialect.quote(l_col),
584                self.dialect.quote(r_table),
585                self.dialect.quote(r_col),
586            ));
587        }
588
589        if let Some(w) = main_where {
590            sql.push_str(&format!(" WHERE {}", w));
591        }
592        Ok(sql)
593    }
594
595    /// 获取主表 SQL
596    pub fn main_sql(&self) -> String {
597        let base = format!("SELECT * FROM {}", self.dialect.quote(&self.main_table));
598        if let Some(w) = &self.main_where {
599            format!("{} WHERE {}", base, w)
600        } else {
601            base
602        }
603    }
604
605    /// 获取指定关联表的 SQL（默认占位符 `?`）
606    pub fn related_sql(&self, name: &str) -> Option<String> {
607        let (_, item) = self.relations.iter().find(|(n, _)| *n == name)?;
608        let foreign_key = match &item.kind {
609            WithRelationKind::HasMany { foreign_key, .. }
610            | WithRelationKind::HasOne { foreign_key, .. }
611            | WithRelationKind::BelongsTo { foreign_key, .. } => foreign_key.clone(),
612        };
613        // 默认使用 ? 占位符，调用方应在执行时绑定具体 ID
614        Some(format!(
615            "SELECT * FROM {} WHERE {} IN (?)",
616            self.dialect.quote(&item.related_table),
617            self.dialect.quote(&foreign_key),
618        ))
619    }
620
621    /// 获取指定关联表 SQL，使用具体的 ID 列表
622    ///
623    /// 接受任意可迭代且元素可 `ToString` 的输入（`&[i64]`、`Vec<String>`、`["a", "b"]` 等）。
624    ///
625    /// # 安全性（v0.2.2 修复 C-5）
626    ///
627    /// 每个 id 经 `sql_safety::validate_id_value` 严格校验，仅允许字母数字+下划线+减号，
628    /// 杜绝通过 id 拼接 SQL 注入。
629    pub fn related_sql_with_ids(
630        &self,
631        name: &str,
632        ids: impl IntoIterator<Item = impl ToString>,
633    ) -> Result<Option<String>, sz_orm_model::DbError> {
634        let (_, item) = self
635            .relations
636            .iter()
637            .find(|(n, _)| *n == name)
638            .ok_or_else(|| {
639                sz_orm_model::DbError::NotFound(format!("relation '{}' not found", name))
640            })?;
641        let foreign_key = match &item.kind {
642            WithRelationKind::HasMany { foreign_key, .. }
643            | WithRelationKind::HasOne { foreign_key, .. }
644            | WithRelationKind::BelongsTo { foreign_key, .. } => foreign_key.clone(),
645        };
646        // v0.2.2 修复 C-5：每个 id 必须通过严格校验，拒绝 SQL 注入
647        let ids_str = ids
648            .into_iter()
649            .map(|v| {
650                let s = v.to_string();
651                sz_orm_model::sql_safety::validate_id_value(&s)?;
652                Ok(s)
653            })
654            .collect::<Result<Vec<_>, sz_orm_model::DbError>>()?
655            .join(", ");
656        Ok(Some(format!(
657            "SELECT * FROM {} WHERE {} IN ({})",
658            self.dialect.quote(&item.related_table),
659            self.dialect.quote(&foreign_key),
660            ids_str,
661        )))
662    }
663
664    /// 获取所有已注册的关联名
665    pub fn relation_names(&self) -> Vec<&str> {
666        self.relations.iter().map(|(n, _)| *n).collect()
667    }
668}
669
670/// 将 `table.column` 拆分为 `(table, column)`
671fn split_qualified(s: &str) -> (&str, &str) {
672    match s.rfind('.') {
673        Some(idx) => (&s[..idx], &s[idx + 1..]),
674        None => (s, ""),
675    }
676}
677
678/// 校验 SQL 标识符（表名/列名）是否合法
679///
680/// H-2 修复：find_with_related 中的所有表名/列名拼接前必须校验，防止 SQL 注入。
681/// 校验规则与 `sz_orm_query::is_valid_sql_identifier` 一致。
682fn is_valid_sql_identifier(s: &str) -> bool {
683    if s.is_empty() || s.len() > 64 {
684        return false;
685    }
686    let mut chars = s.chars();
687    match chars.next() {
688        Some(c) if c.is_ascii_alphabetic() || c == '_' => {}
689        _ => return false,
690    }
691    chars.all(|c| c.is_ascii_alphanumeric() || c == '_')
692}
693
694/// 批量校验 find_with_related 中的 SQL 标识符
695fn validate_find_identifiers(idents: &[&str]) -> Result<(), String> {
696    for ident in idents {
697        if !is_valid_sql_identifier(ident) {
698            return Err(format!(
699                "invalid SQL identifier in find_with_related (potential SQL injection): {}",
700                ident
701            ));
702        }
703    }
704    Ok(())
705}
706
707#[cfg(test)]
708#[allow(deprecated)] // 测试 deprecated 的 where_cond 方法仍正常工作
709mod tests {
710    use super::*;
711    use sz_orm_model::get_dialect;
712    use sz_orm_model::DbType;
713    use sz_orm_model::{BelongsTo, BelongsToMany, HasMany, HasOne, MorphMany, MorphTo};
714
715    fn mysql_dialect() -> Box<dyn Dialect> {
716        get_dialect(DbType::MySQL).expect("MySQL dialect")
717    }
718
719    fn pg_dialect() -> Box<dyn Dialect> {
720        get_dialect(DbType::PostgreSQL).expect("PG dialect")
721    }
722
723    fn sqlite_dialect() -> Box<dyn Dialect> {
724        get_dialect(DbType::Sqlite).expect("SQLite dialect")
725    }
726
727    #[test]
728    fn join_left_basic() {
729        let d = mysql_dialect();
730        let sql = FindWithRelated::new(&*d, "users", "profiles", "user_id", "id", true)
731            .unwrap()
732            .build();
733        assert!(sql.contains("SELECT `users`.*, `profiles`.*"));
734        assert!(sql.contains("FROM `users`"));
735        assert!(sql.contains("LEFT JOIN `profiles`"));
736        assert!(sql.contains("ON `profiles`.`user_id` = `users`.`id`"));
737    }
738
739    #[test]
740    fn join_inner_basic() {
741        let d = mysql_dialect();
742        let sql = FindWithRelated::new(&*d, "users", "orders", "user_id", "id", false)
743            .unwrap()
744            .build();
745        assert!(sql.contains("INNER JOIN `orders`"));
746        assert!(!sql.contains("LEFT JOIN"));
747    }
748
749    #[test]
750    fn join_with_where_order_limit() {
751        let d = mysql_dialect();
752        let sql = FindWithRelated::new(&*d, "users", "orders", "user_id", "id", true)
753            .unwrap()
754            .where_cond("users.status = 'active'")
755            .where_cond("orders.amount > 100")
756            .order_desc("orders.created_at")
757            .limit(10)
758            .offset(20)
759            .build();
760        assert!(sql.contains("WHERE users.status = 'active' AND orders.amount > 100"));
761        assert!(sql.contains("ORDER BY `orders.created_at` DESC"));
762        assert!(sql.contains("LIMIT 10"));
763        assert!(sql.contains("OFFSET 20"));
764    }
765
766    #[test]
767    fn join_pg_dialect() {
768        let d = pg_dialect();
769        let sql = FindWithRelated::new(&*d, "users", "orders", "user_id", "id", true)
770            .unwrap()
771            .build();
772        assert!(sql.contains("SELECT \"users\".*, \"orders\".*"));
773        assert!(sql.contains("LEFT JOIN \"orders\""));
774        assert!(sql.contains("ON \"orders\".\"user_id\" = \"users\".\"id\""));
775    }
776
777    #[test]
778    fn join_sqlite_dialect() {
779        let d = sqlite_dialect();
780        let sql = FindWithRelated::new(&*d, "users", "orders", "user_id", "id", true)
781            .unwrap()
782            .build();
783        // SQLite 方言使用双引号（与 PG 类似）
784        assert!(sql.contains("LEFT JOIN \"orders\""));
785    }
786
787    #[test]
788    fn eager_sql_basic() {
789        let d = mysql_dialect();
790        let (main_sql, related_sql) =
791            find_with_related_eager_sql(&*d, "users", "orders", "user_id", Some("users.id > 0"))
792                .unwrap();
793        assert_eq!(main_sql, "SELECT * FROM `users` WHERE users.id > 0");
794        assert_eq!(related_sql, "SELECT * FROM `orders` WHERE `user_id` IN (?)");
795    }
796
797    #[test]
798    fn eager_sql_no_where() {
799        let d = mysql_dialect();
800        let (main_sql, related_sql) =
801            find_with_related_eager_sql(&*d, "users", "orders", "user_id", None).unwrap();
802        assert_eq!(main_sql, "SELECT * FROM `users`");
803        assert_eq!(related_sql, "SELECT * FROM `orders` WHERE `user_id` IN (?)");
804    }
805
806    #[test]
807    fn subquery_basic() {
808        let d = mysql_dialect();
809        let sql = find_with_related_subquery(
810            &*d,
811            "users",
812            "orders",
813            "user_id",
814            "id",
815            Some("orders.amount > 100"),
816        )
817        .unwrap();
818        assert_eq!(
819            sql,
820            "SELECT * FROM `users` WHERE `id` IN (SELECT `user_id` FROM `orders` WHERE orders.amount > 100)"
821        );
822    }
823
824    #[test]
825    fn subquery_no_where() {
826        let d = mysql_dialect();
827        let sql =
828            find_with_related_subquery(&*d, "users", "orders", "user_id", "id", None).unwrap();
829        assert_eq!(
830            sql,
831            "SELECT * FROM `users` WHERE `id` IN (SELECT `user_id` FROM `orders`)"
832        );
833    }
834
835    #[test]
836    fn inspect_relation_has_many() {
837        let mut rels = HashMap::new();
838        rels.insert(
839            "orders",
840            Relation::HasMany(HasMany {
841                foreign_key: "user_id".to_string(),
842                child_model: "orders".to_string(),
843                child_pk: "id".to_string(),
844            }),
845        );
846        let info = inspect_relation(&rels, "orders").expect("relation exists");
847        assert_eq!(info.0, "orders");
848        assert_eq!(info.1, "user_id");
849        assert_eq!(info.2, "id");
850        assert!(info.3, "HasMany 应标记为 is_many=true");
851    }
852
853    #[test]
854    fn inspect_relation_has_one() {
855        let mut rels = HashMap::new();
856        rels.insert(
857            "profile",
858            Relation::HasOne(HasOne {
859                foreign_key: "user_id".to_string(),
860                child_model: "profiles".to_string(),
861                child_pk: "id".to_string(),
862            }),
863        );
864        let info = inspect_relation(&rels, "profile").expect("relation exists");
865        assert_eq!(info.0, "profiles");
866        assert!(!info.3, "HasOne 应标记为 is_many=false");
867    }
868
869    #[test]
870    fn inspect_relation_belongs_to() {
871        let mut rels = HashMap::new();
872        rels.insert(
873            "user",
874            Relation::BelongsTo(BelongsTo {
875                foreign_key: "user_id".to_string(),
876                parent_model: "users".to_string(),
877                parent_pk: "id".to_string(),
878            }),
879        );
880        let info = inspect_relation(&rels, "user").expect("relation exists");
881        assert_eq!(info.0, "users");
882        assert!(!info.3, "BelongsTo 应标记为 is_many=false");
883    }
884
885    #[test]
886    fn inspect_relation_belongs_to_many() {
887        let mut rels = HashMap::new();
888        rels.insert(
889            "roles",
890            Relation::BelongsToMany(BelongsToMany {
891                junction_table: "user_role".to_string(),
892                foreign_key: "user_id".to_string(),
893                other_key: "role_id".to_string(),
894                target_model: "roles".to_string(),
895                target_pk: "id".to_string(),
896            }),
897        );
898        let info = inspect_relation(&rels, "roles").expect("relation exists");
899        assert_eq!(info.0, "roles");
900        assert!(info.3, "BelongsToMany 应标记为 is_many=true");
901    }
902
903    #[test]
904    fn inspect_relation_morph_many() {
905        let mut rels = HashMap::new();
906        rels.insert(
907            "comments",
908            Relation::MorphMany(MorphMany {
909                child_model: "comments".to_string(),
910                morph_type_column: "commentable_type".to_string(),
911                morph_id_column: "commentable_id".to_string(),
912                morph_type_value: "Post".to_string(),
913            }),
914        );
915        let info = inspect_relation(&rels, "comments").expect("relation exists");
916        assert_eq!(info.0, "comments");
917        assert_eq!(info.1, "commentable_id");
918        assert!(info.3, "MorphMany 应标记为 is_many=true");
919    }
920
921    #[test]
922    fn inspect_relation_morph_to() {
923        let mut rels = HashMap::new();
924        rels.insert(
925            "commentable",
926            Relation::MorphTo(MorphTo {
927                morph_type_column: "commentable_type".to_string(),
928                morph_id_column: "commentable_id".to_string(),
929            }),
930        );
931        let info = inspect_relation(&rels, "commentable").expect("relation exists");
932        assert_eq!(info.1, "commentable_id");
933        assert!(!info.3, "MorphTo 应标记为 is_many=false");
934    }
935
936    #[test]
937    fn inspect_relation_not_found() {
938        let rels = HashMap::<&str, Relation>::new();
939        assert!(inspect_relation(&rels, "nonexistent").is_none());
940    }
941
942    // ===== 边界测试 =====
943
944    #[test]
945    fn empty_where_produces_no_where_clause() {
946        let d = mysql_dialect();
947        let sql = FindWithRelated::new(&*d, "users", "orders", "user_id", "id", true)
948            .unwrap()
949            .build();
950        assert!(!sql.contains("WHERE"));
951    }
952
953    #[test]
954    fn multiple_order_by() {
955        let d = mysql_dialect();
956        let sql = FindWithRelated::new(&*d, "users", "orders", "user_id", "id", true)
957            .unwrap()
958            .order_by("users.id")
959            .order_desc("orders.created_at")
960            .build();
961        assert!(sql.contains("ORDER BY `users.id`, `orders.created_at` DESC"));
962    }
963
964    #[test]
965    fn find_with_related_join_convenience_fn() {
966        let d = mysql_dialect();
967        let sql = find_with_related_join(&*d, "users", "orders", "user_id", "id", true)
968            .unwrap()
969            .where_cond("users.id = 1")
970            .build();
971        assert!(sql.contains("WHERE users.id = 1"));
972    }
973
974    // ---- WithRelation::load 风格 API 测试 ----
975
976    #[test]
977    fn with_relation_load_has_many_eager() {
978        let d = mysql_dialect();
979        let loader = WithRelation::new(&*d, "users")
980            .unwrap()
981            .with_has_many("orders", "user_id", "id")
982            .unwrap()
983            .load_eager(Some("users.id IN (1, 2, 3)"))
984            .expect("load_eager should succeed for non-duplicate relations");
985        assert_eq!(
986            loader.main_sql(),
987            "SELECT * FROM `users` WHERE users.id IN (1, 2, 3)"
988        );
989        // related_sql 默认用 ? 占位符；调用方应在执行时绑定具体 ID 列表
990        assert_eq!(
991            loader.related_sql("orders").unwrap(),
992            "SELECT * FROM `orders` WHERE `user_id` IN (?)"
993        );
994        // related_sql_with_ids 可传入具体 ID 列表（支持 i64/String/&str）
995        assert_eq!(
996            loader
997                .related_sql_with_ids("orders", [1_i64, 2, 3])
998                .unwrap()
999                .unwrap(),
1000            "SELECT * FROM `orders` WHERE `user_id` IN (1, 2, 3)"
1001        );
1002        assert_eq!(
1003            loader
1004                .related_sql_with_ids("orders", ["a", "b", "c"])
1005                .unwrap()
1006                .unwrap(),
1007            "SELECT * FROM `orders` WHERE `user_id` IN (a, b, c)"
1008        );
1009    }
1010
1011    #[test]
1012    fn with_relation_load_has_one_join() {
1013        let d = mysql_dialect();
1014        let sql = WithRelation::new(&*d, "users")
1015            .unwrap()
1016            .with_has_one("profiles", "user_id", "id")
1017            .unwrap()
1018            .load_join(Some("users.id = 1"))
1019            .expect("load_join should succeed for non-duplicate relations");
1020        assert!(sql.contains("LEFT JOIN `profiles`"));
1021        assert!(sql.contains("ON `profiles`.`user_id` = `users`.`id`"));
1022        assert!(sql.contains("WHERE users.id = 1"));
1023    }
1024
1025    #[test]
1026    fn with_relation_load_belongs_to_join() {
1027        let d = mysql_dialect();
1028        let sql = WithRelation::new(&*d, "orders")
1029            .unwrap()
1030            .with_belongs_to("users", "user_id", "id")
1031            .unwrap()
1032            .load_join(None)
1033            .expect("load_join should succeed for non-duplicate relations");
1034        // BelongsTo: orders.user_id → users.id（INNER JOIN）
1035        // JOIN 条件方向：main.fk = related.pk（语义直观）
1036        assert!(sql.contains("INNER JOIN `users`"));
1037        assert!(sql.contains("ON `orders`.`user_id` = `users`.`id`"));
1038    }
1039
1040    #[test]
1041    fn with_relation_multiple_relations_eager() {
1042        let d = mysql_dialect();
1043        let loader = WithRelation::new(&*d, "users")
1044            .unwrap()
1045            .with_has_many("orders", "user_id", "id")
1046            .unwrap()
1047            .with_has_many("posts", "author_id", "id")
1048            .unwrap()
1049            .with_has_one("profiles", "user_id", "id")
1050            .unwrap()
1051            .load_eager(None)
1052            .expect("load_eager should succeed for non-duplicate relations");
1053        assert_eq!(loader.main_sql(), "SELECT * FROM `users`");
1054        // 三个关联都应该有对应的 SQL
1055        assert!(loader.related_sql("orders").is_some());
1056        assert!(loader.related_sql("posts").is_some());
1057        assert!(loader.related_sql("profiles").is_some());
1058        // 不存在的关联应返回 None
1059        assert!(loader.related_sql("nonexistent").is_none());
1060    }
1061
1062    #[test]
1063    fn with_relation_load_eager_with_specific_ids() {
1064        let d = mysql_dialect();
1065        let loader = WithRelation::new(&*d, "users")
1066            .unwrap()
1067            .with_has_many("orders", "user_id", "id")
1068            .unwrap()
1069            .load_eager(None)
1070            .expect("load_eager should succeed for non-duplicate relations");
1071        // 自定义 ID 列表（i64 数组直接传入，泛型自动推断）
1072        let orders_sql = loader
1073            .related_sql_with_ids("orders", [1_i64, 5, 10])
1074            .unwrap()
1075            .unwrap();
1076        assert_eq!(
1077            orders_sql,
1078            "SELECT * FROM `orders` WHERE `user_id` IN (1, 5, 10)"
1079        );
1080    }
1081
1082    #[test]
1083    fn with_relation_pg_dialect_eager() {
1084        let d = pg_dialect();
1085        let loader = WithRelation::new(&*d, "users")
1086            .unwrap()
1087            .with_has_many("orders", "user_id", "id")
1088            .unwrap()
1089            .load_eager(Some("users.id > 100"))
1090            .expect("load_eager should succeed for non-duplicate relations");
1091        assert_eq!(
1092            loader.main_sql(),
1093            "SELECT * FROM \"users\" WHERE users.id > 100"
1094        );
1095        // 默认占位符 ?，调用方应替换为实际 ID
1096        assert_eq!(
1097            loader.related_sql("orders").unwrap(),
1098            "SELECT * FROM \"orders\" WHERE \"user_id\" IN (?)"
1099        );
1100        // 使用具体 ID 列表（PG 方言）
1101        assert_eq!(
1102            loader
1103                .related_sql_with_ids("orders", [100_i64])
1104                .unwrap()
1105                .unwrap(),
1106            "SELECT * FROM \"orders\" WHERE \"user_id\" IN (100)"
1107        );
1108    }
1109
1110    #[test]
1111    fn with_relation_sqlite_dialect_join() {
1112        let d = sqlite_dialect();
1113        let sql = WithRelation::new(&*d, "users")
1114            .unwrap()
1115            .with_has_one("profiles", "user_id", "id")
1116            .unwrap()
1117            .load_join(None)
1118            .expect("load_join should succeed for non-duplicate relations");
1119        assert!(sql.contains("LEFT JOIN \"profiles\""));
1120    }
1121
1122    // v0.2.2 修复 C-5：SQL 注入测试套件
1123
1124    #[test]
1125    fn with_relation_rejects_sql_injection_in_id_semicolon() {
1126        let d = mysql_dialect();
1127        let loader = WithRelation::new(&*d, "users")
1128            .unwrap()
1129            .with_has_many("orders", "user_id", "id")
1130            .unwrap()
1131            .load_eager(None)
1132            .expect("load_eager should succeed for non-duplicate relations");
1133        let err = loader
1134            .related_sql_with_ids("orders", ["1; DROP TABLE users"])
1135            .unwrap_err();
1136        assert!(matches!(err, sz_orm_model::DbError::InvalidInput(_)));
1137    }
1138
1139    #[test]
1140    fn with_relation_rejects_sql_injection_in_id_or() {
1141        let d = mysql_dialect();
1142        let loader = WithRelation::new(&*d, "users")
1143            .unwrap()
1144            .with_has_many("orders", "user_id", "id")
1145            .unwrap()
1146            .load_eager(None)
1147            .expect("load_eager should succeed for non-duplicate relations");
1148        let err = loader
1149            .related_sql_with_ids("orders", ["1) OR 1=1"])
1150            .unwrap_err();
1151        assert!(matches!(err, sz_orm_model::DbError::InvalidInput(_)));
1152    }
1153
1154    #[test]
1155    fn with_relation_rejects_sql_injection_in_id_quote() {
1156        let d = mysql_dialect();
1157        let loader = WithRelation::new(&*d, "users")
1158            .unwrap()
1159            .with_has_many("orders", "user_id", "id")
1160            .unwrap()
1161            .load_eager(None)
1162            .expect("load_eager should succeed for non-duplicate relations");
1163        let err = loader
1164            .related_sql_with_ids("orders", ["' OR '1'='1"])
1165            .unwrap_err();
1166        assert!(matches!(err, sz_orm_model::DbError::InvalidInput(_)));
1167    }
1168
1169    #[test]
1170    fn with_relation_rejects_sql_injection_in_id_comment() {
1171        let d = mysql_dialect();
1172        let loader = WithRelation::new(&*d, "users")
1173            .unwrap()
1174            .with_has_many("orders", "user_id", "id")
1175            .unwrap()
1176            .load_eager(None)
1177            .expect("load_eager should succeed for non-duplicate relations");
1178        let err = loader.related_sql_with_ids("orders", ["1--"]).unwrap_err();
1179        assert!(matches!(err, sz_orm_model::DbError::InvalidInput(_)));
1180    }
1181
1182    #[test]
1183    fn with_relation_rejects_sql_injection_in_id_with_space() {
1184        let d = mysql_dialect();
1185        let loader = WithRelation::new(&*d, "users")
1186            .unwrap()
1187            .with_has_many("orders", "user_id", "id")
1188            .unwrap()
1189            .load_eager(None)
1190            .expect("load_eager should succeed for non-duplicate relations");
1191        let err = loader.related_sql_with_ids("orders", ["1 2"]).unwrap_err();
1192        assert!(matches!(err, sz_orm_model::DbError::InvalidInput(_)));
1193    }
1194}
sz_orm_query/find_with_related.rs

sz_orm_query/
find_with_related.rs
