如何在SeaORM的Entity::find中使用子查询实现指定PostgreSQL查询

我想用Rust的SeaORM实现以下SQL语句：

SELECT grade, ARRAY_AGG(DISTINCT student) 
FROM (SELECT id, grade, student FROM math_class ORDER BY id DESC LIMIT 10000) AS math_class_grade 
GROUP BY grade;

我写的Rust代码如下：

#[derive(Debug, FromQueryResult)]
struct MathGrade {
    garde: String,
    students: Vec<String>,
}


let subquery = Query::select()
        .columns([
            math::Column::Id,
            math::Column::Grade,
            math::Column::Student,
        ])
        .from(math::Entity)
        .order_by(math::Column::Id, Order::Desc)
        .limit(10_000)
        .to_string(sea_orm::sea_query::backend::PostgresQueryBuilder);

    let results = math::Entity::find()
        .select_only()
        .column(math::Column::Grade)
        .expr_as(
            Expr::cust(&format!(
                "ARRAY_AGG(DISTINCT student) FROM ({subquery}) AS math_class_grade"
            )),
            "students",
        )
        .group_by(math::Column::Grade)
        .into_model::<MathGrade>()
        .all(db)
        .await?;
    println!("{:?}", results);

但这段代码无法得到预期结果，运行后没有任何输出。

我的PostgreSQL表结构如下：

预期结果应该是类似这样的结构：

[
    MathGrade { grade: "A", students: ["tom", "jerry"] },
    MathGrade { grade: "B", students: ["woody", "buzz"] },
    MathGrade { grade: "C", students: ["jessie", "rex"] }
]

我的环境配置：

• Rust编译器版本：rustc 1.82.0 (f6e511eec 2024-10-15)
• Cargo.toml：

[package]
name = "pgsql_query_demo"
version = "0.1.0"
edition = "2021"

[workspace]
members = [".", "entity"]

[dependencies]
tokio = { version = "^1.32", features = ["full"] }
sea-orm = { version = "^1.1", features = [ "sqlx-postgres", "runtime-tokio-rustls", "macros" ] }
entity ={ path = "entity" }

问题修复方案 #

1. 修正结构体字段拼写错误

MathGrade结构体中的garde是拼写错误，必须和SQL返回的grade字段名完全一致，否则FromQueryResult无法正确映射结果：

#[derive(Debug, FromQueryResult)]
struct MathGrade {
    grade: String,
    students: Vec<String>,
}

2. 重构查询逻辑

原代码错误地将子查询嵌套在聚合表达式中，正确的做法是直接基于子查询构建主聚合查询，和原始SQL逻辑对齐：

use sea_orm::{Query, Expr, Order, FromQueryResult};

#[derive(Debug, FromQueryResult)]
struct MathGrade {
    grade: String,
    students: Vec<String>,
}

// 构建子查询对象（无需手动转字符串）
let subquery = Query::select()
    .columns([
        math::Column::Id,
        math::Column::Grade,
        math::Column::Student,
    ])
    .from(math::Entity)
    .order_by(math::Column::Id, Order::Desc)
    .limit(10_000)
    .subquery();

// 基于子查询执行聚合查询
let results = Query::select()
    .column(math::Column::Grade)
    .expr(Expr::cust("ARRAY_AGG(DISTINCT student)").alias("students"))
    .from(subquery)
    .group_by(math::Column::Grade)
    .into_model::<MathGrade>()
    .all(db)
    .await?;

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

3. 关键说明

• 使用subquery()方法创建子查询对象，SeaORM会自动处理表别名和语法拼接，避免手动字符串拼接的错误。
• 主查询直接从子查询结果中选择字段并聚合，完全匹配原始SQL的逻辑。
• 确保结构体字段名与查询返回的列名严格一致，是FromQueryResult正确反序列化的前提。

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