作者:zhouwenjun | 来源:互联网 | 2022-12-05 12:58
Note
与多对多关系 Subject
最好的查询方式是什么? 我喜欢写以下工作再上一个音符给予所有科目为:
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();
但是我希望使用更少的字符串和显式连接的方法
1> Clément Prév..:
您尝试让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惰性关系,但这会迫使您将两个实体中的类型都更改为Promise
// note entity
@ManyToMany(() => Subject, (subject: Subject) => subject.notes)
subjects: Promise;
// subject entity
@ManyToMany(() => Note, note => note.subjects)
@JoinTable()
notes: Promise;
有了这种惰性关系,您将需要await
在每次使用之前加载链接的注释,但是您无需为该find
方法提供一系列关系。
const note = await noteRepo.find({
where: { id: someId }
});
const subjects = await note.subjects