Typeorm - Converting SQL Left Join one to one relation into typeorm query builder

Question

I have following entities in a postgresql database using Typeorm:

@Entity('issuer')
export class Issuer {
  @PrimaryColumn()
  issuer_id: string

  @OneToOne(() => UserData, { cascade: true })
  @JoinColumn({ name: 'issuer_id', referencedColumnName: 'public_address' })
  u_data: UserData

  @BeforeInsert()
  newid() {
    this.issuer_id = this.u_data.public_address
  }
  
  ...remaining columns...
}

@Entity('user_data')
export class UserData {
  @PrimaryColumn({ type: 'varchar', unique: true })
  email: string

  @Column({ type: 'varchar', nullable: false, unique: true })
  public_address: string

  ...remaining columns...
}

Above in the Issuer entity, I am doing a small trick to be able to make a key both primary and foreign, issuer_id column, which is primary key of Issuer and foreign key of UserData which refers to public_address column of UserData. I wanna join both entities, and I am able to do it with the following query:

SELECT *
FROM user_data
LEFT OUTER JOIN issuer ON issuer.issuer_id = user_data.public_address
WHERE user_data.email = $1

I am unable to convert this simple SQL code into Typeorm query builder. Here is my failed attempt:

await this.userRepository
  .createQueryBuilder('user')
  .leftJoin('user.public_address', 'issuer')
  .where('user.email = :email', { email })
  .getOne()

Here is the error I am getting:

TypeORMError: Relation with property path public_address in entity was not found.

Answer 1

It seems when trying to left join (right join doesn't exist on typeorm) from an entity that has no direct relation to its relative, leftJoinAndSelect function should be used with a condition:

return await this.userRepo
        .createQueryBuilder('user')
        .leftJoinAndSelect(Issuer, 'issuer', 'user.public_address = issuer.issuer_id')
        .where('user.email = :email', { email })
        .getRawOne()