Dynamic filtering of a query in a dynamic schema

[Razican]

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:

[
    [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():

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:

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:

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?

[weiznich]

Your apply_filter method could look like this:

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.

[Razican]

The idea of returning a BoxableExpression is actually pretty good!

I implemented an expression() method:

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:

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:

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.

[weiznich]

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.