typescriptmikro-orm

MikroORM - Non-FK'd ID conditional join


I have inherited this strange database schema, where there's a jsonb field, and on that json is a userId, and userType. There are also two user tables, representing different user types. The table that userId joins to is dependent on userType.

Now I'm sure I can use query builder to piece together the appropriate SQL, but I currently have a very dynamic system that translates GQL to Mikro filters, so any manual piecing together complicates things.

Is there any possible way I can do something like

@Entity()
class Job {
  @Property({ type: 'jsonb' })
  props: unknown

  @Formula(alias => `select user.name, user.dob, user.etc from user where user.id = ${alias.props->>userId`)
  user: User
}

or something similiar? Basically using decorators to define how to retrieve those pieces of information.


Solution

  • You can create a virtual relation, you just need to use the relation decorator with the formula option instead of the @Formula decorator directly.

    Something like this should work:

    @ManyToOne({
      entity: () => User,
      formula: alias => `${alias}.props->>userId`,
    })
    user: User
    

    Here is a working example doing something similar in the ORM tests:

    https://github.com/mikro-orm/mikro-orm/blob/master/tests/issues/GH4759.test.ts#L34

    Here is how you could handle the different types:

    @ManyToOne({
      entity: () => User,
      formula: alias => `case when ${alias}.userType = 'manager' then null else ${alias}.props->>userId end`,
      hidden: true,
      nullable: true,
    })
    user?: User;
    
    @ManyToOne({
      entity: () => ManagerUser,
      formula: alias => `case when ${alias}.userType = 'manager' then ${alias}.props->>userId else null end`,
      hidden: true,
      nullable: true,
    })
    manager?: ManagerUser;
    
    @Property({
      persist: false,
    })
    get userOrManager(): User | ManagerUser | undefined {
      return this.userType === 'manager' ? this.manager : this.user; 
    }