How to use subquery statements in Entity::find of SeaORM?

[baby195lxl]

Discussed in #2446

^{Originally posted by baby195lxl December 11, 2024}
I want to use Rust's SeaORM to implement the following SQL code: 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.The code is as follows:

#[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);

However, this code fails to produce the expected result. My PostgreSQL table structure is as follows:

----------------------
 id | grade | student
----------------------
 1 | A | tom
----------------------
 2 | A | jerry
----------------------
 3 | B | woody
----------------------
 4 | B | buzz
----------------------
 5 | C | jessie
----------------------
 6 | C | rex
----------------------

The expected result is to obtain a structure like this:{ MathGrade {grade: A, students: [tom, jerry]}, MathGrade {grade: B, students: [woody, buzz]}, MathGrade {grade: C, students: [jessie, rex]} }. However, after running the program, there is no output. The configuration of Cargo.toml is as follows, and the Rust compiler version I am using is rustc 1.82.0 (f6e511eec 2024-10-15). I would appreciate it if someone could help me clear up this confusion, and any reply are welcome. Thanks.

[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" }
```</div>