如何使用 TypeORM 查询多对多关系 [英] How to query a Many-to-Many relation with TypeORM

问题描述

Note 与 Subject

查询它的最佳方法是什么?我想想写以下内容，以便在给定的笔记上记录所有主题:

What is the best way to query it? I would like to write the following to get all the subjects on a give note:

  const subjectRepo = connection.getRepository(Subject);
  const response = await subjectRepo.find({
    relations: ['notes'],
    where: { note }
  });

但这会返回所有主题，而不仅仅是笔记上的主题.

but that returns ALL of the subjects, not just the subjects on the 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"

<小时>

注意:您可以这样做:

---

Note: you can do this:

  return subjectRepo
    .createQueryBuilder('subject')
    .leftJoin('subject.notes', 'note')
    .where('note.id = :id', { id: note.id })
    .getMany();

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

But I am hoping for an approach with less strings, and explicit joining

推荐答案

你试图让TypeORM生成的SQL大致如下

The SQL you are trying to get TypeORM to generate is roughly as follows

SELECT *
FROM subject
JOIN subject_note AS jt on jt.subject_id = subject.id
WHERE jt.note_id = :id

1.repo.find

无法做到这一点

在撰写本文时，无法使用 repo.find(...) 在连接表上创建 where 子句.您可以加入 (doc) 但 where 子句只影响存储库的实体.

1. This is not possible with repo.find

At the time of writing, there is no way to create a where clause on a joined table using repo.find(...). You can join (doc) but the where clause only affects the entity of the repository.

TypeORM 也会默默地忽略无效的 where 子句，所以要小心那些.

TypeORM also silently ignores invalid where clauses, so be careful about those.

如果您想要给定 note 的所有 subject，您将需要使用查询构建器，就像您指出的那样，或者您需要重新选择注意对象及其关系.

If you want all the subject of a given note, you will either need to use a query builder, like you noted or you will need to re-select the note object with it's relationships.

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[]>;

// subject entity
@ManyToMany(() => Note, note => note.subjects)
@JoinTable()
notes: Promise<Note[]>;

使用这种惰性关系，您需要在每次使用之前await 以加载链接的笔记，但您无需为find 方法.

With this lazy relations, you will need to await for the linked notes to load before each usage, but you will not need to provide an array of relations to the find method.

const note = await noteRepo.find({
    where: { id: someId }
});
const subjects = await note.subjects

这篇关于如何使用 TypeORM 查询多对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！