TypeORM find entities where entity.array contains id

Question

I am using NestJS + typescript. This is my entity:

@Entity()
export class YearCourse {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @ManyToMany(() => User)
  @JoinTable()
  admins: User[];
}

And now given user I would like to find all yearCourse entities where given user is it's admin.

Prototype looks like this:

async getByUser(user: User): Promise<YearCourse[]> {
  return this.yearCourseRepository.find({where: { admins.contains(user) }})
}

This obviously doesn't work. How to achieve this type of search?

Answer 1

To write more advanced SQL queries with TypeOrm, you can use the Query Builder.

Considering your admin id is stored as adminId (admin_id) in the User entity, it should look like this:

// query builder name ('year') is completely customisable
return this.yearCourseRepository.createQueryBuilder('year')
      // load "admins" relation (user entity) and select results as "admins"
      .leftJoinAndSelect('year.admins', 'admins')
      // search where users have the "user.id" as "adminId"
      .where('admins.adminId = :id', { id: user.id })
      // get many results
      .getMany();

I recommend you to write this directly in your repository file when using Query Builder for more cleanliness