How to query a Many-to-Many relation with TypeORM

Question

Note has a many-to-many relationship to 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 defined as:

  @ManyToMany(() => Subject, (subject: Subject) => subject.notes)
  subjects: Subject[];

-- and --

  @ManyToMany(() => Note, note => note.subjects)
  @JoinTable()
  notes: Note[];

The executed query is:

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

Answer 1

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. 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 also silently ignores invalid where clauses, so be careful about those.

2. Re-select the note entity

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. Use TypeORM lazy relations

If you want to avoid re-selection, you need to use TypeORM Lazy relations but this forces you to change the type in both entities to be a Promise

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

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

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