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.