sqlx_query_dsl/
builder.rs

1
2use sqlx::{MySql, QueryBuilder};
3use crate::params::{FilterExpr, Op, ExistsValue};
4use crate::whitelist::FieldWhitelist;
5
6/// 递归构建过滤条件的 SQL WHERE 子句
7///
8/// # Arguments
9/// * `qb` - sqlx 的 QueryBuilder，用于追加 SQL 片段和绑定参数
10/// * `expr` - 过滤表达式，定义了具体的过滤逻辑（支持嵌套）
11/// * `whitelist` - 字段白名单，用于校验字段名是否合法，防止 SQL 注入
12pub fn build_filter<'a>(
13    qb: &mut QueryBuilder<'a, MySql>,
14    expr: &'a FilterExpr,
15    whitelist: &FieldWhitelist,
16) -> Result<(), String> {
17    match expr {
18        FilterExpr::And { and } => {
19            qb.push("(");
20            for (i, e) in and.iter().enumerate() {
21                if i > 0 { qb.push(" AND "); }
22                build_filter(qb, e, whitelist)?;
23            }
24            qb.push(")");
25        }
26        FilterExpr::Or { or } => {
27            qb.push("(");
28            for (i, e) in or.iter().enumerate() {
29                if i > 0 { qb.push(" OR "); }
30                build_filter(qb, e, whitelist)?;
31            }
32            qb.push(")");
33        }
34        FilterExpr::Cond(c) => match c.op {
35            Op::IsNull => {
36                let f = c.field.as_ref().unwrap();
37                whitelist.check(f)?;
38                qb.push(f).push(" IS NULL");
39            }
40            Op::IsNotNull => {
41                let f = c.field.as_ref().unwrap();
42                whitelist.check(f)?;
43                qb.push(f).push(" IS NOT NULL");
44            }
45            Op::Exists | Op::NotExists => {
46                let v: ExistsValue =
47                    serde_json::from_value(c.value.clone().unwrap()).unwrap();
48                qb.push(if matches!(c.op, Op::Exists) { "EXISTS (" } else { "NOT EXISTS (" });
49                qb.push(&v.sql);
50                if let Some(bind) = v.bind {
51                    for b in bind {
52                        qb.push_bind(b);
53                    }
54                }
55                qb.push(")");
56            }
57            _ => {
58                let f = c.field.as_ref().unwrap();
59                whitelist.check(f)?;
60                qb.push(f);
61                match c.op {
62                    Op::Eq => { qb.push(" = "); qb.push_bind(c.value.as_ref().unwrap()); }
63                    Op::Ne => { qb.push(" != "); qb.push_bind(c.value.as_ref().unwrap()); }
64                    Op::Like => {
65                        qb.push(" LIKE ");
66                        qb.push_bind(format!("%{}%", c.value.as_ref().unwrap().as_str().unwrap()));
67                    }
68                    Op::Gt => { qb.push(" > "); qb.push_bind(c.value.as_ref().unwrap()); }
69                    Op::Lt => { qb.push(" < "); qb.push_bind(c.value.as_ref().unwrap()); }
70                    Op::Gte => { qb.push(" >= "); qb.push_bind(c.value.as_ref().unwrap()); }
71                    Op::Lte => { qb.push(" <= "); qb.push_bind(c.value.as_ref().unwrap()); }
72                    Op::In | Op::NotIn => {
73                        let arr = c.value.as_ref().unwrap().as_array().unwrap();
74                        qb.push(if matches!(c.op, Op::In) { " IN (" } else { " NOT IN (" });
75                        for (i, v) in arr.iter().enumerate() {
76                            if i > 0 { qb.push(", "); }
77                            qb.push_bind(v);
78                        }
79                        qb.push(")");
80                    }
81                    Op::Between => {
82                        let arr = c.value.as_ref().unwrap().as_array().unwrap();
83                        qb.push(" BETWEEN ");
84                        qb.push_bind(&arr[0]);
85                        qb.push(" AND ");
86                        qb.push_bind(&arr[1]);
87                    }
88                    Op::FindInSet => {
89                        qb.push(" FIND_IN_SET(");
90                        qb.push_bind(c.value.as_ref().unwrap());
91                        qb.push(", ").push(f).push(")");
92                    }
93                    Op::JsonContains => {
94                        qb.push(" JSON_CONTAINS(");
95                        qb.push(f).push(", ");
96                        qb.push_bind(c.value.as_ref().unwrap().to_string());
97                        qb.push(")");
98                    }
99                    _ => {}
100                }
101            }
102        },
103    }
104    Ok(())
105}
sqlx_query_dsl/builder.rs

sqlx_query_dsl/
builder.rs
