Alias bug in the SQL statement generated by the find_also_linked function

Description

When using find_also_linked, it was found that the alias reference error in SQL caused PostgreSQL database to directly report an error of invalid SQL.

thread 'actix-server worker 0' panicked at app/src/biz/system/user.rs:55:10:
called `Result::unwrap()` on an `Err` value: Query(SqlxError(Database(PgDatabaseError { severity: Error, code: "42P01", message: "invalid reference to FROM-clause entry for table \"sys_role_permission\"", detail: None, hint: Some("Perhaps you meant to reference the table alias \"r2\"."), position: Some(Original(316)), where: None, schema: None, table: None, column: None, data_type: None, constraint: None, file: Some("parse_relation.c"), line: Some(3597), routine: Some("errorMissingRTE") })))

Steps to Reproduce

1. Prepare table structure.

/// The menu table
#[derive(Clone, Debug, PartialEq, DeriveEntityModel, Eq, Serialize, Deserialize)]
#[sea_orm(table_name = "sys_menu")]
#[serde(rename_all = "camelCase")]
pub struct Model {
    #[sea_orm(primary_key, auto_increment = false)]
    pub id: String,
    pub created_at: DateTime,
    pub updated_at: DateTime,
    pub created_by: String,
    pub updated_by: String,
    pub pid: String,
    pub r#type: String,
    pub name: String,
    pub route: Option<String>,
    pub hidden: bool,
    pub identifier: Option<String>,
    #[sea_orm(column_type = "JsonBinary", nullable)]
    pub meta: Option<Json>,
    pub status: String,
    pub seq: i32,
    pub icon: Option<String>,
    pub affix: bool,
}

impl Related<super::sys_role::Entity> for Entity {
    fn to() -> RelationDef {
        super::sys_role_permission::Relation::Role.def()
    }

    fn via() -> Option<RelationDef> {
        Some(
            super::sys_role_permission::Relation::Menu.def().rev()
        )
    }
}

#[derive(DerivePartialModel, FromQueryResult, Debug)]
#[sea_orm(entity = "Entity")]
pub struct MenuIdentifier {
    identifier: String
}

/// The role table
#[derive(Clone, Debug, PartialEq, DeriveEntityModel, Eq, Serialize, Deserialize)]
#[sea_orm(table_name = "sys_role")]
#[serde(rename_all = "camelCase")]
pub struct Model {
    #[sea_orm(primary_key, auto_increment = false)]
    pub id: String,
    pub created_at: DateTime,
    pub updated_at: DateTime,
    pub created_by: String,
    pub updated_by: String,
    pub name: String,
    pub description: Option<String>,
    pub status: String,
}
impl Related<super::sys_menu::Entity> for Entity {
    fn to() -> RelationDef {
        super::sys_role_permission::Relation::Menu.def()
    }

    fn via() -> Option<RelationDef> {
        Some(
            super::sys_role_permission::Relation::Role.def().rev()
        )
    }
}

impl Related<super::sys_user::Entity> for Entity {
    fn to() -> RelationDef {
        super::sys_user_role::Relation::User.def()
    }

    fn via() -> Option<RelationDef> {
        Some(super::sys_user_role::Relation::Role.def().rev())
    }
}

/// The role_permission table, the relation table for menu and role
#[derive(Clone, Debug, PartialEq, DeriveEntityModel, Eq, Serialize, Deserialize)]
#[sea_orm(table_name = "sys_role_permission")]
#[serde(rename_all = "camelCase")]
pub struct Model {
    #[sea_orm(primary_key, auto_increment = false)]
    pub role_id: String,
    #[sea_orm(primary_key, auto_increment = false)]
    pub r#type: String,
    #[sea_orm(primary_key, auto_increment = false)]
    pub permission_id: String,
}
#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
pub enum Relation {
    #[sea_orm(
        belongs_to = "super::sys_menu::Entity",
        from = "Column::PermissionId",
        to = "super::sys_menu::Column::Id",
        on_condition = r#"Column::Type.eq("1")"#
    )]
    Menu,
    #[sea_orm(
        belongs_to = "super::sys_role::Entity",
        from = "Column::RoleId",
        to = "super::sys_role::Column::Id"
    )]
    Role
}

