diesel-rust (Postgres) - column type for filtering

Question

I'm trying to provide a generic filter capability to my diesel application. The user would specify a column to filter against, an appropriate value for that column (str, float, int, etc) and then one of the operations:

pub enum FilterOp {
    NONE, 
    EQUAL,
    LESS_THAN,
    LESS_THAN_EQUAL,
    GREATER_THAN, 
    GREATER_THAN_EQUAL, 
    CONTAINS,
    NOT_EQUAL, 
    STRING_LIKE, 
}

So if my table has:

diesel::table! {
    use diesel::sql_types::*;
    use postgis_diesel::sql_types::*;

    entities (uid) {
        uid -> Uuid,
        source -> Text,
        source_id -> Text,
        ...

So for the "source_id" column, I end up with code like:

    let mut query: schema::entities::BoxedQuery<diesel::pg::Pg> = schema::entities::table.into_boxed();

        if "source_id".eq( fitler_column.as_str() )  {
                ...
                match op {
                    FilterOp::EQUAL => {
                        query = query.filter( source_id.eq( filter_value.string_value() ) );
                    },
                    FilterOp::NOT_EQUAL => {
                        query = query.filter( source_id.ne( filter_value.string_value() ) );
                    },
                    FilterOp::LESS_THAN => {
                        query = query.filter( source_id.lt( filter_value.string_value() ) );
                    },

And this works similarly for other column types (a uuid string would be converted to a Uuid instance). But it's a lot of duplicated code (for each column).

So I want to extract out the match, something like: (for the "source" column):

                query_filter(op, 
                             &mut query, 
                             &schema::entities::dsl::source, 
                             filter_value.string_value() );

But I'm struggling to properly define query_filter. It needs to look something like:

fn query_filter<T, V> ( op: FilterOp, 
                        query: &mut schema::entities::BoxedQuery<diesel::pg::Pg>, 
                        column: &T, 
                        filter_value: &V ) 
{
    use crate::schema::entities::dsl::*;

    match op {
        FilterOp::EQUAL => {
            *query = query.filter( column.eq( filter_value ) );
        },
    ...

So this parameterizes the column and it's SQL type (T) and the corresponding native Rust type (V). I'm stumped on what the T and V types should be declared as.

Does this need to be macro instead?

Thanks!

Answer 1

As you post does not really contain a minimal example I've built the following one:

table! {
    users {
        id -> Integer,
        name -> Text,
    }
}

fn apply_filters<C, V>(
    mut query: users::BoxedQuery<diesel::sqlite::Sqlite>,
    column: C,
    value: V,
) -> users::BoxedQuery<diesel::sqlite::Sqlite>
{
    // just use a single method here for now as other methods
    // can be easily added later
    query = query.filter(column.eq(value));
    
    query
}

fn main() {
    let q = users::table.into_boxed();

    let q = apply_filters(q, users::id, 42);
    apply_filters(q, users::name, "Foo");

    println!("Hello, world!");
}

Now before we go to the "solution" first a word of warning: Writing abstract code for diesel is possible, but requires handling a lot of trait bounds. So be sure that you have a good understanding of how rusts trait system works before you start toying around with this kind of code.

Now the first thing that needs to be answered is how to restrict the types C and V. We can look at the diesel documentation here and find that there is a trait called Column, which seems to fit our usecase for C. Otherwise we can also look at ExpressionMethods::eq method and see that it's implemented for all types that implement Expression. Column implies Expression so we are fine there. For V we look again at the eq method and see that the argument is bound on T: AsExpression<Self::SqlType>, This indicates that we need to restrict V on that same trait.

This leaves us with the following starting trait bounds: C: Column and V: AsExpression<C::SqlType>.

If we try that the compiler complains about "the trait diesel::sql_types::SqlType is not implemented for <C as diesel::Expression>::SqlType", so we add the suggested trait bound: C::SqlType: SqlType

If we try that the compiler complains again about "the method eq exists for type parameter C, but its trait bounds were not satisfied". Now it gives the non meaningful suggestion to restrict C to Iterator. The help also contains the following block:

   = note: the following trait bounds were not satisfied:
           `<C as diesel::Expression>::SqlType: SingleValue`
           which is required by `C: diesel::ExpressionMethods`
           `<C as diesel::Expression>::SqlType: SingleValue`
           which is required by `&C: diesel::ExpressionMethods`
           `&mut C: diesel::Expression`
           which is required by `&mut C: diesel::ExpressionMethods`
           `C: Iterator`
           which is required by `&mut C: Iterator`

So lets try that C: SingleValue suggestion instead as that is some diesel trait.

If we try that the compiler complains again about "the trait bound C: ValidGrouping<()> is not satisfied" and suggests to add C: ValidGrouping<()>.

If we try that the compiler complains again about "the trait bound <V as AsExpression<<C as diesel::Expression>::SqlType>>::Expression: ValidGrouping<()> is not satisfied" and suggests that to add that bound as well. Now that can be written more shortly as diesel::dsl::AsExpr<V, C>: ValidGrouping<()>

If we try that the compiler complains again about "the trait bound <C as ValidGrouping<()>>::IsAggregate: MixedAggregates<<<V as AsExpression<<C as diesel::Expression>::SqlType>>::Expression as ValidGrouping<()>>::IsAggregate> is not satisfied" and suggests to add another trait bound. The suggested bound can be written more easily by using <C as ValidGrouping<()>>::IsAggregate:MixedAggregates<<dsl::AsExpr<V, C> as ValidGrouping<()>>::IsAggregate>

If we try that again the compiler complain again about "the trait bound <<C as ValidGrouping<()>>::IsAggregate as MixedAggregates<<<V as AsExpression<<C as diesel::Expression>::SqlType>>::Expression as ValidGrouping<()>>::IsAggregate>>::Output: MixedAggregates<diesel::expression::is_aggregate::No> is not satisfied" and suggests another trait bound. Unfortunately that suggestion is a trap. Instead we need to modify the last bound to <C as ValidGrouping<()>>::IsAggregate:MixedAggregates<<dsl::AsExpr<V, C> as ValidGrouping<()>>::IsAggregate, Output = is_aggregate::No>

If we do that the compiler complains again about "the trait bound <<C as diesel::Expression>::SqlType as diesel::sql_types::SqlType>::IsNull: OneIsNullable<<<C as diesel::Expression>::SqlType as diesel::sql_types::SqlType>::IsNull> is not satisfied" and suggests another long trait bound. This can be simplified by using the bound from the "required for expression::operators::Eq<C, <V as AsExpression<<C as diesel::Expression>::SqlType>>::Expression> to implement diesel::Expression" line instead. This gives us the following bound: dsl::Eq<C, V>: Expression

If we do that the compiler complain again about "the trait bound <expression::grouped::Grouped<expression::operators::Eq<C, <V as AsExpression<<C as diesel::Expression>::SqlType>>::Expression>> as diesel::Expression>::SqlType: BoolOrNullableBool is not satisfied" and suggest another trait bound.

If we try that the compiler complains again about "the trait bound C: AppearsOnTable<users::table> is not satisfied" and suggests to add another trait bound. That bound is somewhat helpful, but it's better to add a bound based on one of the trait bounds in the following note line: "required for expression::operators::Eq<C, <V as AsExpression<<C as diesel::Expression>::SqlType>>::Expression> to implement AppearsOnTable<users::table>". This results in the bound dsl::Eq<C, V>: AppearsOnTable<users::table>

If we try that the compiler complains again about "the trait bound C: QueryFragment<Sqlite> is not satisfied". Again it's better to add the trait bound to dsl::Eq based on the required because … lines.

If we try that the compiler complains again about "C cannot be sent between threads safely". Again it's better to add the trait bound to dsl::Eq

If we try that the compiler complain about "the associated type <V as AsExpression<<C as diesel::Expression>::SqlType>>::Expression may not live long enough" and indicates that there is an hidden lifetime bound in the BoxedQuery type. So we add a named lifetime 'a there and restrict the relevant type to live at least as long as 'a. The same error message is emitted for C so we add the bound there as well.

After adding these bounds the code finally compiles. This gives the following code:

fn apply_filters<'a, C, V>(
    mut query: users::BoxedQuery<'a, diesel::sqlite::Sqlite>,
    column: C,
    value: V,
) -> users::BoxedQuery<diesel::sqlite::Sqlite>
where
    C: Column + ValidGrouping<()> + 'a,
    V: AsExpression<C::SqlType>,
    C::SqlType: SqlType + SingleValue,
    dsl::AsExpr<V, C>: ValidGrouping<()> + 'a,
    <C as ValidGrouping<()>>::IsAggregate: MixedAggregates<
        <dsl::AsExpr<V, C> as ValidGrouping<()>>::IsAggregate,
        Output = is_aggregate::No,
    >,
    dsl::Eq<C, V>:
        Expression + AppearsOnTable<users::table> + QueryFragment<diesel::sqlite::Sqlite> + Send,
    <dsl::Eq<C, V> as Expression>::SqlType: BoolOrNullableBool,
{
    query = query.filter(column.eq(value));

    query
}

The first 5 bounds are general bounds that are required for all operators. The last 2 bounds (both lines containing dsl::Eq) are bounds specific for the operator you use in this function. If you use several operators these lines need to be duplicated and adjusted for the other operators. For example for supporting .ne() the following additional bounds are required:

    dsl::NotEq<C, V>:
        Expression + AppearsOnTable<users::table> + QueryFragment<diesel::sqlite::Sqlite> + Send,
    <dsl::NotEq<C, V> as Expression>::SqlType: BoolOrNullableBool,

The overall message of this is:

• It's possible to write such code
• You usually need some patience and you need to carefully inspect the compiler output to see what to add next
• You usually don't want to write that code in an application as it's quite complex