How do I query for item based on id from many to many relationships in TypeORM

35 views Asked by At

I am attempting to query for notes inside a search function, however the notes may have a many to many relationship with groups and I need to first restrict the search by group id.

Notes entity:

@Entity("note")
export class NoteEntity {
  @PrimaryGeneratedColumn("uuid")
  id: string;

  /**
   * Associations
   */
  @ManyToOne(() => UserEntity, (user) => user.notes)
  user: UserEntity;

  @ManyToMany(() => GroupEntity, (group) => group.notes)
  groups: GroupEntity[];

  @ManyToOne(() => MediaEntity, (media) => media.notes, { nullable: true })
  media: MediaEntity;

  @OneToMany(() => CommentEntity, (comment) => comment.note, { nullable: true })
  comments: CommentEntity[];
}

group entity

@Entity("group")
export class GroupEntity {
  @PrimaryGeneratedColumn("uuid")
  id: string;

  /**
   * Associations
   */
  @ManyToOne(() => UserEntity, (user) => user.groups)
  user: UserEntity;

  @ManyToMany(() => NoteEntity, (note) => note.groups)
  @JoinTable()
  notes: NoteEntity[];

  @ManyToMany(() => MediaEntity, (media) => media.groups)
  @JoinTable()
  medias: MediaEntity[];
}

What I have tried

The function below returns the notes by search query correctly, however I have not been able to figure out how to isolate the notes by the group id from the many to many relationships.

public async search(userId: string, query: { search: string }) {
  const queryBuilder = this.dataSource.createQueryBuilder(NoteEntity, "note");

  queryBuilder
    .where("note.user = :id", { id: userId })
    
    // tried adding various andWhere clauses and join

    .andWhere("LOWER(note.title) LIKE :s OR LOWER(note.note) LIKE :s", {
      s: `%${query.search.toLocaleLowerCase()}%`,
    });

  return await queryBuilder.getMany();
}

Thank you for any assistance in advance.

0

There are 0 answers