TypeORM OneToMany filter in relations not effect to result

Question

I have two tables:

@Entity('Reviews')
class Review {
  ...
  @OneToMany((type) => MapCategory, map => map.review)
  public categories: MapCategory[];
}

And:

@Entity('MapCategories')
export class MapCategory {
  ...
  @ManyToOne(type => Review, (review) => review.categories)
  public review: Review;
}

When I try the filter on 'categories' but the result doesn't filter 'categories' following the key that I already push.

const items = await this.reviewRepository.findAndCount({
  relations: ['categories'],
  where: {
    categories: {
      id: 1
    }
  }
});

Answer 1

We need to use queryBuilder for cases like this since find doesn't allow filtering relations:

const items = await reviewRepository.createQueryBuilder("review")
    .leftJoinAndSelect("review.categories", "category")
    .where("category.id = :id", { id })
    .getManyAndCount()

Answer 2

I prefer to avoid query builder when possible.

There's a workaround for filtering based on relation fields for findOne()/find() methods that I've discovered recently. The problem with filtering related table fields only exists for ObjectLiteral-style where, while string conditions work perfectly.

Assume that we have two entities – User and Role, user belongs to one role, role has many users:

@Entity()
export class User {
  name: string;

  @ManyToOne(() => Role, role => role.users)
  role: Role;
}

@Entity()
export class Role {
  @OneToMany(() => User, user => user.role)
  users: User[];
}

Now we can call findOne()/find() methods of EntityManager or repository:

roleRepository.find({
  join: { alias: 'roles', innerJoin: { users: 'roles.users' } },
  where: qb => {
    qb.where({ // Filter Role fields
      a: 1,
      b: 2
    }).andWhere('users.name = :userName', { userName: 'John Doe' }); // Filter related field
  }
});

You can omit the join part if you've marked your relation as an eager one.