CODEWITHSUNDEEP
mysqlfindtypeormquery-builderrelation
João Fernandes
João Fernandes

Reputation: 558

TypeORM: how to load relations with CreateQueryBuilder, without using JOINs?

I'm developing an API using NestJS & TypeORM to fetch data from a MySQL DB. Currently I'm trying to get all the instances of an entity (HearingTonalTestPage) and all the related entities (e.g. Frequency). I can get it using createQueryBuilder:

const queryBuilder = await this.hearingTonalTestPageRepo
      .createQueryBuilder('hearing_tonal_test_page')
      .innerJoinAndSelect('hearing_tonal_test_page.hearingTest', 'hearingTest')
      .innerJoinAndSelect('hearingTest.page', 'page')
      .innerJoinAndSelect('hearing_tonal_test_page.frequencies', 'frequencies')
      .innerJoinAndSelect('frequencies.frequency', 'frequency')
      .where(whereConditions)
      .orderBy(`page.${orderBy}`, StringToSortType(pageFilterDto.ascending));

The problem here is that this will produce a SQL query (screenshot below) which will output a line per each related entity (Frequency), when I want to output a line per each HearingTonalTestPage (in the screenshot example, 3 rows instead of 12) without losing its relations data. Reading the docs, apparently this can be easily achieved using the relations option with .find(). With QueryBuilder I see some relation methods, but from I've read, under the hood it will produce JOINs, which of course I want to avoid.

SQL result query

So the 1 million $ question here is: is it possible with CreateQueryBuilder to load the relations after querying the main entities (something similar to .find({ relations: { } }) )? If yes, how can I achieve it?

Upvotes: 0

Views: 5958

Answers (2)

Radomir Epur
Radomir Epur

Reputation: 434

You say that you want to avoid JOINs, and are seeking an analogue of find({relations: {}}), but, as the documentation says, find({relations: {}}) uses under the hood, expectedly, LEFT JOINs. So when we talk about query with relations, it can't be without JOIN's.

Now about the problem:

The problem here is that this will produce a SQL query (screenshot below) which will output a line per each related entity (Frequency), when I want to output a line per each HearingTonalTestPage

Your query looks fine. And the result of the query, also, is ok. I think that you expected to have as a result of the query something similar to json structure(when the relation field contains all the information inside itself instead of creating new rows and spread all its values on several rows). But that is how the SQL works. By the way, getMany() method should return 3 HearingTonalTestPage objects, not 12, so what the SQL query returns should not worry you.

The main question:

is it possible with CreateQueryBuilder to load the relations after querying the main entities

I did't get what do you mean by saying "after querying the main entities". Can you provide more context?

Upvotes: 0

Tomás Dias Almeida
Tomás Dias Almeida

Reputation: 31

I am not an expert, but I had a similar case and using:

const qb = this.createQueryBuilder("product");
        
// apply relations
FindOptionsUtils.applyRelationsRecursively(qb, ["createdBy", "updatedBy"], qb.alias, this.metadata, "");

return qb
    .orderBy("product.id", "DESC")
    .limit(1)
    .getOne();

it worked for me, all relations are correctly loaded.

ref: https://github.com/typeorm/typeorm/blob/master/src/find-options/FindOptionsUtils.ts

Upvotes: 3

Related Questions