如何在SeaORM中编写正确的SELECT子查询？附报错代码示例

需求SQL #

需要执行的目标SQL语句如下：

select wallets.*, users.name as name, 
    (select max(regist_at) from payments 
        where payment_item_id = 2 and receiver_id = 1) as newest_login_at
from wallets 
inner join users on wallets.user_id = users.user_id 
where wallets.user_id = 1;

尝试的代码（存在编译错误） #

#[derive(Debug, FromQueryResult, Serialize, Deserialize)]
pub struct WalletSummary {
    pub user_id: i64,
    pub name: String,
    pub amount: i64,
    pub regist_at: DateTimeWithTimeZone,
    pub newest_login_at: Option<DateTimeWithTimeZone>,
}

let user_id: i64 = 2;
let wallet_summary = Wallets::find_by_id(user_id)
    .column(users::Column::Name)
    .column_as(
        Payments::find()
            .column(payments::Column::RegistAt.max())
            .filter(payments::Column::PaymentItemId.eq(1))
            .filter(payments::Column::ReceiverId.eq(user_id)),
        "newest_login_at"
    )
    .join(JoinType::InnerJoin, wallets::Relation::Users.def())
    .into_model::<WalletSummary>()
    .one(db)
    .await?

错误原因与修复方案 #

编译错误核心原因：column_as方法要求接收表达式类型参数，直接传入Payments::find()构建的完整查询会导致类型不匹配。需将子查询转换为SeaORM可识别的表达式，同时修正代码与原SQL不符的参数。

正确实现代码 #

#[derive(Debug, FromQueryResult, Serialize, Deserialize)]
pub struct WalletSummary {
    pub user_id: i64,
    pub name: String,
    pub amount: i64,
    pub regist_at: DateTimeWithTimeZone,
    pub newest_login_at: Option<DateTimeWithTimeZone>,
}

let user_id: i64 = 2;
// 将子查询转换为符合要求的表达式
let newest_login_subquery = Payments::find()
    .column(payments::Column::RegistAt.max())
    .filter(payments::Column::PaymentItemId.eq(2)) // 修正为原SQL指定的2
    .filter(payments::Column::ReceiverId.eq(user_id))
    .into_subquery();

let wallet_summary = Wallets::find()
    .filter(wallets::Column::UserId.eq(user_id)) // 明确过滤条件，避免find_by_id的主键歧义
    .column(users::Column::Name)
    .column_as(newest_login_subquery, "newest_login_at")
    .join(JoinType::InnerJoin, wallets::Relation::Users.def())
    .into_model::<WalletSummary>()
    .one(db)
    .await?;

关键修正点 #

1. 用into_subquery()将Payments查询转换为子查询表达式，适配column_as的参数类型要求。
2. 修正payment_item_id的过滤值为原SQL中的2，保证业务逻辑一致。
3. 若Wallets表主键不是user_id，必须用filter(wallets::Column::UserId.eq(user_id))替代find_by_id，避免逻辑错误。

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