使用Rust Diesel执行多表sql_query时加载结果报错求助

使用Rust 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<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

相关代码（WHERE子句已固定以简化测试）：

#[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从PostgreSQL数据库获取目标数据，想请教是否有更优方案，以及如何解决当前报错问题？

一、修复sql_query的加载报错 #

报错核心原因是SQL返回值类型与结构体声明类型不匹配：PostgreSQL中EXTRACT函数返回numeric类型，但结构体中month/year声明为i32；sum(tc.time_spent)的返回类型也与f32存在潜在不兼容。

两种修复方式： #

1. 在SQL中强制转换类型
   给EXTRACT结果加上::INTEGER，确保返回类型与结构体一致，无需修改结构体定义：

   SELECT
       EXTRACT(MONTH FROM tc.date_assigned)::INTEGER as "month",
       EXTRACT(YEAR FROM tc.date_assigned)::INTEGER as "year",
       tc.project_id as project_id,
       p.project_code as project_code,
       sum(tc.time_spent)::DOUBLE PRECISION as time_spent,
       u.lastname || ' ' || u.firstname as fullname
   -- 其余SQL内容不变
   

2. 调整结构体类型并自定义反序列化
   如果不想修改SQL，可以将month/year对应为PostgreSQL的Numeric类型，并实现从Numeric到i32的反序列化：

   use diesel::pg::types::sql_types::{Double, Integer, Text, Numeric};
   use diesel::deserialize::FromSql;
   use diesel::pg::Pg;
   use std::str::FromStr;
   
   impl FromSql<Numeric, Pg> for i32 {
       fn from_sql(bytes: Option<&[u8]>) -> diesel::deserialize::Result<Self> {
           let s = String::from_sql(bytes)?;
           i32::from_str(&s).map_err(|e| diesel::deserialize::Error::Custom(e.to_string()))
       }
   }
   
   #[derive(QueryableByName, Debug)]
   struct TimeCountSumaryEntry {
       #[diesel(sql_type = Numeric)]
       month: i32,
       #[diesel(sql_type = Numeric)]
       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,
   }
   

二、更优方案：使用Diesel查询构建器 #

查询构建器能提供类型安全保障，避免手写SQL的类型匹配问题，以下是对应需求的实现：

前提：确保已生成表的schema文件 #

假设src/schema.rs已包含time_count、cra、project、user表的自动生成定义。

实现代码： #

use diesel::prelude::*;
use diesel::dsl::{sum, sql};
use schema::{time_count, cra, project, user};

#[derive(Queryable, Debug)]
struct TimeCountSumaryEntry {
    month: i32,
    year: i32,
    project_id: i32,
    project_code: String,
    time_spent: f64,
    fullname: String,
}

fn _timecount_by_filters(
    user_id: Option<i32>,
    month: Option<u8>,
    year: Option<u16>,
    connection: &mut PgConnection,
) {
    let target_user_id = user_id.unwrap_or(3);
    let target_month = month.unwrap_or(8) as i32;
    let target_year = year.unwrap_or(2022) as i32;

    let results = time_count::table
        .inner_join(cra::table.on(time_count::cra_id.eq(cra::cra_id)))
        .inner_join(project::table.on(time_count::project_id.eq(project::project_id)))
        .inner_join(user::table.on(cra::user_id.eq(user::user_id)))
        .filter(user::user_id.eq(target_user_id))
        .filter(sql("EXTRACT(MONTH FROM time_count.date_assigned) = ").bind::<Integer, _>(target_month))
        .filter(sql("EXTRACT(YEAR FROM time_count.date_assigned) = ").bind::<Integer, _>(target_year))
        .select((
            sql("EXTRACT(MONTH FROM time_count.date_assigned)::INTEGER").alias("month"),
            sql("EXTRACT(YEAR FROM time_count.date_assigned)::INTEGER").alias("year"),
            time_count::project_id,
            project::project_code,
            sum(time_count::time_spent).alias("time_spent"),
            sql("user.lastname || ' ' || user.firstname").alias("fullname"),
        ))
        .group_by(
            time_count::project_id,
            user::lastname,
            user::firstname,
            sql("month"),
            sql("year"),
            project::project_code,
        )
        .order_by((sql("year"), sql("month"), user::lastname, user::firstname, time_count::project_id))
        .load::<TimeCountSumaryEntry>(connection)
        .expect("Error loading summary");

    println!("{:?}", results);
}

说明： #

• 用inner_join处理多表关联，保持类型安全
• 对EXTRACT这类Diesel未直接支持的操作，用sql函数嵌入原生SQL片段，通过bind绑定参数避免SQL注入
• 聚合函数sum直接使用Diesel提供的内置方法
• 结构体使用Queryable而非QueryableByName，查询构建器会自动匹配字段顺序与类型

内容的提问来源于stack exchange，提问作者Yther