typeorm - 如何使用 TypeORM 查询多对多关系

Question

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();

但我希望有一种更少字符串和显式连接的方法

Answer 1

您尝试让 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 relations但这会迫使您将两个实体中的类型更改为 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