# Dynamic filtering of a query in a dynamic schema

**URL:** <https://discourse.diesel.rs/t/dynamic-filtering-of-a-query-in-a-dynamic-schema/134>\
**Category:** Uncategorized\
**Created:** [January 27, 2020, 5:15pm UTC](https://discourse.diesel.rs/t/dynamic-filtering-of-a-query-in-a-dynamic-schema/134 "2020-01-27T17:15:36Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Razican](https://yyz2.discourse-cdn.com/flex036/user_avatar/discourse.diesel.rs/razican/32/67_2.png) [@Razican](https://discourse.diesel.rs/u/Razican)\
**Post date:** [January 27, 2020, 5:15pm UTC](https://discourse.diesel.rs/t/dynamic-filtering-of-a-query-in-a-dynamic-schema/134/1 "2020-01-27T17:15:36Z")

</div>

Hi!

I’m trying to filter a query using a dynamic structure that is a wrapper over a `Vec<Vec<FilterClause>>`, where `FilterClause` is a structure that can generate something like `Eq`, `NotEq` or one of those (not sure the trait).

The outer vector should have the “OR” clauses, and the internal one the “AND” clauses. So something like this:

```auto
[
    [aEQb, bNEQc],
    [aEQc, cNEQb],
]

```

Should be converted to something like `WHERE (a = b AND b != c) OR (a = c AND c != b)`  
This vector structure is wrapped in a `Filter` structure, that ideally should have an `apply_filter()` method to get a query and either mutate it or return a new query (which is a better/more idiomatic approach?)

To complicate things a bit more, I’m using an unreleased version of `diesel_dynamic_schema`, that allows me using dynamic selects in a changing schema. This is the code calling the `apply_filter()`:

```auto
type DynTable = diesel_dynamic_schema::Table<String>;

pub(crate) fn get_record_list<S>(
    db_conn: &PgConnection,
    table: &DynTable,
    select: S,
    filter: Option<Filter>,
) -> Result<Vec<Record>, diesel::result::Error>
where
    S: 'static
        + Expression<SqlType = Any>
        + SelectableExpression<DynTable>
        + QueryId
        + QueryFragment<Pg>
{
    let mut query = table
        .clone()
        .select(select)
        .into_boxed();

    if let Some(filter) = filter {
        query = filter.apply_filter(query);
    }

    todo!()
}

```

With some help from Gitter, I got to this definition of the `apply_filter()` method:

```auto
fn apply_filter<'f, Q>(&self, query: Q) -> Q
where
    Q: BoxedDsl<'f, DynTable>,
{
    todo!()
}

```

But it seems that this is not working properly, since I’m getting the following error:

```auto
overflow evaluating the requirement `diesel::query_builder::BoxedSelectStatement<'_, diesel_dynamic_schema::dynamic_value::Any, diesel_dynamic_schema::table::Table<std::string::String>, diesel::pg::Pg>: diesel::query_dsl::boxed_dsl::BoxedDsl<'_, diesel_dynamic_schema::table::Table<std::string::String>>`

```

Does anyone have a good approach to implement this?

---

<div class="post-metadata">

**Author:** ![weiznich](https://yyz2.discourse-cdn.com/flex036/user_avatar/discourse.diesel.rs/weiznich/32/64_2.png) [@weiznich](https://discourse.diesel.rs/u/weiznich)\
**Post date:** [January 27, 2020, 9:38pm UTC](https://discourse.diesel.rs/t/dynamic-filtering-of-a-query-in-a-dynamic-schema/134/2 "2020-01-27T21:38:16Z")

</div>

Your `apply_filter` method could look like this:

```auto
fn apply_filter<'f, Q>(
    &self, 
    query: diesel::dsl::IntoBoxed<'f, diesel::dsl::Select<DynTable, S>, diesel::pg::Pg>
) -> diesel::dsl::IntoBoxed<'a, diesel::dsl::Select<DynTable, S>, diesel::pg::Pg>
{
    todo!()
}

```

Alternatively you could create `BoxableExpression<DynTable, diesel::pg::Pg>` from your filter struct and apply that then in your `get_record_list` function.

---

<div class="post-metadata">

**Author:** ![Razican](https://yyz2.discourse-cdn.com/flex036/user_avatar/discourse.diesel.rs/razican/32/67_2.png) [@Razican](https://discourse.diesel.rs/u/Razican)\
**Post date:** [January 28, 2020, 8:19pm UTC](https://discourse.diesel.rs/t/dynamic-filtering-of-a-query-in-a-dynamic-schema/134/3 "2020-01-28T20:19:27Z")

</div>

The idea of returning a ` BoxableExpression` is actually pretty good!

I implemented an `expression()` method:

```auto
fn expression(&self, table: &DynTable) -> Option<DynExpr> {
    use diesel::BoolExpressionMethods;

    let mut or_expr = None::<DynExpr>;
    for and_filter in &self.inner {
        if let Some(new_expr) = Self::and_expression(table, &and_filter) {
            or_expr = Some(if let Some(cur_expr) = or_expr {
                Box::new(cur_expr.or(new_expr))
            } else {
                new_expr
            });
        }
    }

    or_expr
}

fn and_expression(table: &DynTable, list: &[FilterClause]) -> Option<DynExpr> {
    use diesel::BoolExpressionMethods;

    let mut and_expr = None::<DynExpr>;

    for clause in list {
        if let Ok(new_expr) = clause.expression(table) {
            and_expr = Some(if let Some(cur_expr) = and_expr {
                Box::new(cur_expr.and(new_expr))
            } else {
                new_expr
            });
        }
    }

    and_expr
}

```

For that, I implemented an `expression()` method in `FilterClause`, that generates an expression depending on the type of the column. In order to no repeate code, this function will call to `expression_ty()` a typed version of the function:

```auto
fn expression_ty<T, E, V>(&self, table: &DynTable) -> Result<DynExpr, Error>
where
    T: FromStr<Err = E> + diesel::expression::AsExpression<V> + std::fmt::Display,
    E: std::error::Error + Send + Sync + 'static,
    V: diesel::sql_types::SingleValue,
    <T as diesel::expression::AsExpression<V>>::Expression:
        diesel::expression::NonAggregate + QueryFragment<Pg> + SelectableExpression<DynTable>,
{
    
    use diesel::{ExpressionMethods, PgTextExpressionMethods};

    let field = table.column::<V, _>(self.field);

    let res: DynExpr = match o {
        Operator::Contains => Box::new(field.ilike(format!("%{}%", value))),
        Operator::DoesNotContain => {
            Box::new(field.not_ilike(format!("%{}%", value)))
        }
        Operator::StartsWith => Box::new(field.ilike(format!("{}%", value))),
        Operator::EndsWith => Box::new(field.ilike(format!("%{}", value))),
    };

    Ok(res)
}

```

But I’m getting some errors in that last function:

```auto
error[E0599]: no method named `ilike` found for struct `diesel_dynamic_schema::column::Column<diesel_dynamic_schema::table::Table<std::string::String>, std::string::String, V>` in the current scope
   --> api_server/src/db.rs:853:66
    |
853 | ... Operator::Contains => Box::new(field.ilike(format!("%{}%", value))),
    | ^^^^^ method not found in `diesel_dynamic_schema::column::Column<diesel_dynamic_schema::table::Table<std::string::String>, std::string::String, V>`
    |
    = note: the method `ilike` exists but the following trait bounds were not satisfied:
            `&diesel_dynamic_schema::column::Column<diesel_dynamic_schema::table::Table<std::string::String>, std::string::String, V> : diesel::PgTextExpressionMethods`
            `&mut diesel_dynamic_schema::column::Column<diesel_dynamic_schema::table::Table<std::string::String>, std::string::String, V> : diesel::PgTextExpressionMethods`
            `diesel_dynamic_schema::column::Column<diesel_dynamic_schema::table::Table<std::string::String>, std::string::String, V> : diesel::PgTextExpressionMethods`

```

So, it seems that the `table.column()` method is not creating a `PgTextExpressionMethods`. Probably I’m missing some bounds in the header of the function, do you have an idea of what could it be?

Also, do you think this looks performant enough? It seems like it’s boxing a bunch of stuff.

---

<div class="post-metadata">

**Author:** ![weiznich](https://yyz2.discourse-cdn.com/flex036/user_avatar/discourse.diesel.rs/weiznich/32/64_2.png) [@weiznich](https://discourse.diesel.rs/u/weiznich)\
**Post date:** [January 30, 2020, 3:06pm UTC](https://discourse.diesel.rs/t/dynamic-filtering-of-a-query-in-a-dynamic-schema/134/4 "2020-01-30T15:06:48Z")

</div>

> So, it seems that the table.column() method is not creating a PgTextExpressionMethods. Probably I’m missing some bounds in the header of the function, do you have an idea of what could it be?

That makes sense, because you don’t say anywhere that `V` is a type that is some kind of text, which would allow `ilike` to exist. It doesn’t make any sense for `V==Integer` to have a `ilike` method.

I would guess you could just fix that by adding `<T as AsExpression<V>>::Expression: Expression<SqlType = Text>` as bound, but I haven’t tested that. A more generic way would be to have `<<T as Expression<V>>::Expression as Expression>::SqlType: TextOrNullableText`, but that relies on internal diesel api’s.

> Also, do you think this looks performant enough? It seems like it’s boxing a bunch of stuff.

The overhead from boxing and dynamic method calls will probably not be relevant, because a call to the database is some magnitudes more expensive that some allocations and virtual function calls. I would only start thinking about how to optimize that if it shows up in some profiling results.