QueryBuilder looks up the wrong joinAttr when entities have identical property names

[thelukester92]

Issue description

QueryBuilder looks up the wrong joinAttr when entities have identical property names

Expected Behavior

When two entities in a query have the same property name, then the query builder should generate the correct query that references the correct entity's property. For example, if EntityA has a field child: EntityB and EntityB has a field child: EntityC, queryBuilder.where({ child: { child: { field: 'value' } } }) should match entities where EntityC.field = 'value'.

Actual Behavior

The first joinAttr matching property name is always referenced, regardless of context, resulting in either 1) a runtime error if the field doesn't exist or 2) a logical error in the generated query that matches the wrong entity's field.

Steps to reproduce

@Entity()
export class EntityA {
  @PrimaryGeneratedColumn()
  id: number;

  @OneToOne(() => EntityB)
  @JoinColumn()
  child: EntityB;
}

@Entity()
export class EntityB {
  @PrimaryGeneratedColumn()
  id: number;

  @OneToOne(() => EntityC)
  @JoinColumn()
  child: EntityC;
}


@Entity()
export class EntityC {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  field: string;
}

const repo = dataSource.getRepository(EntityA);
const query = repo
  .createQueryBuilder('entity_a')
  .innerJoinAndSelect('entity_a.child', 'entity_b')
  .innerJoinAndSelect('entity_b.child', 'entity_c');

query.where({
  child: {
    child: {
      field: 'value',
    },
  },
});

console.log(query.getSql());

await query.getOne();

In this example, the generated query matches joinAttr with propertyName child as entity_a and generates a query attempting to match field: 'value' for entity_b instead of for entity_c. Generated SQL:

SELECT "entity_a"."id" AS "entity_a_id", "entity_a"."childId" AS "entity_a_childId", "entity_b"."id" AS "entity_b_id", "entity_b"."field" AS "entity_b_field", "entity_b"."childId" AS "entity_b_childId", "entity_c"."id" AS "entity_c_id", "entity_c"."field" AS "entity_c_field" FROM "entity_a" "entity_a" INNER JOIN "entity_b" "entity_b" ON "entity_b"."id"="entity_a"."childId"  INNER JOIN "entity_c" "entity_c" ON "entity_c"."id"="entity_b"."childId" WHERE "entity_b"."field" = $1

Since in this case EntityB.field doesn't exist, a runtime error is thrown. If EntityB.field does exist, we get a logical error where the generated query succeeds but tests the wrong value.

My Environment

Dependency	Version
Operating System
Node.js version	18.13.0
Typescript version	4.9.5
TypeORM version	0.3.20

Additional Context

I believe the fix is a single line fix in QueryBuilder.ts:1311 to match joinAttr not only by propertyName but also by parent table's alias name:

// old logic
// note: `relationPropertyPath` is just `entityOrProperty.slice(entityOrProperty.index('.') + 1)`)
// (joinAttr) => joinAttr.relationPropertyPath === part,

// new logic
(joinAttr) => joinAttr.entityOrProperty === `${alias.name}.${part}`,

I have a fork with the fix in place and verified against my repro, but I haven't contributed here before so I'll need to write and run tests and I'm not sure if there are any other considerations that might make my solution invalid. Would appreciate feedback!

Relevant Database Driver(s)

• aurora-mysql
• aurora-postgres
• better-sqlite3
• cockroachdb
• cordova
• expo
• mongodb
• mysql
• nativescript
• oracle
• postgres
• react-native
• sap
• spanner
• sqlite
• sqlite-abstract
• sqljs
• sqlserver

Are you willing to resolve this issue by submitting a Pull Request?

Yes, I have the time, but I don't know how to start. I would need guidance.