如何在Rust中使用SQLX从关联表查询嵌套结构体？

方案一：利用FromRow的字段前缀映射 #

Sqlx的FromRow支持通过属性指定字段的列前缀，可直接映射嵌套结构体，无需额外辅助结构体。

修改结构体定义 #

给PublicEvent的owner字段添加#[from_row(prefix = "owner_")]属性，让Sqlx自动匹配带前缀的查询列：

use sqlx::{FromRow, Postgres};
use uuid::Uuid;
use serde::Serialize;

#[derive(Debug, Serialize, FromRow)]
struct PublicUser {
    id: Uuid,
    username: String,
}

#[derive(Debug, Serialize, FromRow)]
struct PublicEvent {
    id: Uuid,
    name: String,
    #[from_row(prefix = "owner_")] // 指定嵌套字段的列前缀
    owner: PublicUser,
}

调整查询语句 #

查询时给User表的所有字段加上owner_前缀，与结构体属性对应：

use sqlx::{PgPool, query_as};
use anyhow::Result;

pub async fn list_events(pool: &PgPool) -> Result<Vec<PublicEvent>> {
    Ok(query_as(r#"
        SELECT 
            "Event".id, "Event".name,
            "User".id AS owner_id, "User".username AS owner_username
        FROM "Event"
        JOIN "User" ON "Event".owner_id = "User".id
    "#)
    .fetch_all(pool)
    .await?)
}

方案二：手动实现FromRow trait #

如果需要更灵活的映射逻辑，可手动为PublicEvent实现FromRow，直接从PgRow中提取嵌套的PublicUser：

use sqlx::{FromRow, PgRow, Decode, Error as SqlxError};
use uuid::Uuid;
use serde::Serialize;

#[derive(Debug, Serialize, FromRow)]
struct PublicUser {
    id: Uuid,
    username: String,
}

#[derive(Debug, Serialize)]
struct PublicEvent {
    id: Uuid,
    name: String,
    owner: PublicUser,
}

impl<'r> FromRow<'r, PgRow> for PublicEvent {
    fn from_row(row: &'r PgRow) -> Result<Self, SqlxError> {
        Ok(Self {
            id: row.try_get("id")?,
            name: row.try_get("name")?,
            owner: PublicUser {
                id: row.try_get("user_id")?, // 对应查询中的字段别名
                username: row.try_get("username")?,
            },
        })
    }
}

// 对应手动实现的查询语句
pub async fn list_events(pool: &PgPool) -> Result<Vec<PublicEvent>, anyhow::Error> {
    Ok(query_as(r#"
        SELECT 
            "Event".id, "Event".name,
            "User".id AS user_id, "User".username
        FROM "Event"
        JOIN "User" ON "Event".owner_id = "User".id
    "#)
    .fetch_all(pool)
    .await?)
}

原代码报错原因 #

原代码中"User" as "owner: PublicUser"的写法无效，Sqlx无法将整行数据直接解码为自定义的PublicUser结构体——Decode trait没有实现这种批量解码逻辑，必须明确映射每个字段或使用前缀匹配规则。

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