查询它的最佳方法是什么? 我喜欢写以下工作再上一个音符给予所有科目为:
const subjectRepo = connection.getRepository(Subject); const response = await subjectRepo.find({ relations: ['notes'], where: { note } });
但这会返回所有主题,而不仅仅是注释中的主题。
Reln定义为:
@ManyToMany(() => Subject, (subject: Subject) => subject.notes) subjects: Subject[];
- 和 -
@ManyToMany(() => Note, note => note.subjects) @JoinTable() notes: Note[];
执行的查询是:
SELECT "Subject"."id" AS "Subject_id", "Subject"."name" AS "Subject_name", "Subject"."description" AS "Subject_description", "Subject"."createdDate" AS "Subject_createdDate", "Subject"."updatedDate" AS "Subject_updatedDate", "Subject"."notebookId" AS "Subject_notebookId", "Subject"."measurementsId" AS "Subject_measurementsId", "Subject_notes"."id" AS "Subject_notes_id", "Subject_notes"."content" AS "Subject_notes_content", "Subject_notes"."notedAt" AS "Subject_notes_notedAt", "Subject_notes"."createdDate" AS "Subject_notes_createdDate", "Subject_notes"."updatedDate" AS "Subject_notes_updatedDate", "Subject_notes"."notebookId" AS "Subject_notes_notebookId" FROM "subject" "Subject" LEFT JOIN "subject_notes_note" "Subject_Subject_notes" ON "Subject_Subject_notes"."subjectId"="Subject"."id" LEFT JOIN "note" "Subject_notes" ON "Subject_notes"."id"="Subject_Subject_notes"."noteId"
注意:你可以这样做:
return subjectRepo .createQueryBuilder('subject') .leftJoin('subject.notes', 'note') .where('note.id = :id', { id: note.id }) .getMany();
但我希望采用较少的字符串和明确的连接方法
写回答关注邀请回答
提问于 2018-09-10
如何查询与TypeORM的多对多关系?
写回答关注
1 个回答
热门排序
用户回答回答于 2018-09-10
您试图让TypeORM生成的SQL大致如下
SELECT * FROM subject JOIN subject_note AS jt on jt.subject_id = subject.id WHERE jt.note_id = :id
1.这是不可能的 repo.find
在编写本文时,无法where使用连接表创建子句repo.find(...)。您可以join(doc)但该where子句仅影响存储库的实体。
TypeORM也默默地忽略了无效的where子句,所以要小心。
2.重新选择备注实体
如果您想要所有subject给定的note,您将需要使用查询构建器,就像您所指出的那样,或者您需要使用它的关系重新选择注释对象。
note = await noteRepo.find({ relations: ['subjects'], where: { id: note.id } }); const subjects = note.subjects
3.使用TypeORM惰性关系
如果要避免重新选择,则需要使用TypeORM Lazy关系,但这会强制您将两个实体中的类型更改为Promise
// note entity @ManyToMany(() => Subject, (subject: Subject) => subject.notes) subjects: Promise<Subject[]>; // subject entity @ManyToMany(() => Note, note => note.subjects) @JoinTable() notes: Promise<Note[]>;
使用这种惰性关系,您需要await在每次使用之前加载链接的注释,但是您不需要为该find方法提供一系列关系。
const note = await noteRepo.find({ where: { id: someId } }); const subjects = await note.subjects