Query condition simplification incoming processing

[zhangwuqiao]

I used PHP and Java to develop before. Recently, I was studying trust development. I saw the high quality of the code you wrote. Thank you for your hard work and dedication.
During dynamic query, for example, there are 20 query fields on my front-end interface. During query, I don't know which field the user wants to query. When he confirms the keyword of the queried field and the corresponding value of the data table field, I dynamically assemble the data transmitted from the front-end into a variable, which may be HashMap or self-defined variable, You can pass in variables through a fixed method?
example:
1.map.insert("field1","1,2,3"); Automatically recognized as field1 in ('1 ',' 2 ',' 3 '); Consent word "in 1,2,3" "not_in 1,2,3"
2. map. Insert ("field1", "processing"); Automatically identified as field1 = 'processing'; Same meaning as "= in process"
3.map.insert("or field1",">= 50"); Automatically recognized as field1 > = '50'; > =, < =, >, <= Same processing
If the relationship with the previous one is an or relationship, the database field cannot have spaces. If there are spaces, the front is an or relationship, the front is a relationship, and the back is a field name
4.map.insert("field1","50~100"); Automatically recognized as field1 between '50' and '100'
5.map.insert("field1","%abc%"); Automatically recognized as field1 like '% ABC%'; Same processing as "not_like% ABC" "not_like ABC%"

    let (sql, values) = Query::select()
        .columns(vec![
            Character::Id,
            Character::Character,
            Character::FontSize,
        ])
        .from(Character::Table)
        .condition_all(map) //batch incoming
        .order_by(Character::Id, Order::Desc)
        .limit(1)
        .build(PostgresQueryBuilder);

The above is just what I want to express, which means that there is a simplified method of importing conditional values, so that developers can handle development work more efficiently and quickly.

Everyone has different needs, but you can define a standard for everyone to use.

[tyt2y3]

Hi and welcome.

The Condition data structure is the intended tool.

https://docs.rs/sea-orm/0.2.x/sea_orm/query/struct.Condition.html

let mut conditions = Condition::all();
if let Some(name) = params.name {
    conditions = conditions.add(cake::Column::Name.contains(name));
}
// .. other conditions
cake::Entity::find().filter(conditions);

Let me know what you think about it?

[zhangwuqiao]

Thank you.

[tyt2y3]

Edit: the 'add' method takes ownership of self, so it has to be mut when defined. Oh and there is also an add_option which can simplify the above code:

struct Input {
    name: Option<String>,
}
let input = Input { name: Some("cheese".to_owned()) };
assert_eq!(
    cake::Entity::find()
        .filter(
            Condition::all()
                .add_option(input.name.map(|n| cake::Column::Name.contains(&n)))
        )
        .build(DbBackend::MySql)
        .to_string(),
    "SELECT `cake`.`id`, `cake`.`name` FROM `cake` WHERE `cake`.`name` LIKE '%cheese%'"
);

[tyt2y3]

Oh I thought this belongs to SeaORM. I guess it also applies?

[tyt2y3]

Okay this is the final one

use sea_query::{*, tests_cfg::*};
let query = Query::select()
    .column(Glyph::Image)
    .from(Glyph::Table)
    .cond_where(
        Cond::all()
            .add_option(Some(Expr::tbl(Glyph::Table, Glyph::Image).like("A%")))
            .add_option(None::<SimpleExpr>)
    )
    .to_owned();
assert_eq!(
    query.to_string(MysqlQueryBuilder),
    r#"SELECT `image` FROM `glyph` WHERE `glyph`.`image` LIKE 'A%'"#
);