Find-Operations on Relations leads to compiler issues and weird queries after upgrade 0.2 > 0.3

[itinance]

After upgrade from TypeORM 0.2.41 to 0.3.9, find-operations with related entites do not work any more thanks to compiler issues with typeScript. Any known workaround leads to a weird behaviour caused by broken queries built in query builder.

Issue Description

Given are the following entities, which are related to each other with OneToMany/ManyToOne:

VaultEntity:

@Entity({ name: 'vaults' })
@Unique('idx_unique_uuid', ['uuid'])
export class VaultEntity extends ImmutableBaseEntity {
  @ManyToOne(() => ClientEntity, (entity) => entity.vaults)
  @JoinColumn({ name: 'client_id' })
  @Index()
  client: ClientEntity;

  @Column({ name: 'uuid', type: 'varchar', length: 64 })
  @IsUUID()
  uuid: string;
 
  // .... more properties that makes no sense to mention them here
}

ClientEntity:

@Entity({ name: 'clients' })
@Unique('idx_unique_uuid', ['uuid'])
export class ClientEntity extends ImmutableBaseEntity {
  @ApiProperty({ description: 'UUID' })
  @Column({ name: 'uuid', type: 'varchar', length: 64 })
  @IsUUID()
  uuid: string;

  @ApiProperty({ description: 'Associated vaults' })
  @OneToMany(() => VaultEntity, (vault) => vault.client, { cascade: true })
  @JoinColumn({ name: 'client_id' })
  vaults: VaultEntity[];

  @ApiProperty({ type: (type) => UserEntity, isArray: true })
  @OneToMany(() => UserEntity, (user) => user.client, { cascade: true })
  users: UserEntity[];

  // ....
}

In typeorm 0.2.x, I used functions like this in my VaultService in order to find vaults which are associated to a specific Client:

  async findAllForClient(client: ClientEntity): Promise<VaultEntity[]> {
    return this.repository.find({
      where: { client: client },
      order: { createdAt: 'ASC' },
    });
  }

With typeorm 0.3.x, this leads to compiler issues:

TS2322: Type 'ClientEntity' is not assignable to type 'boolean | FindOperator | FindOptionsWhere | FindOptionsWhere[] | EqualOperator<...>'.   Type 'ClientEntity' is not assignable to type 'FindOptionsWhere'.     Types of property 'users' are incompatible.       Type 'UserEntity[]' is not assignable to type 'boolean | FindOperator | FindOptionsWhere | FindOptionsWhere[] | EqualOperator<...>'.         Type 'UserEntity[]' is not assignable to type 'FindOptionsWhere[]'.           Type 'UserEntity' is not assignable to type 'FindOptionsWhere'.             Types of property 'hasRole' are incompatible.               Type '(role: string) => boolean' is not assignable to type 'never'.

I'm curious why UserEntity plays a role here as it makes no sense.

Some people on GitHub suggest to use the Equal-Operator:

  async findAllForClient(client: ClientEntity): Promise<VaultEntity[]> {
    return this.repository.find({
      where: { client: Equal(client) },
      order: { createdAt: 'ASC' },
    });
  }

But then a query-error is happening that is also hard to figure out why it happens this way:

Unknown column 'createdAt' in 'where clause'

Although all of our entities have a column createdAt , it is not clear why the Query is using this column at all.
This also happens when I remove the order-statement:

  async findAllForClient(client: ClientEntity): Promise<VaultEntity[]> {
    return this.repository.find({
      where: { client: Equal(client) },
    });
  }

Unknown column 'createdAt' in 'where clause'

When I look at the reported query in logs, it prints something like:

stack: [
{
code: 'ER_BAD_FIELD_ERROR',
errno: 1054,
sqlState: '42S22',
sqlMessage: "Unknown column 'createdAt' in 'where clause'",
sql: "SELECT VaultEntity.id AS VaultEntity_id, VaultEntity.created_at AS VaultEntity_created_at, VaultEntity.last_updated_at AS VaultEntity_last_updated_at, VaultEntity.uuid AS VaultEntity_uuid, VaultEntity.version AS VaultEntity_version, VaultEntity.salt AS VaultEntity_salt, VaultEntity.kms_id AS VaultEntity_kms_id, VaultEntity.key_name AS VaultEntity_key_name, VaultEntity.key_id AS VaultEntity_key_id, VaultEntity.key_version_id AS VaultEntity_key_version_id, VaultEntity.account_id AS VaultEntity_account_id, VaultEntity.client_id AS VaultEntity_client_id FROM vaults VaultEntity WHERE (VaultEntity.client_id = id = 30, createdAt = '2022-09-04 12:50:23.844', lastUpdatedAt = '2022-09-04 12:50:23.844', uuid = 'd703f71d-02c7-429a-a5b0-1bf2486cd788', name = 'xxxx-132', companyName = 'aaaaaa', firstName = 'xx', lastName = 'yyy', contactEmail = 'xxx', pinataKey = 'xxxx', pinataKeyTest = NULL, pinataSecret = 'xsecfret')
}
]

What we can see is that the Equal-operator will compare for all columns instead of querying the relation with its own Foreign key.

Expected Behavior

Querying associated entities in a relation should work like it was working properly in older Versions 0.2.x using the foreign keys.

Actual Behavior

The documented code to query relations leads to compiler issues as mentioned above.
The often mentioned workaround using Equal is not a good candidate for relations as described above and queries for columns which should not play a role here

> Unknown column 'createdAt' in 'where clause'

Steps to Reproduce

Setup two entities with a One2Many-Releation and query for the relation.

// example code has been provided above

My Environment

Dependency	Version
Operating System	MacOS 12.5.1
Node.js version	v16.13.1
Typescript version	4.7.4 + 4.8.2
TypeORM version	0.3.9

Additional Context

no additional context necessary

Relevant Database Driver(s)

DB Type	Reproducible
aurora-mysql	no
aurora-postgres	no
better-sqlite3	no
cockroachdb	no
cordova	no
expo	no
mongodb	no
mysql	yes
nativescript	no
oracle	no
postgres	no
react-native	no
sap	no
spanner	no
sqlite	no
sqlite-abstract	no
sqljs	no
sqlserver	no

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

• ✖️ Yes, I have the time, and I know how to start.
• ✅ Yes, I have the time, but I don't know how to start. I would need guidance.
• ✖️ No, I don’t have the time, but I can support (using donations) development.
• ✖️ No, I don’t have the time and I’m okay to wait for the community / maintainers to resolve this issue.

[ArielPrevu3D]

This is mentioned in #8616 , in the BREAKING CHANGES section. FindOptionsWhere only accepts primitive types, Buffer or operators.