Relation not mapping to object consistently

[zortoflaven]

Describe the bug
Given the following entities and query, the "userRole" relation is not populating as expected.

//Entities
export enum UserRole {
  Admin = "Admin",
  DataManager = "Data Manager", 
  DataEntry = "Data Entry"  
}

@Entity({ tableName: "user" })
export class User {
  [OptionalProps]?: "workspaceRoles";
  @PrimaryKey({
    defaultRaw: "(UUID())",
    length: 38,
    name: "idUser",
    type: "number"
  })
  id: string;  

  @Unique()
  @Property({
    length: 255,
    type: "string"
  })
  Email: string;   

  @OneToMany({ entity: () => WorkspaceUser, mappedBy: "user" })
  workspaceRoles: WorkspaceUser[] = [];
}

@Entity({ tableName: "workspaceuser" })
export class WorkspaceUser {
  @ManyToOne({
    entity: () => User,
    primary: true,
    fieldName: "idUser",
    onUpdateIntegrity: "CASCADE",
    onDelete: "CASCADE"
  })
  user: string | User;

  @ManyToOne({
    entity: () => Workspace,
    primary: true,
    fieldName: "idWorkspace",
    onUpdateIntegrity: "CASCADE",
    onDelete: "CASCADE"
  })
  workspace: string | Workspace;

  @ManyToOne({
    entity: () => UserRole,
    fieldName: "userRole",
    onUpdateIntegrity: "CASCADE",
    onDelete: "RESTRICT"
  })
  userRole: UserRoleEnum | UserRole;
}

@Entity({ tableName: "workspace" })
export class Workspace {
  @PrimaryKey({
    fieldName: "idWorkspace",
    length: 38,
    defaultRaw: "(UUID())",
    type: "string"
  })
  id: string;

  @Unique()
  @Property({
    length: 45,
    type: "string"
  })
  orgName: string;  
}

@Entity({ tableName: "userroles" })
export class UserRole {
  @PrimaryKey({
    name: "UserRole",
    fieldName: "userRole",
    length: 45
  })
  UserRole: UserRoleEnum;

  @Property({
    fieldName: "level",
    columnType: "TINYINT",
    type: "number"
  })
  Level: number;
}

//Query
const userId = "445bff08-0258-45c4-98b8-a1b1b3997de5";
em
      .createQueryBuilder(User)
      .select("*")
      .leftJoinAndSelect("workspaceRoles", "wsr")
      .leftJoinAndSelect("wsr.workspace", "ws")
      .leftJoinAndSelect("wsr.userRole", "role")
      .where({
        id: userId
      }).execute("get");

Output

{
   "id":"445bff08-0258-45c4-98b8-a1b1b3997de5",   
   "Email":"test@test.com", 
   "workspaceRoles":[
      {
         "user":"445bff08-0258-45c4-98b8-a1b1b3997de5",
         "workspace":{
            "id":"8724b13e-9472-462f-b4be-927dc06f11fe",
            "orgName":"Test1"
         },
         "userRole":{
            "UserRole":"Admin",
            "Level":1
         }
      },
      {
         "user":"445bff08-0258-45c4-98b8-a1b1b3997de5",
         "workspace":{
            "id":"d278edec-97fc-4a7c-9999-df0b28967dba",
            "orgName":"Test2"
         },
         "userRole":"Admin" //Why is this not an object like above?
      }
   ]
}

Expected behavior
The "userRole" property for each "workspaceRole" on User should be an object since we selected and joined the relation, however it seems that on some rows, it is not getting populated.

Additional context
This worked as expected prior to v5.8.4. I suspect it is related to the change made for #4741, though I am not certain.

Versions

Dependency	Version
node	18.17.0
typescript	5.2.2
mikro-orm	5.8.8
your-driver	MySQL

[B4nan]

Can you provide a complete reproduction? Entity definition is not a reproduction on its own. Also you say you use qb.execute? As with that, there is no mapping to entities, so it cannot be a serialization problem on its own. In fact, using that method wont even group the results the way you say.

[zortoflaven]

Apologies. I have created a simple repo here showing what I mean: https://github.com/zortoflaven/MikroORM

If you look at the output, you will see that only the first "userRole" is a real object.

[
   {
      "id":"445bff08-0258-45c4-98b8-a1b1b3997de5",
      "Email":"test@test.com",
      "workspaceRoles":[
         {
            "user":"445bff08-0258-45c4-98b8-a1b1b3997de5",
            "workspace":{
               "id":"8724b13e-9472-462f-b4be-927dc06f11fe",
               "orgName":"Test"
            },
            "userRole":{
               "UserRole":"Admin",
               "Level":1
            }
         },
         {
            "user":"445bff08-0258-45c4-98b8-a1b1b3997de5",
            "workspace":{
               "id":"d278edec-97fc-4a7c-9999-df0b28967dba",
               "orgName":"Test2"
            },
            "userRole":"Admin"  //Why not an object?
         }
      ]
   },
   {
      "id":"445bff08-0258-45c4-98b8-a1b1b3997de5",
      "Email":"test@test.com",
      "workspaceRoles":[
         {
            "user":"445bff08-0258-45c4-98b8-a1b1b3997de5",
            "workspace":{
               "id":"8724b13e-9472-462f-b4be-927dc06f11fe",
               "orgName":"Test"
            },
            "userRole":{
               "UserRole":"Admin",
               "Level":1
            }
         },
         {
            "user":"445bff08-0258-45c4-98b8-a1b1b3997de5",
            "workspace":{
               "id":"d278edec-97fc-4a7c-9999-df0b28967dba",
               "orgName":"Test2"
            },
            "userRole":"Admin" //Why not an object?
         }
      ]
   }
]

As mentioned, if you run this with an older version of MikroORM (< 5.8.4), the "userRole" property is a full object for all entries.

[strmer15]

Just ran into this problem when trying to upgrade from 5.8.1 to 5.8.8, tried all the versions in between and had to pin at 5.8.3 to avoid the problem, as you're saying. Is there any workaround besides that?

[B4nan]

One workaround is to not use qb.execute() here, the problem is only in the intermediate state produced by the query builder, if you map to entities, it won't be surfacing at all (which is the reason this was not caught by the existing tests). Fixing this is not trivial, already spent a good day of debugging on it.

[B4nan]

I kinda know how to fix this, but it might be a bit breaking - now you get the cartesian product when using qb.execute and joining to-many relations, in this case you get two rows for the same basically, and the fix I have in mind would merge those. It is something that most people probably expect anyway, so it might be safe to do in v5 in the end.

[zortoflaven]

Having the result of execute not contain the duplicate entries when doing joins would be a nice change for my use case. Of course, as with anything, someone else needs those in there or everything breaks. :)

Appreciate the effort involved here and your time looking into this. Thank you.

[B4nan]

Ok, this was really painful but I think I cracked it: #4825

[zortoflaven]

Updated to latest and everything looks to be working as expected. Many thanks for this.