Alias bug in the SQL statement generated by the find_also_linked function

[ilxqx]

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

[ilxqx]

And if I specify table aliases when customizing a Join query, the alias for the on_condition = r#"Column::Type.eq("1")"# in the relationship declaration has not been changed:

let sql = entities::prelude::SysMenu::find()
        .join_as_rev(
            JoinType::InnerJoin,
            entities::sys_role_permission::Relation::Menu.def(),
            Alias::new("tt"),
        )
        .build(DbBackend::Postgres)
        .to_string();
    println!("{}", sql);

Result:

SELECT "sys_menu"."id", "sys_menu"."created_at", "sys_menu"."updated_at", "sys_menu"."created_by", "sys_menu"."updated_by", "sys_menu"."pid", "sys_menu"."type", "sys_menu"."name", "sys_menu"."route", "sys_menu"."hidden", "sys_menu"."identifier", "sys_menu"."meta", "sys_menu"."status", "sys_menu"."seq", "sys_menu"."icon", "sys_menu"."affix" FROM "sys_menu" INNER JOIN "sys_role_permission" AS "tt" ON "tt"."permission_id" = "sys_menu"."id" AND "sys_role_permission"."type" = '1'

# Formatted
SELECT
	"sys_menu"."id",
	"sys_menu"."created_at",
	"sys_menu"."updated_at",
	"sys_menu"."created_by",
	"sys_menu"."updated_by",
	"sys_menu"."pid",
	"sys_menu"."type",
	"sys_menu"."name",
	"sys_menu"."route",
	"sys_menu"."hidden",
	"sys_menu"."identifier",
	"sys_menu"."meta",
	"sys_menu"."status",
	"sys_menu"."seq",
	"sys_menu"."icon",
	"sys_menu"."affix" 
FROM
	"sys_menu"
	INNER JOIN "sys_role_permission" AS "tt" ON "tt"."permission_id" = "sys_menu"."id" 
	AND "sys_role_permission"."type" = '1'

Error:

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

[tyt2y3]

Thank you for posting an example, can you trim this down? It'd help locate the problem.

[ilxqx]

Ok, All you need to do is create a new cargo project, then copy the contents of the following 4 files, and finally run the program to reproduce this issue. Thanks!

Cargo.toml

[package]
name = "sea-orm-bugs"
version = "0.1.0"
edition = "2021"

# See more keys and their definitions at https://doc.rust-lang.org/cargo/reference/manifest.html

[dependencies]
sea-orm = { version = "0.12.4", features = ["macros", "time", "chrono", "sqlx-postgres", "uuid", "debug-print", "runtime-tokio", "bigdecimal", "serde_json"] }
sqlx = { version = "0.7.2", features = ["runtime-tokio", "bigdecimal", "postgres", "macros", "chrono", "json", "regexp", "uuid"] }
tokio = { version = "1.33.0", features = ["full"] }
log = "0.4.20"
env_logger = "0.10.0"

src/car.rs

use sea_orm::entity::prelude::*;

#[derive(Clone, Debug, PartialEq, DeriveEntityModel, Eq)]
#[sea_orm(table_name = "car")]
pub struct Model {
    #[sea_orm(primary_key, auto_increment = false)]
    pub id: String,
    pub name: String,
    pub description: String,
}

#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
pub enum Relation {
    #[sea_orm(has_many = "super::wheel::Entity")]
    Wheel
}

impl Related<super::wheel::Entity> for Entity {
    fn to() -> RelationDef {
        Relation::Wheel.def()
    }
}

impl ActiveModelBehavior for ActiveModel {}

src/wheel.rs

use sea_orm::entity::prelude::*;

#[derive(Clone, Debug, PartialEq, DeriveEntityModel, Eq)]
#[sea_orm(table_name = "wheel")]
pub struct Model {
    #[sea_orm(primary_key, auto_increment = false)]
    pub id: String,
    pub car_id: String,
    pub brand: String,
    pub name: String,
}

#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
pub enum Relation {
    #[sea_orm(
        belongs_to = "super::car::Entity",
        from = "Column::CarId",
        to = "super::car::Column::Id",
        on_condition = r#"Column::Brand.eq("Michelin")"#
    )]
    Car
}

