CODEWITHSUNDEEP
many-to-manyquery-buildertypeorm
Tienus McVinger
Tienus McVinger

Reputation: 477

TypeORM many to many retrieve all relations

So, my issue may be a bit more complicated than the title suggests - I'm unsure how to describe this in a concise title.

Presume I have two entities with a many-to-many relation between them, Question and Category. In the database, the relations are defined in a join table. Question is the 'owning side' in this relationship - just like in this example: https://typeorm.io/#/many-to-many-relations/what-are-many-to-many-relations

Now comes the part I'm having difficulty with: I always want to return the category relations on a question, in case it has any. If I'm doing it through the repository, I can very easily do it with something like this:

let question = questionRepository.find(id, {relation: ['categories']};

it'll return the question with all of its categories.

This gets more difficult when I want to do it using the QueryBuilder. Reason being is, I'm trying to list questions with all their categories, based on an array of categories. So imagine the following data:

Questions:

  1. QuestionA
  2. QuestionB
  3. QuestionC

Categories:

  1. Category1
  2. Category2

Relations:

  1. QuestionA - Category1
  2. QuestionA - Category2
  3. QuestionB - Category1

Again, I want to fetch the questions with all their categories. And I'm trying to do this with the QueryBuilder by using an array of CategoryIDs in the where-clause. This is my current querybuilder:

let count = categoryIds.length;
let queryBuilder = questionRepository.createQueryBuilder('q')
    .leftJoin('q.categories', category)
    .where('category.id IN (:categoryIds), {categoryIds})
    .groupBy('q.id)
    .having(COUNT(DISTINCT category.id) = :count, {count})
    .getMany();

As far as retrieving all questions that contain the provided categories is concerned, this works fine. The problem here is I want to return all categories for the questions it finds.

So imagine if categoryIds were to only contain Category1, it would return both QuestionA and QuestionB. However it wouldn't return all their categories.

Even if I were to change .leftJoin('q.categories', category) to .leftJoinAndSelect('q.categories', category), it would only return Category1 in the categories array for both questions.

I have not been able to figure this one out, so any help would be greatly appreciated! Thanks.

Upvotes: 2

Views: 6249

Answers (1)

bato3
bato3

Reputation: 2815

It all depends on what you want to achieve. TypeORM gives you many options. Sometimes it is enough to use decorators in the definition of an entity.

let results = questionRepository.createQueryBuilder('q')
    .leftJoin('q.categories', 'cat', 'cat.id IN(:...categoryIds)', {categoryIds})
    .getMany();

In other cases, you must manually construct the ON clause.

let results = questionRepository.createQueryBuilder('q')
    .innerJoin('relations', 'r', 'q.id = r.queryId')
    .innerJoinAndMapMany('q.categories', 'categories', 'c', 
        'c.id = r.categoryId AND c.id IN(:...categoryIds)', {categoryIds})
    .getMany();

Upvotes: 2

Related Questions