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