Defining entities with relationships that have overlapping columns with different nullability

[dorcastan]

Is your feature request related to a problem? Please describe.

Is there a way to define entities where a column is part of multiple foreign keys / relationships, but one relationship is optional and the other is required?

Desired schema:

create table "student_allocation" (
  "student_id" text not null,
  "academic_year" text not null,
  "school_code" text not null references "school" ("school_code"),
  "class_code" text null,
  constraint "student_allocation_pkey" primary key ("student_id", "academic_year")
);

alter table "student_allocation" add constraint "student_allocation_class_foreign"
foreign key ("school_code", "academic_year", "class_code") references "class" ("school_code", "academic_year", "class_code");

• (Context: The app uses natural keys. A student may be assigned to a school but not have a class yet, hence both relationships are necessary.)

Current behaviour: When defining the optional relationship, all columns (including school_code and academic_year) are marked as nullable. Moving the school property after class "fixes" the issue for school_code, but this doesn't work for academic_year.

Sample code:

import { Entity, ManyToOne, PrimaryKey, PrimaryKeyType } from '@mikro-orm/core';

@Entity()
class School {
  @PrimaryKey()
  schoolCode: string;
}

@Entity()
class Class {
  [PrimaryKeyType]: [string, string, string];

  @PrimaryKey({ name: 'school_code' })
  school: School;

  @PrimaryKey()
  academicYear: string;

  @PrimaryKey()
  classCode: string;
}

@Entity()
export class StudentAllocation {
  [PrimaryKeyType]: [string, string];

  @PrimaryKey()
  studentId: string;

  @PrimaryKey()
  academicYear: string;

  @ManyToOne({ name: 'school_code' })
  school: School;

  // TODO: this overwrites the nullability of school_code and academic_year -- both columns are set to nullable.
  @ManyToOne({ fieldNames: ['school_code', 'academic_year', 'class_code'] })
  class?: Class;
}

Describe the solution you'd like

My understanding from #593 (comment) that this isn't a feature supported by MikroORM --

you can't have 2 properties on your entity that represent same column (parentEntity vs parentEntityKey are in fact one column but you are modelling it as two).

Is there a recommended way to address this use case?

Alternatively, would it be possible to add a flag to make the schema generator ignore the relationship entirely? e.g. modify ignoreSchemaChanges to apply to foreign keys.

• Desired behaviour: once the DB migration has been manually fixed, no further schema changes will be flagged out.

  @ManyToOne({
    fieldNames: ['school_code', 'academic_year', 'class_code'],
    ignoreSchemaChanges: ['type'] 
  })
  class?: Class;

[B4nan]

you can't have 2 properties on your entity that represent same column (parentEntity vs parentEntityKey are in fact one column but you are modelling it as two).

I want to look into lifting this restriction in v6, not sure if we have an open issue for that, so let's keep this one.

[dorcastan]

Thanks for the speedy reply!

Is there any early indication / target date for when v6 will be released?

[B4nan]

Not really, I am kinda almost there, but it's hard to find some time to work on it during summer, and I still have some rather challenging refactors I want to do (or at least give it a try). And then there are the outdated docs and the new getting started guide.

But v6 is already published to NPM on each commit to v6 branch, and is generally usable. The upgrading guide is here:

https://github.com/mikro-orm/mikro-orm/blob/v6/docs/docs/upgrading-v5-to-v6.md

[B4nan]

This can be actually fixed in v5, it's pretty simple in the end.