sea-orm-cli generation doesn't support multi-column foreign keys

[oersted]

Description

For single-column foreign keys, the generated parent and child have a relation to each other. The parent has a simple has_many and the child has a belong_to with the details of the foreign key columns and on update/delete policies.

For multi-column foreign keys though, it generates one belongs_to relation on the child per column and doesn't generate a has_many relation on the parent. Therefore, for example, using find_with_related doesn't compile.

Steps to Reproduce

#[derive(Iden)]
enum Block {
    Table,
    Qidx,
    Bidx,
}

#[derive(Iden)]
enum Rule {
    Table,
    Qidx,
    Bidx,
    Ridx,
}

#[async_trait::async_trait]
impl MigrationTrait for Migration {
    async fn up(&self, manager: &SchemaManager) -> Result<(), DbErr> {
        manager
            .create_table(Table::create()
                .table(Block::Table)
                .if_not_exists()
                .col(ColumnDef::new(Block::Qidx).big_unsigned().not_null())
                .col(ColumnDef::new(Block::Bidx).big_unsigned().not_null())
                .primary_key(Index::create()
                    .name("PK-block")
                    .col(Rule::Qidx).col(Rule::Bidx)
                    .primary())
                .to_owned())
            .await?;

        manager
            .create_table(Table::create()
                .table(Rule::Table)
                .if_not_exists()
                .col(ColumnDef::new(Rule::Qidx).big_unsigned().not_null())
                .col(ColumnDef::new(Rule::Bidx).big_unsigned().not_null())
                .col(ColumnDef::new(Rule::Ridx).big_unsigned().not_null())
                .primary_key(Index::create()
                    .name("PK-rule")
                    .col(Rule::Qidx).col(Rule::Bidx).col(Rule::Ridx)
                    .primary())
                .foreign_key(ForeignKey::create()
                    .name("FK-rule-block")
                    .from(Rule::Table, (Rule::Qidx, Rule::Bidx))
                    .to(Block::Table, (Block::Qidx, Block::Bidx))
                    .on_delete(ForeignKeyAction::Cascade)
                    .on_update(ForeignKeyAction::Restrict))
                .to_owned())
            .await?;
    }

It will generate both of these files.

//! SeaORM Entity. Generated by sea-orm-codegen 0.8.0

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

#[derive(Clone, Debug, PartialEq, DeriveEntityModel)]
#[sea_orm(table_name = "block")]
pub struct Model {
    #[sea_orm(primary_key, auto_increment = false)]
    pub qidx: i32,
    #[sea_orm(primary_key, auto_increment = false)]
    pub bidx: i32,
}

impl ActiveModelBehavior for ActiveModel {}

//! SeaORM Entity. Generated by sea-orm-codegen 0.8.0

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

#[derive(Clone, Debug, PartialEq, DeriveEntityModel)]
#[sea_orm(table_name = "rule")]
pub struct Model {
    #[sea_orm(primary_key, auto_increment = false)]
    pub qidx: i32,
    #[sea_orm(primary_key, auto_increment = false)]
    pub bidx: i32,
    #[sea_orm(primary_key, auto_increment = false)]
    pub ridx: i32,
}

#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
pub enum Relation {
    #[sea_orm(
        belongs_to = "super::block::Entity",
        from = "Column::Qidx",
        to = "super::block::Column::Qidx",
        on_update = "Restrict",
        on_delete = "Cascade"
    )]
    Block2,
    #[sea_orm(
        belongs_to = "super::block::Entity",
        from = "Column::Bidx",
        to = "super::block::Column::Bidx",
        on_update = "Restrict",
        on_delete = "Cascade"
    )]
    Block1,
}

impl ActiveModelBehavior for ActiveModel {}

Expected Behavior

It should generate a has_many relation on Block pointing to Rule. Simply adding this by hand doesn't trivially work, I'm unsure how the Related trait should be implemented for a double relation like Block1 and Block2, is the relation on the child even correct?

Even if the support for this in the generation is not implemented in the short term, I would really appreciate some guidance on how to define the Entity manually so that this works as intended.

Versions

sea-orm 0.8.0

[oersted]

Are multi-column Relations even supported by SeaORM in general? The definition of the RelationDef struct only accepts a single from_col and to_col.

What more idiomatic alternative would you suggest? Should I add a separate unique id to each table and use that for the relations?

If it is not supported, why does the generation not fail? And why does it fall back on this Block1 & Block2 pattern?

[billy1624]

Hey @oersted, thanks for the report! SeaORM did support multi-column foreign keys:

• sea-orm/src/tests_cfg/cake_filling_price.rs

  Lines 65 to 77 in a0a2492

  	impl RelationTrait for Relation {
  	fn def(&self) -> RelationDef {
  	match self {
  	Self::CakeFilling => Entity::belongs_to(super::cake_filling::Entity)
  	.from((Column::CakeId, Column::FillingId))
  	.to((
  	super::cake_filling::Column::CakeId,
  	super::cake_filling::Column::FillingId,
  	))
  	.into(),
  	}
  	}
  	}

But codegen don't support that yet. What's the database you're using?

[oersted]

Thank you for the follow-up. I'm using Sqlite.

[Pascualex]

I have a similar problem and am able to manually fix it by replacing

from = "Column::SampleId",
to = "super::samples::Column::ProjectId",

With

from = "(Column::SampleId, Column::ProjectId)",
to = "(super::samples::Column::Id, super::samples::Column::ProjectId)",

But it's a bit painful to have to constantly fix it when regenerating the code. @billy1624 can you point me in the direction of where this should be fixed? I want to give it a try.