使用Diesel的sql_query能否将结果反序列化为嵌套结构体？

我必须使用sql_query()，但想知道能否将数据库结果序列化为嵌套struct，避免创建扁平数据类型，希望用类似#[serde(flatten)]反序列化JSON的嵌套结构体定义。

我尝试了以下代码：

use diesel::*;
use serde::Deserialize;
use diesel::r2d2::{ConnectionManager, PooledConnection};
type DB = diesel::pg::Pg;
type DbConn = PooledConnection<ConnectionManager<PgConnection>>;

diesel::table! {
    foo (id) {
        id -> Int4,
    }
}

diesel::table! {
    bar (id) {
        id -> Int4,
        foo_id -> Int4,
    }
}
              
#[derive(Deserialize, Queryable)]
#[diesel(table_name=foo)]
pub struct Foo {
    pub id: i32,
}

#[derive(Deserialize, Queryable, QueryableByName)]
pub struct Nested {
    #[sql_type = "Integer"]
    pub bar_id: i32,
    pub foo: Foo
}

pub fn get_nested(conn: & mut DbConn) {
    sql_query("select * from bar join foo on foo.id = bar.foo_id")
        .get_results::<Nested>(conn);
}

但出现如下报错：

error: Cannot determine the SQL type of foo
  --> src/main.rs:30:9
   |
30 |     pub foo: Foo
   |         ^^^
   |
   = help: Your struct must either be annotated with `#[diesel(table_name = foo)]` or have this field annotated with `#[diesel(sql_type = ...)]`

error[E0277]: the trait bound `Untyped: load_dsl::private::CompatibleType<Nested, Pg>` is not satisfied
    --> src/main.rs:35:32
     |
35   |         .get_results::<Nested>(conn);
     |          -----------           ^^^^ the trait `load_dsl::private::CompatibleType<Nested, Pg>` is not implemented for `Untyped`
     |          |
     |          required by a bound introduced by this call
     |
     = help: the trait `load_dsl::private::CompatibleType<U, DB>` is implemented for `Untyped`
     = note: required for `SqlQuery` to implement `LoadQuery<'_, _, Nested>`
note: required by a bound in `get_results`
    --> /home/rogers/.cargo/registry/src/github.com-1ecc6299db9ec823/diesel-2.0.3/src/query_dsl/mod.rs:1695:15
     |
1695 |         Self: LoadQuery<'query, Conn, U>,
     |               ^^^^^^^^^^^^^^^^^^^^^^^^^^ required by this bound in `RunQueryDsl::get_results`

For more information about this error, try `rustc --explain E0277`.
error: could not compile `rust_test` due to 2 previous errors
[Finished running. Exit status: 101]

请问在Diesel中执行原生SQL查询时，是否有办法将数据库值序列化为嵌套结构体？

Diesel 本身不支持直接从原生sql_query()结果自动映射到嵌套结构体，但可以通过以下两种方法实现需求：

方法1：手动映射查询结果 #

先将查询结果加载到一个扁平结构体中，再手动转换为嵌套结构：

use diesel::*;
use serde::Deserialize;
use diesel::r2d2::{ConnectionManager, PooledConnection};
type DB = diesel::pg::Pg;
type DbConn = PooledConnection<ConnectionManager<PgConnection>>;

diesel::table! {
    foo (id) {
        id -> Int4,
    }
}

diesel::table! {
    bar (id) {
        id -> Int4,
        foo_id -> Int4,
    }
}
              
#[derive(Deserialize, Queryable)]
#[diesel(table_name=foo)]
pub struct Foo {
    pub id: i32,
}

// 定义扁平结构体接收查询结果
#[derive(QueryableByName)]
pub struct FlatResult {
    #[sql_type = "Integer"]
    pub id: i32, // bar表的id
    #[sql_type = "Integer"]
    pub foo_id: i32,
    #[sql_type = "Integer"]
    pub foo_id_: i32, // foo表的id，加别名避免字段冲突
}

#[derive(Debug)]
pub struct Nested {
    pub bar_id: i32,
    pub foo: Foo
}

pub fn get_nested(conn: &mut DbConn) -> QueryResult<Vec<Nested>> {
    // 修改SQL，给foo的id加别名
    let flat_results = sql_query("select bar.id, bar.foo_id, foo.id as foo_id_ from bar join foo on foo.id = bar.foo_id")
        .get_results::<FlatResult>(conn)?;
    
    // 手动转换为嵌套结构
    Ok(flat_results.into_iter().map(|fr| Nested {
        bar_id: fr.id,
        foo: Foo { id: fr.foo_id_ }
    }).collect())
}

方法2：实现自定义FromSqlRow trait #

如果需要更灵活的自动映射，可以为嵌套结构体实现FromSqlRow trait，手动处理字段映射逻辑：

use diesel::deserialize::{self, FromSqlRow};
use diesel::pg::Pg;
use diesel::sql_types::{Integer, SqlType};

#[derive(Debug)]
pub struct Nested {
    pub bar_id: i32,
    pub foo: Foo
}

// 为Nested实现FromSqlRow
impl FromSqlRow<Pg> for Nested {
    fn build_from_row<'a>(row: &impl deserialize::Row<'a, Pg>) -> deserialize::Result<Self> {
        // 按查询结果的字段顺序提取值
        let bar_id = row.get(0)?;
        let foo_id = row.get(2)?; // 对应SQL中foo.id的位置
        Ok(Nested {
            bar_id,
            foo: Foo { id: foo_id }
        })
    }

    fn row_metadata() -> &'static [diesel::deserialize::ColumnMetadata] {
        // 声明列元数据，数量和顺序需与SQL查询结果一致
        &[
            diesel::deserialize::ColumnMetadata::new("id", &Integer::default()),
            diesel::deserialize::ColumnMetadata::new("foo_id", &Integer::default()),
            diesel::deserialize::ColumnMetadata::new("id", &Integer::default()),
        ]
    }
}

// 之后可直接调用get_results
pub fn get_nested(conn: &mut DbConn) -> QueryResult<Vec<Nested>> {
    sql_query("select bar.id, bar.foo_id, foo.id from bar join foo on foo.id = bar.foo_id")
        .get_results::<Nested>(conn)
}

注意事项 #

• 原生SQL查询的字段顺序和数量必须与映射逻辑严格对应
• 避免字段名重复，必要时给SQL中的字段加别名
• Diesel的Queryable和QueryableByName仅支持扁平结构体，无法直接处理嵌套类型

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