Dynamic sorting/ordering query from string

[perseoGI]

version = "1.4.5"
features = ["postgres", "r2d2", "chrono"]

Hi there, I'm trying to find a way to "ORDER BY" from a string (including ordering direction)

This Stack Overflow answer is a good approach but it is not generalized.
I'm new in Rust and I cannot find a way to make a template for this:

#[derive(Debug)]
pub struct SortQuery {
    pub sort: Option<String>,
    pub sort_dir: Option<String>,
}

// utility to get a SortQuery from Rocket API 
// query_sort format: ["<field_to_sort>", "<ASC | DESC>"]
pub fn to_sort_query(query_sort: Option<String>) -> SortQuery {
    if let Some(raw_sort) = query_sort {
        let sort_arr: [String; 2] = serde_json::from_str(&raw_sort).unwrap();
        return SortQuery {
            sort: Some(sort_arr[0].to_owned()),
            sort_dir: Some(sort_arr[1].to_owned()),
        };
    }

    SortQuery {
        sort: None,
        sort_dir: None,
    }
}

// Don't know how to generalize
fn sort_by_column<U: 'static>(
    mut query: crate::schema::users::BoxedQuery<'static, Pg>,
    column: U,
    sort_dir: Option<String>,
) -> crate::schema::users::BoxedQuery<'static, Pg>
where
    U: ExpressionMethods + QueryFragment<Pg> + AppearsOnTable<users>,
{
    match sort_dir.as_ref().map(String::as_str) {
        Some("ASC") => query.order_by(column.asc()),
        Some("DESC") => query.order_by(column.desc()),
        _ => query,
    }
}


pub fn get_all(
    query_range: Option<String>,
    query_sort: Option<String>,
    conn: &PgConnection,
) -> QueryResult<(Vec<UserDTO>, i64)> {

    let mut query = users.into_boxed();
    let sort_query = to_sort_query(query_sort);
    query = match sort_query.sort.as_ref().map(String::as_str) {
        Some("first_name") => sort_by_column(query, first_name, sort_query.sort_dir),
        Some("last_name") => sort_by_column(query, last_name, sort_query.sort_dir),
        Some("is_admin") => sort_by_column(query, is_admin, sort_query.sort_dir),
        _ => query,
    };

   query.get_results::<UserDTO>(conn)
}

This is the best approach I found but it will be nice to generalize sort_by_column function to work for every table defined in schema.

Maybe something like

fn sort_by_column<U: 'static, Table>(
    mut query: Table::BoxedQuery<'static, Pg>,
    column: U,
    sort_dir: Option<String>,
) -> Table::BoxedQuery<'static, Pg>
where
    Table: /* help */,
    U: ExpressionMethods + QueryFragment<Pg> + AppearsOnTable<Table>,
{
    match sort_dir.as_ref().map(String::as_str) {
        Some("ASC") => query.order_by(column.asc()),
        Some("DESC") => query.order_by(column.desc()),
        _ => query,
    }
}

Don't know if this is possible.

Thanks in advance :)

[weiznich]

The following function is a generalized variant of your sort_by_column function.

fn sort_by_column<'a, U, T>(
    mut query: diesel::dsl::IntoBoxed<'static, T, Pg>,
    column: U,
    sort_dir: Option<String>,
) -> diesel::dsl::IntoBoxed<'static, T, Pg>
where
    T: BoxedDsl<'static, Pg>,
    diesel::dsl::IntoBoxed<'static, T, Pg>: OrderDsl<Box<dyn BoxableExpression<T, Pg, SqlType = ()>>, Output = diesel::dsl::IntoBoxed<'static, T, Pg>> + QueryDsl,
    U: ExpressionMethods + QueryFragment<Pg> + AppearsOnTable<T> + SelectableExpression<T> + NonAggregate + 'static,
{
    match sort_dir.as_ref().map(String::as_str) {
        Some("ASC") => query.order_by(Box::new(column.asc())),
        Some("DESC") => query.order_by(Box::new(column.desc())),
        _ => query
    }
}

You need to explicitly specify the table type while calling this function via sort_by_column::<_, users::table>(…) or something similar.

The main changes are:

• Using diesel::dsl::IntoBoxed as input and return type, as this is the return type of QueryDsl::into_boxed
• Add any required trait bounds. Those are mostly suggested by rustc
• Assert that OrderDsl generates the same output type for this input (The Output = … part in the trait bounds)
• Erase the type of the order expression by using BoxableExpression

[yasamoka]

@perseoGI, I just ran into a similar problem and solved it with the required trait bounds. Do you still have this issue by any chance?

[perseoGI]

Its been a long time since this issue, could you share your solution?
I didn't find a good approach to solve the problem.

[yasamoka]

Can you share an example of your most recent attempt so I can try to add the trait bounds to that?

[weiznich]

@perseoGI I suggest that you open up a new discussion for this that contains your actual question including the code that you have already written. I did provide a working answer to the original question above. If you the later change your question please do not expect me to also change the answer.

As a more general note: I generally do not give much support for questions about generic code these days it really wouldn't help you much in the most cases to have this code without understanding it.