What's the correct return type to return `diesel` columns from a function?

Hi everyone,
I'm trying to implement a filtering system to use with diesel. As a first step I want to be able to define an enum which represents the columns that are available for filtering with the possibility to get the corresponding diesel column for each enum variant.

With the following users table in the database...

diesel::table! {
    users (id) {
        id -> Uuid,
        first_name -> Varchar,
        last_name -> Varchar,
        username -> Nullable<Varchar>,
        email -> Varchar,
        tenant_id -> Uuid,
    }
}

... I want to define an enum like follows

enum FilterColumn {
    FirstName,
    LastName,
}

impl FilterColumn {
    fn diesel_column(&self) -> ??? {
        match &self {
            Self::FirstName => users::dsl::first_name,
            Self::FirstName => users::dsl::last_name,
        }
    }
}

I tried to use Box<dyn Column> as return value, but as the Column type requires two generic arguments (Column<SqlType, Table>) I'm unsure if this can work as I need to provide SqlType which is not the same for all columns.

I'm grateful for any help. Thanks in advance!

Diesel represents each column as it's own distinct zero sized type. See the "Schema in Depth" guide for details.

That has the implication that you only can return a trait object as shared type from such a function. The right trait for that is usually BoxableExpression. As you already noticed with your dyn Column approach this will require that you specify the SqlType and the query source (QS) of your column. That's the case because diesel needs this information to type check your query at compile time. The SqlType is required to reject invalid operators for that column, while the query source is required to check whether the relevant table appears in your from clause.

Now it's hard to tell you what to do instead of that as we don't know what exactly you are trying to solve. If you try to build a flexible filter system it's usually a better idea to return the boxed filter expression instead, as that one has always the same SQL type.

3 Likes

Thanks for suggesting BoxableExpression. I guess this might be the solution I'm looking for.

First of all I want to explain what I'm planning to implement:
With a schema like this...

diesel::table! {
    users (id) {
        id -> Uuid,
        first_name -> Varchar,
        last_name -> Varchar,
        username -> Nullable<Varchar>,
        email -> Varchar,
        tenant_id -> Uuid,
    }
}

... the available filters should be defined like this ...

enum UsersFilter {
    FirstName(filter::type::Str),
    LastName(filter::type::Str),
    ...,

    And(Vec<UsersFilter>),
    Or(Vec<UsersFilter>),
}

mod filter {
    mod type {
        /// This enum defines the available filter operations for strings.
        pub enum Str {
            Eq(String),
            Contains(String),
        }
    }
}

With this a filter would look like this:

let filter_1 = UsersFilter::FirstName(filter::type::Str::Eq("John".into()));
let filter_2 = UsersFilter::And(vec![
    UsersFilter::FirstName(filter::type::Str::Eq("John".into())),
    UsersFilter::LastName(filter::type::Contains("oe".into())),
]);

Then there obviously needs to be a function that fetches the users from the database. This function has a parameter of UsersFilter which it should apply to the query.

fn get_users(db: DbConnection, filter: Option<UsersFilter>) -> Vec<User> {
    let users = users::table.filter(create_filters(filter));
    users.load::<User>(db).expect("...")
}

