2个左连接,分组去重

sql语句

SELECT u.id,
       u.avatar,
       u.nick_name,
       u.username,
       GROUP_CONCAT(ur.role_id) AS role_ids,
       GROUP_CONCAT(ro.name) AS role_names
FROM users u
         LEFT JOIN user_role ur ON u.id = ur.user_id
         LEFT JOIN role ro ON ro.id = ur.role_id
GROUP BY u.id;

查询结果

rust写法

pub async fn find_user_list() -> AppResult<Vec<UserResponse>> {
    let db = get_db();
    let users = Users::find()
        .join_as(JoinType::LeftJoin,users::Relation::UserRole.def(),Alias::new("user_role"))
        .join_as(JoinType::LeftJoin,user_role::Relation::Role.def(),Alias::new("role"))
        .select_only()
        .column(users::Column::Id)
        .column(users::Column::Username)
        .column(users::Column::Avatar)
        .column(users::Column::LastLogin)
        .column(users::Column::NickName)
        .expr_as(Expr::cust("GROUP_CONCAT(user_role.role_id)"),"role_ids")
        .expr_as(Expr::cust("GROUP_CONCAT(role.name)"),"role_names")
        .group_by(users::Column::Id)
        .into_model::<UserResponse>()
        .all(db).await?;
    Ok(users)
}

测试

左连接查询

原生 sql

select * from permission per left join permission_role pr on pr.role_id == 1 group by per.id

sea-orm

///查找角色对应的权限列表
pub async fn find_permission_list_by_role_id(role_id: i32) -> AppResult<Vec<PermissionResponse>> {
    let db = get_db();
    let p = permission::Entity::find()
        .join(JoinType::LeftJoin, permission::Relation::PermissionRole.def())
        .group_by(permission::Column::Id)
        .all(db).await?;
    Ok(p.into_iter().map(|p| p.into()).collect())
}