sqlx-query-dsl 0.1.1

A query DSL extension for SQLx
Documentation 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105


use sqlx::{MySql, QueryBuilder};
use crate::params::{FilterExpr, Op, ExistsValue};
use crate::whitelist::FieldWhitelist;

/// 递归构建过滤条件的 SQL WHERE 子句
///
/// # Arguments
/// * `qb` - sqlx 的 QueryBuilder，用于追加 SQL 片段和绑定参数
/// * `expr` - 过滤表达式，定义了具体的过滤逻辑（支持嵌套）
/// * `whitelist` - 字段白名单，用于校验字段名是否合法，防止 SQL 注入
pub fn build_filter<'a>(
    qb: &mut QueryBuilder<'a, MySql>,
    expr: &'a FilterExpr,
    whitelist: &FieldWhitelist,
) -> Result<(), String> {
    match expr {
        FilterExpr::And { and } => {
            qb.push("(");
            for (i, e) in and.iter().enumerate() {
                if i > 0 { qb.push(" AND "); }
                build_filter(qb, e, whitelist)?;
            }
            qb.push(")");
        }
        FilterExpr::Or { or } => {
            qb.push("(");
            for (i, e) in or.iter().enumerate() {
                if i > 0 { qb.push(" OR "); }
                build_filter(qb, e, whitelist)?;
            }
            qb.push(")");
        }
        FilterExpr::Cond(c) => match c.op {
            Op::IsNull => {
                let f = c.field.as_ref().unwrap();
                whitelist.check(f)?;
                qb.push(f).push(" IS NULL");
            }
            Op::IsNotNull => {
                let f = c.field.as_ref().unwrap();
                whitelist.check(f)?;
                qb.push(f).push(" IS NOT NULL");
            }
            Op::Exists | Op::NotExists => {
                let v: ExistsValue =
                    serde_json::from_value(c.value.clone().unwrap()).unwrap();
                qb.push(if matches!(c.op, Op::Exists) { "EXISTS (" } else { "NOT EXISTS (" });
                qb.push(&v.sql);
                if let Some(bind) = v.bind {
                    for b in bind {
                        qb.push_bind(b);
                    }
                }
                qb.push(")");
            }
            _ => {
                let f = c.field.as_ref().unwrap();
                whitelist.check(f)?;
                qb.push(f);
                match c.op {
                    Op::Eq => { qb.push(" = "); qb.push_bind(c.value.as_ref().unwrap()); }
                    Op::Ne => { qb.push(" != "); qb.push_bind(c.value.as_ref().unwrap()); }
                    Op::Like => {
                        qb.push(" LIKE ");
                        qb.push_bind(format!("%{}%", c.value.as_ref().unwrap().as_str().unwrap()));
                    }
                    Op::Gt => { qb.push(" > "); qb.push_bind(c.value.as_ref().unwrap()); }
                    Op::Lt => { qb.push(" < "); qb.push_bind(c.value.as_ref().unwrap()); }
                    Op::Gte => { qb.push(" >= "); qb.push_bind(c.value.as_ref().unwrap()); }
                    Op::Lte => { qb.push(" <= "); qb.push_bind(c.value.as_ref().unwrap()); }
                    Op::In | Op::NotIn => {
                        let arr = c.value.as_ref().unwrap().as_array().unwrap();
                        qb.push(if matches!(c.op, Op::In) { " IN (" } else { " NOT IN (" });
                        for (i, v) in arr.iter().enumerate() {
                            if i > 0 { qb.push(", "); }
                            qb.push_bind(v);
                        }
                        qb.push(")");
                    }
                    Op::Between => {
                        let arr = c.value.as_ref().unwrap().as_array().unwrap();
                        qb.push(" BETWEEN ");
                        qb.push_bind(&arr[0]);
                        qb.push(" AND ");
                        qb.push_bind(&arr[1]);
                    }
                    Op::FindInSet => {
                        qb.push(" FIND_IN_SET(");
                        qb.push_bind(c.value.as_ref().unwrap());
                        qb.push(", ").push(f).push(")");
                    }
                    Op::JsonContains => {
                        qb.push(" JSON_CONTAINS(");
                        qb.push(f).push(", ");
                        qb.push_bind(c.value.as_ref().unwrap().to_string());
                        qb.push(")");
                    }
                    _ => {}
                }
            }
        },
    }
    Ok(())
}