Open ilxqx opened 8 months ago
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".
Thank you for posting an example, can you trim this down? It'd help locate the problem.
[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"
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 {}
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 {}
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.
}
Entity::insert(model)
does not trigger the before_save
method in ActiveModelBehavior, but the Model's insert()
method can trigger. 😭If we use find_also_linked
and into_partial_model
together, the latter clears aliases setup by the former (A_
), causing this bug.
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",
),
}
FWIW our team is currently using this workaround: https://gist.github.com/jinohkang-theori/4bc96527eaf1c8e22ee7d7252338a591
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.Steps to Reproduce
impl Related for Entity {
fn to() -> RelationDef {
super::sys_role_permission::Relation::Role.def()
}
}
[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)]
} impl Related for Entity {
fn to() -> RelationDef {
super::sys_role_permission::Relation::Menu.def()
}
}
impl Related for Entity {
fn to() -> RelationDef {
super::sys_user_role::Relation::User.def()
}
}
/// 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)]
}
[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
pub enum Relation {
[sea_orm(
}
/// 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)]
} impl Related for Entity {
fn to() -> RelationDef {
super::sys_user_role::Relation::Role.def()
}
}
pub struct UserToMenu; impl Linked for UserToMenu { type FromEntity = super::sys_user::Entity; type ToEntity = super::sys_menu::Entity;
}
[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)]
}
[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
pub enum Relation {
[sea_orm(
}
Expected Behavior
Normal query results are obtained.
Actual Behavior
Generated SQL in reality:
Pg db error:
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