typeorm search in relation without using query builder nodejs

Question

I try to search some field in relation using typeorm Here is my UserEntity

  @OneToOne(() => UserAuth, userAuth => userAuth.user, {
    eager: true,
    cascade: true,
    onDelete: 'CASCADE',
  })
  @JoinColumn({name:'id'})
  userAuth?: UserAuth;

In UserAuthEntity I have

  @PrimaryGeneratedColumn()
  id!: number;
  @Column()
  username: string;
  @OneToOne(() => UserEntity, user => user.UserAuth)
  @JoinColumn({ name: 'user_id' })
  user?: UserEntity;

example data in database

id	username
1	testUser

In my Query I use

this.userRepository.find({
      relations: [ 'userAuth' ],
      where: {
        userAuth: {
          username: 'testUser'
        }
      }
    }); //this one return null data

I always get null data but if I change username into id I got right data

 this.userRepository.find({
          relations: [ 'userAuth' ],
          where: {
            userAuth: {
              id: 1
            }
          }
        }); // this query return data

if I use query builder every work fine but is their anyway to make this query work ?

Answer 1

It looks like that find/findOne doesn't allow filtering by nested relation properties.

You should use QueryBuilder instead.

Check the example code below:

const username = 'testUser';

await this.ormRepository
    .createQueryBuilder("userEntity")
    .innerJoinAndSelect("userEntity.userAuth", "userAuth")
    .where("userAuth.username= :username", { username })
    .getMany();

And you can check more about this issue in this link reference