TypeORM: when can join condition be omitted?

Question

I'm working on two projects using TypeORM. In one of these projects, when I use the query builder to select data and the query contains a join, the generated query does not contain the join condition, generating the following query:

SELECT *
FROM `alarms`.`faultLog` `fl`
INNER JOIN `alarms`.`alarm` `a`

Which gives 2 543 922 results instead of the expected 1743 results.

The query used:

return this.faultsRepository
            .createQueryBuilder('fl')
            .innerJoin('fl.alarm', 'a')
            .getRawMany();

If I add the condition as third parameter of the innerjoin function, then the following invalid query:

SELECT *
FROM `alarms`.`faultLog` `fl`
INNER JOIN `alarms`.`alarm` `a`
ON  AND (`a`.`id` = fl.alarmId)

If I change the name of the property in the query to alarm instead of fl.alarm then the generated query is ok.

In the other project, I have a similar query which does not pose a problem, in which the generated query contains the join condition:

return this._recipeRepository
            .createQueryBuilder('r')
            .innerJoin('r.type', 'rType')
            .innerJoin('r.equipment', 'eq')
            .innerJoin('r.versions', 'rVers')
            .select([
              'r.id as id',
              'r.name as name',
              'rType.name as type',
              'r.SKU as sku',
              'r.description as description',
              'rVers.id as versionId',
              'rVers.versionNumber as version'
            ])
            .getRawMany();

I checked in TypeORM initialization and repository configuration and everything seems to be equivalent so I don't understand what is happening here.

The trimed down entities:

@Entity({
  name: "alarm",
  database: 'alarms',
  synchronize: false,
})
export class AlarmEntity {
  @PrimaryGeneratedColumn({
    name: 'id',
    type: 'integer'
  })
  @Generated('increment')
  id: number;

  @Column({
    name: 'description',
    type: 'varchar',
    length: '200'
  })
  description: string;

  @OneToMany(
   () => FaultLogEntity,
   faultLog => faultLog.alarm,
   {
     cascade: true
   }
  )
  faultLogs: FaultLogEntity[];
}

@Entity({
  name: "faultLog",
  database: 'alarms',
  synchronize: false
})
export class FaultLogEntity {
  @PrimaryGeneratedColumn({
    name: 'id',
    type: 'integer'
  })
  id: number;

  @Column({type: 'datetime'})
  timestampAppearance:Date;

  @OneToMany(() => AlarmEntity, alarm => alarm.faultLogs)
  @JoinColumn({ name: 'alarmId' })
  alarm: AlarmEntity;
}

Repositories configuration:

export const alarmsProviders = [
  {
    provide: "AlarmEntity",
    useFactory: (connection: Connection) => connection.getRepository(AlarmEntity),
    inject: [Constants.DATABASE_CONNECTION]
  },
  {
    provide: "FaultLogEntity",
    useFactory: (connection: Connection) => connection.getRepository(FaultLogEntity),
    inject: [Constants.DATABASE_CONNECTION]
  },
]

Database connection:

export const databaseProviders = [
  {
    provide: Constants.DATABASE_CONNECTION,
    useFactory: async () => await createConnection({
      type: 'mysql',
      host: 'localhost',
      port: 3000,
      username: 'xxx',
      password: 'xxx',
      database: 'alarms',
      synchronize: false,
      logging: true,
      entities: [
        __dirname + '/../**/*.entity{.ts,.js}',
      ],
    }),
  },
];

Usage in app module:

@Module({
  controllers: [AppController, AlarmsController],
  providers: [AppService, AlarmsService, ...databaseProviders, ...alarmsProviders]
})
export class AppModule {
}

So, the main question is: What does TypeORM needs for automatically inferring join conditions? What am I missing here?

Answer 1

One of the entities should have a ManyToOne relationship, you should try changing the relationship in FaultLogEntity to ManyToOne