Rust Diesel,加载从多个表中选择列的sql_query的结果时出错

qyyhg6bp  于 2022-11-24  发布在  其他
关注(0)|答案(1)|浏览(255)

我尝试使用不匹配单个表的Diesel执行sql_query,并收到以下错误:

error[E0277]: the trait bound `Untyped: load_dsl::private::CompatibleType<TimeCountSumaryEntry, _>` is not satisfied
        --> src/api/cra_service.rs:263:10
         |
    263  |         .load::<TimeCountSumaryEntry>(connection)
         |          ^^^^ the trait `load_dsl::private::CompatibleType<TimeCountSumaryEntry, _>` is not implemented for `Untyped`
         |
         = help: the trait `load_dsl::private::CompatibleType<U, DB>` is implemented for `Untyped`
         = note: required because of the requirements on the impl of `LoadQuery<'_, _, TimeCountSumaryEntry>` for `SqlQuery`
    note: required by a bound in `diesel::RunQueryDsl::load

下面是相关的代码(其中的子句被固定以简化实验):

#[derive(QueryableByName, Debug)]
struct TimeCountSumaryEntry {
    #[diesel(sql_type = Integer)]
    month: i32,
    #[diesel(sql_type = Integer)]
    year: i32,
    #[diesel(sql_type = Integer)]
    project_id: i32,
    #[diesel(sql_type = Text)]
    project_code: String,
    #[diesel(sql_type = Double)]
    time_spent: f32,
    #[diesel(sql_type = Text)]
    fullname: String,
}

fn _timecount_by_filters(
    user_id: Option<i32>,
    month: Option<u8>,
    year: Option<u16>,
    connection: &mut PgConnection,
) {
    let query =
        "SELECT
            EXTRACT(MONTH FROM tc.date_assigned) as \"month\",
            EXTRACT(YEAR FROM tc.date_assigned) as \"year\",
            tc.project_id as project_id,
            p.project_code as project_code,
            sum(tc.time_spent) as time_spent,
            u.lastname || ' ' || u.firstname as fullname
        FROM
            time_count tc
            JOIN cra c on tc.cra_id = c.cra_id
            JOIN project p on p.project_id = tc.project_id
            JOIN \"user\" u on u.user_id = c.user_id
        WHERE
            u.user_id = 3
            and EXTRACT(MONTH FROM tc.date_assigned) = 8
            and EXTRACT(YEAR FROM tc.date_assigned) = 2022
        GROUP BY
            tc.project_id, u.lastname, u.firstname, \"month\", \"year\", p.project_code
        ORDER BY
            \"year\", \"month\", u.lastname, u.firstname, tc.project_id";

    let time_counts_sumary = diesel::dsl::sql_query(query)
        .load::<TimeCountSumaryEntry>(connection)
        .expect("Error getting cra ids");
    println!("{:?}", time_counts_sumary);
}

我找不到任何提到如何处理这种用例的资源(或者说这根本不可能)。我首先尝试使用查询构建器,但似乎不可能,所以我认为sql_query是从DB(postgresql)获取这些数据的方法,而不会在过程中获得无用的信息,但也许有一种更好的方法。
有没有人遇到过这个用例或者有什么关于如何处理它的提示?

afdcj2ne

afdcj2ne1#

如果有人有同样的问题,我发现它在哪里。
柴油不能匹配类型,它从sql_query和我在TimeCountSumaryEntry中提到的。
先来这里:

#[diesel(sql_type = Double)]
time_spent: f32,

我输入了错误的Double(应该是f64
第二个EXTRACT()函数在postgresql中返回一个数字。我可以使用the numeric diesel feature,但在我的情况下(年和月),一个整数就足够了。所以我必须在请求中使用CAST()指定类型。如下所示:

CAST(EXTRACT(MONTH FROM tc.date_assigned) as Integer) as "month",
CAST(EXTRACT(YEAR FROM tc.date_assigned) as Integer) as "year",

下面是工作代码:

#[derive(QueryableByName, Debug)]
struct TimeCountSumaryEntry {
    #[diesel(sql_type = Integer)]
    month: i32,
    #[diesel(sql_type = Integer)]
    year: i32,
    #[diesel(sql_type = Integer)]
    project_id: i32,
    #[diesel(sql_type = Text)]
    project_code: String,
    #[diesel(sql_type = Double)]
    time_spent: f64,
    #[diesel(sql_type = Text)]
    fullname: String,
}

fn _timecount_by_filters(
    user_id: Option<i32>,
    month: Option<u8>,
    year: Option<u16>,
    connection: &mut PgConnection,
) {
    let query =
        "SELECT
            CAST(EXTRACT(MONTH FROM tc.date_assigned) as integer) as \"month\",
            CAST(EXTRACT(YEAR FROM tc.date_assigned) as integer) as \"year\",
            tc.project_id as project_id,
            p.project_code as project_code,
            sum(tc.time_spent) as time_spent,
            u.lastname || ' ' || u.firstname as fullname
        FROM
            time_count tc
            JOIN cra c on tc.cra_id = c.cra_id
            JOIN project p on p.project_id = tc.project_id
            JOIN \"user\" u on u.user_id = c.user_id
        WHERE
            u.user_id = 3
            and EXTRACT(MONTH FROM tc.date_assigned) = 8
            and EXTRACT(YEAR FROM tc.date_assigned) = 2022
        GROUP BY
            tc.project_id, u.lastname, u.firstname, \"month\", \"year\", p.project_code
        ORDER BY
            \"year\", \"month\", u.lastname, u.firstname, tc.project_id";

    let time_counts_sumary = diesel::dsl::sql_query(query)
        .load::<TimeCountSumaryEntry>(connection)
        .expect("Error getting cra ids");
    println!("{:?}", time_counts_sumary);
}

相关问题