rust 如何根据Diesel的动态参数按列进行有条件排序？

sqyvllje  于 2022-12-19  发布在  其他
关注(0)|答案(2)|浏览(155)

我尝试根据外部参数为order_by指定不同的列。
这是可行的，但很难看：

#[macro_use]
extern crate diesel;

use crate::diesel::prelude::*;
use diesel::pg::PgConnection;

mod schema {
    table! {
        items (id) {
            id -> Int4,
            name -> Text,
        }
    }
}

#[derive(Queryable, Debug)]
pub struct Item {
    pub id: i32,
    pub name: String,
}

fn load_items(conn: PgConnection, sort_prop: String, sort_dir: String) -> Vec<Item> {
    use schema::items::dsl::*;

    let mut query = items.into_boxed();

    // ugly: duplicating condition by sort_dir and query.order_by() calls
    query = match sort_prop.as_str() {
        "name" => {
            if sort_dir == "asc" {
                query.order_by(name.asc())
            } else {
                query.order_by(name.desc())
            }
        }
        _ => {
            if sort_dir == "asc" {
                query.order_by(id.asc())
            } else {
                query.order_by(id.desc())
            }
        }
    };

    query.load::<Item>(&conn).expect("Failed to load items")
}

fn main() {}

我的货物。汤姆有这个：

[dependencies]
diesel = { version = "1.4.3", features = ["postgres"] }

我只想按列而不是整个查询进行条件化，如下所示：

use schema::items::dsl::*;

let mut column = match sort_prop.as_str() {
    "name" => name,
    _ => id // error: match arms have incompatible types
}

column = if sort_dir == "asc" {
    column.asc()
} else {
    column.desc()
}

let results = items
    .order_by(column)
    .load::<Item>(connection)
    .expect("Failed to load items");

这是可能的吗？有没有其他方法来重构它？
我读过Querying a Diesel table with dynamic parameters，但它基本上是关于整个查询的条件化，这是我试图避免的。
我也读过Creating Diesel.rs queries with a dynamic number of .and()'s，它是关于通过过滤器进行条件化的，这可能接近我对order_by的需要，但我很难将BoxableExpression的怪异之处应用到我的案例中，因为文档中缺乏我的确切案例的好例子，也缺乏RLS支持来显示IDE中的任何schema::items::dsl::*类型，所以我可以自己摸索。

rust

2条答案

按热度按时间
6yjfywim

6yjfywim1#

给定以下模式：

table! {
    flights (id) {
        id -> Int4,
        name -> Text,
        country -> Text,
        launch_date -> Timestamptz,
    }
}

和以下包含排序选项的结构：

pub struct SearchFlight {
    pub sort: Option<String>,
    pub sort_dir: Option<String>
}

可以执行以下操作：

let mut query = flights.into_boxed();
match search.sort.as_ref().map(String::as_str)  {
    Some("name") => sort_by_column(query, flights_schema::name, search.sort_dir),
    Some("country") => sort_by_column(query, flights_schema::country, search.sort_dir),
    Some("launch_date") => sort_by_column(query, flights_schema::launch_date, search.sort_dir),
    _ => query
}
query.load_page::<Flight>(con)

其中，如果使用以下函数，则使用sort_by_column

