Declaring a composite FK where one of the columns is a part of a different FK messes up the query builder.

[ThomasShih]

Issue description

It seems that somehow, the query builder messes up and tries to use build the query via the direct relationship instead of what is asked.

Expected Behavior

In the code to reproduce:

• Test that "should be able to query consuming entity with root entity"
  • sql should be:

select
	"consuming"."id" as "consuming_id",
	"consuming"."rootId" as "consuming_rootId",
	"consuming"."nodeId" as "consuming_nodeId",
	"root"."id" as "root_id"
from
	"consuming" "consuming"
left join "root" "root" on
	root.id = "consuming"."rootId"

• Test that "should be able to query consuming entity with node and root entity"
  • sql should be:

select
	"consuming"."id" as "consuming_id",
	"consuming"."rootId" as "consuming_rootId",
	"consuming"."nodeId" as "consuming_nodeId",
	"node"."id" as "node_id",
	"node"."rootId" as "node_rootId",
	"root"."id" as "root_id"
from
	"consuming" "consuming"
left join "node" "node" on
	"node"."id" = "consuming"."nodeId"
	and "node"."rootId" = "consuming"."rootId"
left join "root" "root" on
	root.id = "consuming"."rootId"

Actual Behavior

In the code to reproduce:

• Test that "should be able to query consuming entity with root entity"
  • sql is:

select
	"consuming"."id" as "consuming_id",
	"consuming"."rootId" as "consuming_rootId",
	"consuming"."nodeId" as "consuming_nodeId",
	"root"."id" as "root_id"
from
	"consuming" "consuming"
left join "root" "root" on
	root.rootId = "consuming"."rootId"

• Test that "should be able to query consuming entity with node and root entity"
  • sql is:

select
	"consuming"."id" as "consuming_id",
	"consuming"."rootId" as "consuming_rootId",
	"consuming"."nodeId" as "consuming_nodeId",
	"node"."id" as "node_id",
	"node"."rootId" as "node_rootId",
	"root"."id" as "root_id"
from
	"consuming" "consuming"
left join "node" "node" on
	"node"."id" = "consuming"."nodeId"
	and "node"."rootId" = "consuming"."rootId"
left join "root" "root" on
	root.rootId = "consuming"."rootId"

Steps to reproduce

import { type INestApplication } from '@nestjs/common';
import { Test } from '@nestjs/testing';
import {
  getDataSourceToken,
  getRepositoryToken,
  TypeOrmModule,
} from '@nestjs/typeorm';
import {
  type DataSource,
  type Repository,
  Column,
  Entity,
  Index,
  JoinColumn,
  ManyToOne,
  PrimaryGeneratedColumn,
} from 'typeorm';

@Entity('root')
class RootEntity {
  @PrimaryGeneratedColumn('uuid')
  id!: string;
}

@Entity('node')
@Index(['rootId', 'id'], {
  unique: true,
})
class NodeEntity {
  @PrimaryGeneratedColumn('uuid')
  id!: string;

  @ManyToOne(() => RootEntity)
  @JoinColumn({ name: 'rootId', referencedColumnName: 'id' })
  root!: RootEntity;

  @Column({ type: 'uuid' })
  rootId!: string;
}

@Entity('consuming')
class ConsumingEntity {
  @PrimaryGeneratedColumn('uuid')
  id!: string;

  @ManyToOne(() => RootEntity)
  @JoinColumn({ name: 'rootId', referencedColumnName: 'id' })
  root!: RootEntity;

  @Column({ type: 'uuid' })
  rootId!: string;

  @ManyToOne(() => NodeEntity)
  @JoinColumn([
    { name: 'nodeId', referencedColumnName: 'id' },
    { name: 'rootId', referencedColumnName: 'rootId' },
  ])
  node!: NodeEntity;

  @Column({ type: 'uuid' })
  nodeId!: string;
}

describe('typeorm', () => {
  let app: INestApplication;
  let dataSource: DataSource;
  let rootRepository: Repository<RootEntity>;
  let nodeRepository: Repository<NodeEntity>;
  let consumingRepository: Repository<ConsumingEntity>;

  beforeAll(async () => {
    // eslint-disable-next-line regex/invalid-error
    const module = await Test.createTestingModule({
      imports: [
        // Basically a help to start a postgresql testcontainer and sync the required entities
        TypeOrmModule.forRoot({
          type: 'postgres' as const,
          username: 'some value',
          password: 'some value',
          host: 'some value',
          port: 'some value',
          database: 'some value',
          autoLoadEntities: true,
          keepConnectionAlive: true,
          synchronize: true,
        }),
        TypeOrmModule.forFeature([RootEntity, NodeEntity, ConsumingEntity]),
      ],
      providers: [],
    }).compile();
    app = module.createNestApplication();
    await app.init();
    dataSource = app.get(getDataSourceToken());
    rootRepository = await app.get(getRepositoryToken(RootEntity));
    nodeRepository = await app.get(getRepositoryToken(NodeEntity));
    consumingRepository = await app.get(getRepositoryToken(ConsumingEntity));

    const root = await rootRepository.save({});
    const node = await nodeRepository.save({
      rootId: root.id,
    });
    await consumingRepository.save({
      rootId: root.id,
      nodeId: node.id,
    });
  });

  afterAll(async () => {
    await app.close();
  });

  it('should have data stored in all tables', async () => {
    const rootEntity = await rootRepository.find();
    const nodeEntity = await nodeRepository.find();
    const consumingEntity = await consumingRepository.find();
    expect(rootEntity).toHaveLength(1);
    expect(nodeEntity).toHaveLength(1);
    expect(consumingEntity).toHaveLength(1);
  });

  it('should be able to query consuming entity with node entity', async () => {
    const queryBuilder = dataSource
      .createQueryBuilder(ConsumingEntity, 'consuming')
      .leftJoinAndSelect('consuming.node', 'node');

    const sql = queryBuilder.getSql();
    const consumingEntity = await queryBuilder.getRawMany();
    expect(consumingEntity).toHaveLength(1);
  });

  it.failing('should be able to query consuming entity with root entity', async () => {
    const queryBuilder = dataSource
      .createQueryBuilder(ConsumingEntity, 'consuming')
      .leftJoinAndSelect('consuming.root', 'root');

    const sql = queryBuilder.getSql();
    const consumingEntity = await queryBuilder.getRawMany();
    expect(consumingEntity).toHaveLength(1);
  });

  it.failing('should be able to query consuming entity with node and root entity', async () => {
    const queryBuilder = dataSource
      .createQueryBuilder(ConsumingEntity, 'consuming')
      .leftJoinAndSelect('consuming.node', 'node')
      .leftJoinAndSelect('consuming.root', 'root');

    const sql = queryBuilder.getSql();
    const consumingEntity = await queryBuilder.getRawMany();
    expect(consumingEntity).toHaveLength(1);
  });
});

My Environment

Dependency	Version
Operating System	ARM Macbook Pro
Node.js version	v20.11.0
Typescript version	5.3.3
TypeORM version	0.3.20

Additional Context

No response

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?

No, I don’t have the time and I’m okay to wait for the community / maintainers to resolve this issue.

[ThomasShih]

To confirm, 0.3.21 does not fix this issue:
QueryFailedError: column root.rootid does not exist