`sea_orm_cli` doesn't generate the correct relation for a foreign key in a different schema

[junbl]

Description

If a table has a foreign key relationship with a table in a different schema, sea doesn't generate the correct code. It seems to assume that all referenced tables will be in the one schema.

Probably relatively low priority, since you get an immediate compile error with an obvious fix and it's a relatively uncommon case to begin with.

Steps to Reproduce

1. Create two schemata and a table for each, e.g. schema1.src and schema2.dest
2. Add a foreign key constraint from one to the other, e.g. schema2.dest.src_id references schema1.src.id
3. Generate the models with sea.

Expected Behavior

The relation will be generated with the correct model/entity references.

Actual Behavior

The relation is generated incorrectly, referencing a nonexistent table in the current schema.

Reproduces How Often

Always reproducible.

Workarounds

Editing the generated code with the actual path to the referenced module.

Reproducible Example

https://github.com/SeaQL/sea-orm/pull/1779

Versions

sea-orm-cli v0.11.0:
    sea
    sea-orm-cli

├── sea-orm v0.11.3
│   ├── sea-orm-macros v0.11.3 (proc-macro)
│   ├── sea-query v0.28.5
│   │   ├── sea-query-derive v0.3.0 (proc-macro)
│   ├── sea-query-binder v0.3.1
│   │   ├── sea-query v0.28.5 (*)
│   ├── sea-strum v0.23.0
│   │   └── sea-strum_macros v0.23.0 (proc-macro)

Ubuntu 22.04 on Windows 11 (WSL)

[Marcelo-maga]

Hello, I don't know if this is the correct way to solve this problem, but recently I went through this exact situation and I solved it the following way:

I generated the entities separately and soon after importing them in the files that are showing conflicts

use crate::entity::module name::{ table name }

then I removed the super that is in front of the imports

// example of how it works without error
Self::EntityName=> Entity::has_many(super::entity::Entity).into(),

// example of how I resolved the error
Self::EntityName=> Entity::belongs_to(entity::Entity)
...

// example of how it works without error
impl Related<super::entity::Entity> for Entity {
    fn to() -> RelationDef {
        Relation::entity.def()
    }
}

// example of how I resolved the error
impl Related<entity::Entity> for Entity {
    fn to() -> RelationDef {
        Relation::entity.def()
    }
}

I did it like this and it solved my problem, but I don't think it's the best solution 👍

[junbl]

Related to the codegen but probably a larger issue is that even if you change the columns to refer to the correct objects, the relation doesn't get the correct tables (from_tbl still just refers to the table without the schema).

This is what I ended up writing to get it to work:

impl Related<crate::entity::schema1::src::Entity> for Entity {
    fn to() -> RelationDef {
        let mut relation = Relation::Src.def();
        relation.from_tbl =
            TableRef::SchemaTable(Arc::new(Alias::new("schema2")), Arc::new(Alias::new("dest")));
        relation.to_tbl =
            TableRef::SchemaTable(Arc::new(Alias::new("schema1")), Arc::new(Alias::new("src")));
        relation
    }
}

I was looking at RelationDef -- is from_tbl and to_tbl necessary or could it just be from_col/to_col: ColumnRef and then use ColumnRef::SchemaTableColumn?