impl Related<super::car::Entity> for Entity {
    fn to() -> RelationDef {
        Relation::Car.def()
    }
}

impl ActiveModelBehavior for ActiveModel {}

src/main.rs

mod car;
mod wheel;

use log::{info, LevelFilter};
use sea_orm::{ConnectionTrait, ConnectOptions, Database, DatabaseConnection, EntityTrait, JoinType, QuerySelect, RelationTrait, Schema};
use sea_orm::sea_query::Alias;

#[tokio::main]
async fn main() {
    env_logger::builder()
        .filter_level(LevelFilter::Debug)
        .init();

    let mut connect_options = ConnectOptions::new("postgres://postgres:12345678@localhost:5432/postgres");
    connect_options.sqlx_logging(false);
    let db = Database::connect(connect_options).await.expect("fail to connect to database");

    // Note: The example of table structure may not be appropriate, but the problem can be reproduced.
    //
    // create table
    // let backend = db.get_database_backend();
    // let schema = Schema::new(backend);
    // db.execute(
    //     backend.build(
    //         &schema.create_table_from_entity(car::Entity)
    //     )
    // ).await.expect("Create table car failed");
    // db.execute(
    //     backend.build(
    //         &schema.create_table_from_entity(wheel::Entity)
    //     )
    // ).await.expect("Create table car failed");

    // This is ok.
    // let result = car::Entity::find()
    //     .join_rev(
    //         JoinType::InnerJoin,
    //         wheel::Relation::Car.def()
    //     )
    //     .all(&db)
    //     .await
    //     .unwrap();

    // Will cause a Panic error
    let result = car::Entity::find()
        .join_as_rev(
            JoinType::InnerJoin,
            wheel::Relation::Car.def(),
            Alias::new("t")
        )
        .all(&db)
        .await
        .unwrap();

    // Here's an explanation: The find_with_link() method is probably the same issue because it likely uses aliases like A_xxx and B_xxx internally.
    // However, it specifically does not handle aliases in the field conditions of on_condition defined in Relation.
}

[ilxqx]

I also found another possible BUG at the same time：

Entity::insert(model) does not trigger the before_save method in ActiveModelBehavior, but the Model's insert() method can trigger. 😭

[jinohkang-theori]

If we use find_also_linked and into_partial_model together, the latter clears aliases setup by the former (A_), causing this bug.

[Enitoni]

I'm having a similar issue. Trying to use a where clause with linked does not work in the same way:

let users = users::Entity::find()
    .find_also_linked(UserRole)
    .filter(roles::Column::Name.eq("superuser"))
    .all(db)
    .await
    .map_err(ApiError::db)?;

Yields

SELECT
  "users"."id" AS "A_id",
  "users"."member_id" AS "A_member_id",
  "users"."password" AS "A_password",
  "users"."activation_token" AS "A_activation_token",
  "r1"."id" AS "B_id",
  "r1"."name" AS "B_name"
FROM
  "users"
  LEFT JOIN "role_users" AS "r0" ON "users"."id" = "r0"."user_id"
  LEFT JOIN "roles" AS "r1" ON "r0"."role_id" = "r1"."id"
WHERE
  "roles"."name" = $1

As you can see the WHERE clause is incorrect and causes a postgres error in my case:

PgDatabaseError {
      severity: Error,
      code: "42P01",
      message: "invalid reference to FROM-clause entry for table \"roles\"",
      detail: None,
      hint: Some(
          "Perhaps you meant to reference the table alias \"r1\".",
      ),
      position: Some(
          Original(
              342,
          ),
      ),
      where: None,
      schema: None,
      table: None,
      column: None,
      data_type: None,
      constraint: None,
      file: Some(
          "parse_relation.c",
      ),
      line: Some(
          3628,
      ),
      routine: Some(
          "errorMissingRTE",
      ),
  }

[jinohkang-theori]

FWIW our team is currently using this workaround: https://gist.github.com/jinohkang-theori/4bc96527eaf1c8e22ee7d7252338a591