MikroORM - Non-FK'd ID conditional join

Question

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.

Answer 1

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; 
}