Two relations using the same column

[akermabon]

Issue description

Invalid join queries when two relations of an entity use the same column

Expected Behavior

We can't put the full code for confidentiality purpose but the following setup is relevant to understand the issue. We have those 3 entities:

Customer - standard table PK on id

Order - partitioned table PK composite on (customer_id, id) FK on customer_id -> Customer(id)

Ticket - partitioned table PK composite on (customer_id, id) FK on customer_id -> Customer(id) FK composite on (customer_id, order_id) -> Order(customer_id, id)

With this setup, we should be able to query tickets normally

Actual Behavior

We get invalid generated SQL when we query the Ticket entity We have to disable entity synchronization and handle joins ourselves to make it work

Steps to reproduce

The following setup should produce the current behavior

@Entity({ name: 'customers' })
class Customer {
  @PrimaryColumn('uuid')
  id: string;
}

@Entity({ name: 'orders' })
class Order {
  @PrimaryColumn('uuid')
  id: string;

  @PrimaryColumn('uuid')
  customerId: string;

  @JoinColumn([{ name: 'customer_id', referencedColumnName: 'id' }])
  @ManyToOne(() => Customer)
  customer: Customer;

  @OneToMany(() => Ticket, (ticket) => ticket.order)
  tickets: Ticket[];
}

@Entity({ name: 'tickets' })
class Ticket {
  @PrimaryColumn('uuid')
  id: string;

  @PrimaryColumn('uuid')
  customerId: string;

  @Column({ nullable: true, type: 'uuid' })
  orderId: string;

  @JoinColumn([{ name: 'customer_id', referencedColumnName: 'id' }])
  @ManyToOne(() => Customer)
  customer: Customer;

  @ManyToOne(() => Order, (order) => order.tickets, { nullable: true })
  @JoinColumn([
    { name: 'customer_id', referencedColumnName: 'customerId' },
    { name: 'order_id', referencedColumnName: 'id' },
  ])
  order: Order | null;
}

My Environment

Dependency	Version
Operating System	macOS sequoia15.0.1
Node.js version	22.9.0
TypeORM version	0.3.20

Additional Context

We believe the issue is due to the fact that we have both on Ticket entity

• 1 FK to Customer using customer_id
• 1 composite FK to Order which also uses customer_id

It works well if we have either one, but having both at the same time causes the bug. It seems we're not the only one who had this problem as I found 2 issues which are almost the same: https://github.com/typeorm/typeorm/issues/10148, https://github.com/typeorm/typeorm/issues/10121

Is there a known workaround ? Disabling synchro is really painful for us Thank you 🙏

Relevant Database Driver(s)

• [ ] aurora-mysql
• [ ] aurora-postgres
• [ ] better-sqlite3
• [ ] cockroachdb
• [ ] cordova
• [ ] expo
• [ ] mongodb
• [ ] mysql
• [ ] nativescript
• [ ] oracle
• [X] 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.

[yevhen-komarov]

I'm not sure if it's the same thing. I solved the problem by creating separate entities for each relationship

@Entity('text_designations')
export class TextDesignation {
  @PrimaryColumn('int', { unsigned: true })
  desId: number;

}

@Entity('text_designations')
export class msTextDesignation extends TextDesignation {
  @JoinColumn({ name: 'DES_ID', referencedColumnName: 'msNameDes' })
  @ManyToOne(() => ModelsSeries, { createForeignKeyConstraints: false })
  modelsSeries: ModelsSeries;
}

@Entity('text_designations')
export class mscsTextDesignation extends TextDesignation {
  @JoinColumn({ name: 'DES_ID', referencedColumnName: 'mscsNameDes' })
  @ManyToOne(() => MsCountrySpecific, { createForeignKeyConstraints: false })
  msCountrySpecific: MsCountrySpecific;
}

[yevhen-komarov]

Hi, I have a similar issue and am ready to fix it. I created a test for this task https://github.com/yevhen-komarov/typeorm/tree/fix-issue-11109/test/github-issues/11109 Indeed it crashes on "loading tickets with relations using QueryBuilder". Confirm that the test reproduces your issue.

[yevhen-komarov]

If you need to fix this issue or want to test the solution, install https://github.com/typeorm/typeorm/pull/11400#issuecomment-2849034549