fn sort_by_column<U: 'static>(mut query: BoxedQuery<'static, Pg>,
                     column: U,
                     sort_dir: Option<String>) -> BoxedQuery<'static, Pg>
    where U: ExpressionMethods + QueryFragment<Pg> + AppearsOnTable<flights_schema::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
    }
}
赞(0）分享  回复(0）举报  2022-12-19
63lcw9qa

63lcw9qa2#

我遇到了同样的问题。希望这对任何表/查询都有效：

#[macro_use]
extern crate diesel;

use diesel::table;

table! {
    posts (id) {
        id -> Nullable<Integer>,
    }
}

#[derive(Clone, Copy)]
pub enum QueryOrdering {
    Ascending,
    Descending,
}

/// bring ExtendedQueryDsl from below into scope when building queries
fn example(order: QueryOrdering)
{
    use diesel::mysql::Mysql;

    posts::table.order_dyn::<Mysql, _>(order, posts::id).limit(5);

    posts::table
        .into_boxed::<Mysql>()
        .order_dyn2::<Mysql, _>(order, posts::id)
        .limit(5);
    // This will not work since table.into_boed().into_boxed() is not implemented in diesel even
    // though it could be a simple a no-op
    /*
    posts::table
        .into_boxed()
        .order_dyn::<Mysql,_>(order, posts::id)
        .limit(5);
    */
    order
        .order_query::<_, _, Mysql>(posts::table, posts::id)
        .limit(5);
}
以下是我的解决方案（适用于柴油机1和2）
use diesel::backend::Backend;
use diesel::dsl::{Asc, Desc};
use diesel::helper_types::IntoBoxed;
use diesel::query_dsl::methods::{BoxedDsl, OrderDsl};
use diesel::{ExpressionMethods, QueryDsl};

#[derive(Clone, Copy)]
pub enum QueryOrdering {
    Ascending,
    Descending,
}

impl QueryOrdering {
    fn order_query<'a, Expr, Q, DB>(&self, query: Q, expr: Expr) -> IntoBoxed<'a, Q, DB>
    where
        Expr: ExpressionMethods,
        Q: QueryDsl + BoxedDsl<'a, DB>,
        DB: Backend,
        IntoBoxed<'a, Q, DB>: OrderDsl<Asc<Expr>, Output = IntoBoxed<'a, Q, DB>>
            + OrderDsl<Desc<Expr>, Output = IntoBoxed<'a, Q, DB>>,
    {
        match self {
            Self::Ascending => query.into_boxed().order(expr.asc()),
            Self::Descending => query.into_boxed().order(expr.desc()),
        }
    }
    fn order_query2<'a, Expr, Q, DB>(
        &self,
        query: Q,
        expr: Expr,
    ) -> <Q as OrderDsl<Asc<Expr>>>::Output
    where
        Expr: ExpressionMethods,
        DB: Backend,
        Q: OrderDsl<Asc<Expr>>
            + OrderDsl<Desc<Expr>, Output = <Q as OrderDsl<Asc<Expr>>>::Output>,
    {
        match self {
            Self::Ascending => query.order(expr.asc()),
            Self::Descending => query.order(expr.desc()),
        }
    }
}

pub trait DynOrderDsl2<'a, DB, Expr>
where
    Expr: ExpressionMethods,
    DB: Backend,
    Self: QueryDsl
        + OrderDsl<Asc<Expr>>
        + OrderDsl<Desc<Expr>, Output = <Self as OrderDsl<Asc<Expr>>>::Output>,
{
    fn dyn_order2(
        self,
        order: QueryOrdering,
        expr: Expr,
    ) -> <Self as OrderDsl<Asc<Expr>>>::Output;
}

impl<'a, Expr, DB, Q> DynOrderDsl2<'a, DB, Expr> for Q
where
    Expr: ExpressionMethods,
    DB: Backend,
    Q: QueryDsl
        + OrderDsl<Asc<Expr>>
        + OrderDsl<Desc<Expr>, Output = <Q as OrderDsl<Asc<Expr>>>::Output>,
{
    fn dyn_order2(
        self,
        order: QueryOrdering,
        expr: Expr,
    ) -> <Q as OrderDsl<Asc<Expr>>>::Output {
        order.order_query2::<_, _, DB>(self, expr)
    }
}

pub trait DynOrderDsl<'a, DB, Expr>
where
    Expr: ExpressionMethods,
    Self: QueryDsl + BoxedDsl<'a, DB>,
    DB: Backend,
    IntoBoxed<'a, Self, DB>: OrderDsl<Asc<Expr>, Output = IntoBoxed<'a, Self, DB>>
        + OrderDsl<Desc<Expr>, Output = IntoBoxed<'a, Self, DB>>,
{
    fn dyn_order(self, order: QueryOrdering, expr: Expr) -> IntoBoxed<'a, Self, DB>;
}

impl<'a, Expr, DB, Q> DynOrderDsl<'a, DB, Expr> for Q
where
    Expr: ExpressionMethods,
    Q: QueryDsl + BoxedDsl<'a, DB>,
    DB: Backend,
    IntoBoxed<'a, Q, DB>: OrderDsl<Asc<Expr>, Output = IntoBoxed<'a, Q, DB>>
        + OrderDsl<Desc<Expr>, Output = IntoBoxed<'a, Q, DB>>,
{
    fn dyn_order(self, order: QueryOrdering, expr: Expr) -> IntoBoxed<'a, Q, DB> {
        order.order_query(self, expr)
    }
}

pub trait ExtendedQueryDsl: Sized {
    fn order_dyn<'a, DB, Expr>(self, order: QueryOrdering, expr: Expr) -> IntoBoxed<'a, Self, DB>
    where
        Expr: ExpressionMethods,
        Self: QueryDsl + BoxedDsl<'a, DB>,
        DB: Backend,
        IntoBoxed<'a, Self, DB>: OrderDsl<Asc<Expr>, Output = IntoBoxed<'a, Self, DB>>
            + OrderDsl<Desc<Expr>, Output = IntoBoxed<'a, Self, DB>>,
    {
        DynOrderDsl::<DB, Expr>::dyn_order(self, order, expr)
    }
    fn order_dyn2<DB, Expr>(
        self,
        order: QueryOrdering,
        expr: Expr,
    ) -> <Self as OrderDsl<Asc<Expr>>>::Output
    where
        Expr: ExpressionMethods,
        DB: Backend,
        Self: QueryDsl
            + OrderDsl<Asc<Expr>>
            + OrderDsl<Desc<Expr>, Output = <Self as OrderDsl<Asc<Expr>>>::Output>,
    {
        DynOrderDsl2::<DB, Expr>::dyn_order2(self, order, expr)
    }
}

impl<Q> ExtendedQueryDsl for Q {}
赞(0）分享  回复(0）举报  2022-12-19
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com