/// The User table
#[derive(Clone, Debug, PartialEq, DeriveEntityModel, Eq, Serialize, Deserialize)]
#[sea_orm(table_name = "sys_user")]
#[serde(rename_all = "camelCase")]
pub struct Model {
    #[sea_orm(primary_key, auto_increment = false)]
    pub id: String,
    pub created_at: DateTime,
    pub updated_at: DateTime,
    pub created_by: String,
    pub updated_by: String,
    #[sea_orm(unique)]
    pub account: String,
    pub password: String,
    pub name: String,
    #[sea_orm(unique)]
    pub mobile_phone: String,
    pub avatar: Option<String>,
    #[sea_orm(unique)]
    pub email: Option<String>,
    pub status: SwitchStatus,
}
impl Related<super::sys_role::Entity> for Entity {
    fn to() -> RelationDef {
        super::sys_user_role::Relation::Role.def()
    }

    fn via() -> Option<RelationDef> {
        Some(super::sys_user_role::Relation::User.def().rev())
    }
}

pub struct UserToMenu;
impl Linked for UserToMenu {
    type FromEntity = super::sys_user::Entity;
    type ToEntity = super::sys_menu::Entity;

    fn link(&self) -> Vec<LinkDef> {
        vec![
            super::sys_user_role::Relation::User.def().rev(),
            super::sys_user_role::Relation::Role.def(),
            super::sys_role_permission::Relation::Role.def().rev(),
            super::sys_role_permission::Relation::Menu.def()
        ]
    }
}

#[derive(DerivePartialModel, FromQueryResult, Debug)]
#[sea_orm(entity = "Entity")]
pub struct UserEmpty {
}

/// The user_role table, the relation table for user and role
#[derive(Clone, Debug, PartialEq, DeriveEntityModel, Eq, Serialize, Deserialize)]
#[sea_orm(table_name = "sys_user_role")]
#[serde(rename_all = "camelCase")]
pub struct Model {
    #[sea_orm(primary_key, auto_increment = false)]
    pub user_id: String,
    #[sea_orm(primary_key, auto_increment = false)]
    pub role_id: String,
}
#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
pub enum Relation {
    #[sea_orm(
        belongs_to = "super::sys_user::Entity",
        from = "Column::UserId",
        to = "super::sys_user::Column::Id"
    )]
    User,
    #[sea_orm(
        belongs_to = "super::sys_role::Entity",
        from = "Column::RoleId",
        to = "super::sys_role::Column::Id"
    )]
    Role,
}

A total of 5 tables are involved, among which the core rust structure declaration is as shown above. Unimportant code (such as impl ActiveModelBehavior for ActiveModel {} and so on) has been hidden.
2. Write query code, this is a logic for joining 5 tables in a query

let result = entities::prelude::SysUser::find()
        .find_also_linked(entities::sys_user::UserToMenu)
        // .into_tuple() I'm sorry, but the into_tuple method is not supported here, which forces me to define two PartialModel.
        .into_partial_model::<entities::sys_user::UserEmpty, entities::sys_menu::MenuIdentifier>()
        .all(&db)
        .await
        .unwrap();
    for (user, menu) in result {
        println!("{:?} = {:?}", user, menu);
    }

4. Run

Expected Behavior

Normal query results are obtained.

Actual Behavior

Generated SQL in reality:

SELECT "sys_menu"."identifier" FROM "sys_user" LEFT JOIN "sys_user_role" AS "r0" ON "sys_user"."id" = "r0"."user_id" LEFT JOIN "sys_role" AS "r1" ON "r0"."role_id" = "r1"."id" LEFT JOIN "sys_role_permission" AS "r2" ON "r1"."id" = "r2"."role_id" LEFT JOIN "sys_menu" AS "r3" ON "r2"."permission_id" = "r3"."id" AND "sys_role_permission"."type" = '1'

# The pretty format
SELECT
	"sys_menu"."identifier" 
FROM
	"sys_user"
	LEFT JOIN "sys_user_role" AS "r0" ON "sys_user"."id" = "r0"."user_id"
	LEFT JOIN "sys_role" AS "r1" ON "r0"."role_id" = "r1"."id"
	LEFT JOIN "sys_role_permission" AS "r2" ON "r1"."id" = "r2"."role_id"
	LEFT JOIN "sys_menu" AS "r3" ON "r2"."permission_id" = "r3"."id" 
	AND "sys_role_permission"."type" = '1'

Pg db error:

ERROR:  invalid reference to FROM-clause entry for table "sys_role_permission"
LINE 9:  AND "sys_role_permission"."type" = '1'
             ^
HINT:  Perhaps you meant to reference the table alias "r2".

It is obvious that in the above SQL statement, "sys_role_permission"."type" = '1' should be "r2"."type" = '1',
"sys_menu"."identifier" should be "r3"."identifier".

Versions

Latest version