I had a look at the BoxableExpression documentation you linked in your answer which looks promising but raises two questions:

  1. How to apply multiple filters in a row, when the initial one needs to be boxed (and the subsequent ones don't)?
  2. Is it possible to return an empty filter or does this case needs to be handled differently (e.g. having a control flow that does not even call .filter(...))?

How to apply multiple filters in a row, when the initial one needs to be boxed (and the subsequent ones doesn’t)?

You just call .and()/.or() on the left BoxableExpression and pass the right one as argument. If you want to keep the same return type you can box + cast to BoxableExpression again afterwards.

Is it possible to return an empty filter or does this case needs to be handled differently (e.g. having a control flow that does not even call .filter(...))?

There are two solutions for this: Either you do conditional query building (likely requires you to call QueryDsl::into_boxed() on your query as well) or you use a expression that always evaluates to TRUE as placeholder for empty filters.

1 Like

Thanks again for your help! I really appreciate it :slight_smile:

With that I came up with a first working draft of a solution:

pub enum UserFilter {
    Id(filter_type::Uuid),
    FirstName(filter_type::Str),
    LastName(filter_type::Str),
    And(Vec<UserFilter>),
}

pub mod filter_type {
    pub enum Uuid {
        Eq(uuid::Uuid),
    }

    pub enum Str {
        Eq(String),
        Contains(String),
    }
}

pub fn build_filter_expression(
    filter: UserFilter,
) -> Box<
    dyn BoxableExpression<
        users::table,
        diesel::pg::Pg,
        SqlType = diesel::sql_types::Bool,
    >,
> {
    match filter {
        UserFilter::Id(filter_type) => match filter_type {
            filter_type::Uuid::Eq(uuid) => Box::new(users::props::id.eq(uuid)),
        },

        UserFilter::FirstName(filter_type) => match filter_type {
            filter_type::Str::Eq(val) => {
                Box::new(users::props::first_name.eq(val))
            }
            filter_type::Str::Contains(val) => {
                Box::new(users::props::first_name.ilike(format!("%{val}%")))
            }
        },

        UserFilter::LastName(filter_type) => match filter_type {
            filter_type::Str::Eq(val) => {
                Box::new(users::props::last_name.eq(val))
            }
            filter_type::Str::Contains(val) => {
                Box::new(users::props::last_name.ilike(format!("%{val}%")))
            }
        },

        UserFilter::And(filters) => {
            let mut boxed_expressions: Vec<_> = filters
                .into_iter()
                .map(|f| build_filter_expression(f))
                .collect();

            let mut x = boxed_expressions.pop().unwrap();

            for y in boxed_expressions {
                x = Box::new(x.and(y));
            }

            x
        }
    }
}

The first thing I'm not quite sure about is how the "and" case is handled because I'm "re-boxing" the expression in the for-loop. Is there a more elegant way to solve this?

The second thing is, that I'd like to generate this code automatically with macros based on the definition of the UserFilter enum. But as every filter (like UserFilter:Id(filter_type)) matches over the filter_type, I'd need to be able to know all variants of the filter_type enum when generating the code and I don't think this is possible. Does anyone have an idea how to solve this problem?

1 Like

That's the correct solution. How would you expect to solve that differently given that each expression has a different type?

The second thing is, that I'd like to generate this code automatically with macros based on the definition of the UserFilter enum. But as every filter (like UserFilter:Id(filter_type)) matches over the filter_type, I'd need to be able to know all variants of the filter_type enum when generating the code and I don't think this is possible. Does anyone have an idea how to solve this problem?

You likely want to use a derive macro for this. Each of the filter types could implement a trait exposing a generic function on it's own that allows you to apply the filter to a column. Then you would just need to call that generic function for each column.

1 Like

I knew that with every expression having a different type there's probably not much else I could do. But I still wanted to clarify because I've never had to use Box that much and I didn't want to make it too inefficient. Thanks for making it clear!

That's a pretty good idea. I assume the generic type parameter of that trait function is for allowing columns of different types to be passed as a parameter, right?


I thought about this problem a lot over the weekend and came up with a solution which only uses declarative macros. Here's my thinking behind the solution:
The build_filter_expression function needs to match over every filter type (e.g. filter_type::Str) to apply its operations (like Str::Eq or Str::Contains) correctly. The information about the variant each filter type has is only available when the filter_type::Str enum is defined. Therefore I wrote a macro which takes the enum definition and generates another macro that simply contains the match clause.

With yet another macro used to define the actual filters (like the UserFilter in the code above) and knowing the names of the macros containing the match-clauses for the filter types, I was able to generate the build_filter_expression automatically.

Code where all the macros are defined and the filter types are generated
// Re-Exports //////////////////////////////////////////////////////////////////
pub mod prelude {
    pub use super::types::*;
}

// Operations //////////////////////////////////////////////////////////////////

/// Module containing all the possible filter operations
pub mod operation {
    /// A macro that applies diesel's `.eq(...)` operation to a column.
    macro_rules! eq {
        ($db_column:expr, $value:ident) => {
            $db_column.eq($value)
        };
    }

    pub(crate) use eq;

    /// A macro that applies diesel's `.ilike(format!("%{}%"), ...)` operation
    /// to a column.
    /// Attention: This operation is NOT case sensitive.
    macro_rules! contains_i {
        ($db_column:expr, $value:ident) => {
            $db_column.ilike(format!("%{}%", $value))
        };
    }

    pub(crate) use contains_i;
}

// Types ///////////////////////////////////////////////////////////////////////

/// Module containing all the possible types for filtering.
pub mod types {
    /// Macro for defining a filter.
    macro_rules! filter_type {
        (
            $vis:vis enum $name:ident
                macro(name: $macro_name:ident)
            {
                $(
                    // Instead of using $operation:path we have to use
                    // $($operation:tt)+ path, because the operation is a macro
                    // which has to be suffixed with "!". For some reason
                    // calling a macro passed as "path" is not possible.
                    $variant:ident ($($type:tt)+) [$($operation:tt)+],
                )+
            }
        ) => {
            #[derive(async_graphql::OneofObject, Debug, Clone, Hash)]
            $vis enum $name {
                $(
                    $variant ($($type)+),
                )*
            }

            macro_rules! $macro_name {
                (
                    // Identifier of the variable of type $name (the enum).
                    $filter:ident,

                    // Diesel column (e.g. users::first_name)
                    $column:path
                ) => {
                    // Match over the filter variants
                    match $filter {
                        // Implement match arms for each variant
                        $(
                            $name::$variant(val) => {
                                // $($operation)+! calls the provided macro in
                                // the enum definition (in the square brackets)
                                Box::new($($operation)+ ! ($column, val))
                            }
                        )*
                    }
                };
            }

            // Export the macro
            pub(crate) use $macro_name;
        };
    }

    // Filter Definitions //////////////////////////////////////////////////////

    filter_type!(
        pub enum StrFilter
            macro (name: match_str_filter)
        {
            Eq(std::string::String)         [crate::db::filters::operation::eq],
            ContainsI(std::string::String)  [crate::db::filters::operation::contains_i],
        }
    );

    filter_type!(
        pub enum UuidFilter
            macro(name: match_uuid_filter)
        {
            Eq(uuid::Uuid)                  [crate::db::filters::operation::eq],
        }
    );
}

// Filters /////////////////////////////////////////////////////////////////////
#[macro_export]
macro_rules! filter {
    (
        $(#[$attr:meta])*
        $vis:vis enum $name:ident
            diesel(table: $table:ty, db: $db:ty)
            func($fn_vis:vis $fn_name:ident)
        {
            $(
                $variant:ident ($($type:tt)+) [$matcher:ident][$column:path],
            )+
        }
    ) => {
        #[derive(async_graphql::OneofObject)]
        $(#[$attr])*
        $vis enum $name {
            $(
                $variant($($type)+),
            )+

            And(Vec<$name>),
            Or(Vec<$name>),
        }

        $fn_vis fn $fn_name(filter: $name) -> Box<
            dyn diesel::expression::BoxableExpression<
                $table,
                $db,
                SqlType = diesel::sql_types::Bool,
            >
        > {
            match filter {
                $(
                    $name :: $variant (x) => $matcher!(x, $column),
                )*

                $name :: And (filters) => {
                    let mut boxed_expressions: Vec<_> = filters
                        .into_iter()
                        .map(|f| $fn_name(f))
                        .collect();

                    // TODO: How to handle empty "filters" array
                    let mut x = boxed_expressions.pop().unwrap();

                    for y in boxed_expressions {
                        x = Box::new(x.and(y));
                    }

                    x
                },

                $name :: Or (filters) => {
                    let mut boxed_expressions: Vec<_> = filters
                        .into_iter()
                        .map(|f| $fn_name(f))
                        .collect();

                    // TODO: How to handle empty "filters" array
                    let mut x = boxed_expressions.pop().unwrap();

                    for y in boxed_expressions {
                        x = Box::new(x.or(y));
                    }

                    x
                }
            }
        }
    };
}

////////////////////////////////////////////////////////////////////////////////

This is how the usage of the main macro looks like in the end:

use super::filters::prelude::*;

crate::filter!(
    pub enum UserFilter
        diesel  (table: users::table, db: diesel::pg::Pg)
        func    (user_filter_expr)
    {
        Id(UuidFilter)         [match_uuid_filter] [users::props::id],
        FirstName(StrFilter)   [match_str_filter]  [users::props::first_name],
        LastName(StrFilter)    [match_str_filter]  [users::props::last_name],
    }
);

/// Get all users from the database (filtered).
pub async fn get_all_filtered(
    db: &DbConnPool,
    filter: Option<UserFilter>,
) -> Vec<User> {
    // Get connection
    let mut conn = db.get().await.expect("db connection");

    // Generate filter by calling the function generated by the `filter!` macro
    let diesel_filter = filter.map(|f| user_filter_expr(f));

    // Generate query
    match diesel_filter {
        Some(df) => users::table
            .filter(df)
            .load::<User>(&mut conn)
            .await
            .expect("get_all_filtered (filtered case)"),

        None => users::table
            .load::<User>(&mut conn)
            .await
            .expect("get_all_filtered (unfiltered case)"),
    }
}


Thanks a lot @weiznich for helping me out again. I really appreciate your help! As your first post basically contained the answer to my question I marked it as the solution.

Of course I'd still be interested in what you or someone else think about my solution with declarative macros and if it has any downsides compared to the proposed derive macro solution?