Rust diesel conditionally filter a query

Question

I am trying to use diesel for a project and I would like to have a "filterable" type. The idea is that you can go to /api/foo?id=10&bar=11 and it would return a struct Foo:

struct Foo {
    id: Option<i64>,
    bar: Option<i64>,
    name: Option<String>,
}

Such as:

Foo {
   id: Some(10),
   bar: Some(11),
   name: None,
}

I've been scouring the internet for a way to filter by the fields that exist, but I am unable to find a solution that works. I was initially using the mysql driver and constructing sql queries with proc macros, but diesel is a lot nicer to work with and I was wondering if there was a way to get the same behaviour I had with the mysql driver with diesel.

Answer 1

You can do more complex queries with nested and grouped and/or conditions and even re-use queries as conditions.

Define a flexible stucture

enum Condition {
    bar(i32),
    And(Vec<Condition>),
    Or(Vec<Condition>),
}

Create boxed type

type Source = foo::dsl::foo;
type BoxedCondition = Box<dyn BoxableExpression<Source, Sqlite, SqlType = Bool>>;

Then can create a boxed condition

impl Condition {
    fn to_boxed_condition(self) -> Option<BoxedCondition> {
        Some(match self {
            // Here we box the condition
            Condition::bar(value) => Box::new(foo::dsl::bar.eq(value)),
            // For and/or
            Condition::And(conditions) => match create_filter(conditions, AndOr::And) {
                Some(boxed_condition) => boxed_condition,
                None => return None,
            },
            Condition::Or(conditions) => match create_filter(conditions, AndOr::Or) {
                Some(boxed_condition) => boxed_condition,
                None => return None,
            },
        })
    }
}

And then apply boxed conditions for a Vec of conditions

fn create_filter(conditions: Vec<Condition>, and_or: AndOr) -> Option<BoxedCondition> {
    conditions
        .into_iter()
        // Map into array of boxed conditions
        .filter_map::<BoxedCondition, _>(Condition::to_boxed_condition)
        // Reduce to a boxed_condition1.and(boxed_condition2).and(boxed_condition3)...
        .fold(None, |boxed_conditions, boxed_condition| {
            Some(match boxed_conditions {
                Some(bc) => match and_or {
                    AndOr::And => Box::new(bc.and(boxed_condition)),
                    AndOr::Or => Box::new(bc.or(boxed_condition)),
                },
                None => boxed_condition,
            })
        })
}

Above can be used in a diesel-rs query:

// bar = 1 or (bar = 1 and bar = 0)

let condition = create_filter(
    vec![Condition::Or(vec![
        Condition::bar(1),
        Condition::And(vec![Condition::bar(1), Condition::bar(0)]),
    ])],
    AndOr::And,
)
.unwrap();


let result = foo::dsl::foo
    .filter(condition)
    .select(foo::dsl::id)
    .load::<String>(&mut connection)
    .unwrap()
)

// (bar = 1 or bar = 1) and bar = 0 === false

let condition = create_filter(
    vec![Condition::And(vec![
        Condition::Or(vec![Condition::bar(1), Condition::bar(1)]),
        Condition::bar(0),
    ])],
    AndOr::And,
)
.unwrap();

foo::dsl::foo
    .filter(condition)
    .select(foo::dsl::id)
    .load::<String>(&mut connection)
    .unwrap()

See full example here

For query re-use as inner statement you can go reference Inner Query section of the full by example tutorial

Answer 2

You can use the into_boxed method, which:

Boxes the pieces of a query into a single type. This is useful for cases where you want to conditionally modify a query, but need the type to remain the same. A boxed query will incur a minor performance penalty, as the query builder can no longer be inlined by the compiler. For most applications this cost will be minimal.

use crate::schema::foo;

let mut query = foo::table.into_boxed();

if let Some(id) = foo.id {
    query = query.filter(foo::id.eq(id));
}

if let Some(bar) = foo.bar {
    query = query.filter(foo::bar.eq(bar));
}

if let Some(name) = foo.name {
    query = query.filter(foo::name.eq(name));
}

let results = query
    .load::<Foo>(&conn)
    .expect("error loading foo");