.setRelationLoadStrategy("query") missing

[vlrevolution]

Documentation Issue

What was unclear or otherwise insufficient?

Documentation for 0.3.0 (#8616) states the following:

Old ways of custom repository creation were deprecated.

added new option on relation load strategy called relationLoadStrategy.
Relation load strategy is used on entity load and their relations load when you query entities from the database.
Used on find* methods and QueryBuilder. Value can be set to join or query.
    join loads relations using SQL's JOIN
    query executes separate SQL queries for each relation
    
And QueryBuilder:

userRepository
    .createQueryBuilder()
    .setRelationLoadStrategy("query")

However I cannot locate any reference to function 'setRelationLoadStrategy' in the 0.3.0 codebase. Is this a mistake and is not implemented?

[Rainson12]

you are right, the documentation must be wrong. However I do not think that this implementation works at all. You can try setting the relationLoadStrategy to "query" by doing this:

const testObj = userRepository.createQueryBuilder()
testObj.expressionMap.relationLoadStrategy = 'query'

However when I joined together our data model i got the following error:
Too many tables; MariaDB can only use 61 tables in a join. Which indicates that its not using query strategy but joining everything instead. My code was looking like this:

const testObj = userRepository.createQueryBuilder('user')
testObj.expressionMap.relationLoadStrategy = 'query'
.leftJoinAndSelect('user.groups', 'groups')
.leftJoinAndSelect('user.music', 'music')
.leftJoinAndSelect('user.movies', 'movies')
[...]

[pleerock]

Yes looks like an issue. Feel free to contribute! Just add a new method to SelectQueryBuilder called setRelationLoadStrategy and set given strategy to the expressionMap.

[Rainson12]

@pleerock this alone doesnt fix it since even when setting it via the expressionMap, the data seems to be loaded using joins and not multiple queries

[pleerock]

oh, I see. Current implementation works only when using FindOptions. We have to make much more changes if we want this to be supported by QueryBuilder as well.

[vlrevolution]

Use this syntax, guys/girls (sucks that can't use querybuilder with it but this helped me do like a 100x increase in performance vs joins with 5 relations - tbh most of the stuff that querybuilder enables can be now achieved with .find too - just gotta be creative about it):

    let results = await this.repo.find({
      relations: {
        relationName: true, // Setting the relationName key to true in relations object forces to use query strategy!
      },
    });

[Ginden]

tbh most of the stuff that querybuilder enables can be now achieved with .find too

Lately I was thinking about adding find operators And and Or, adding multicolumn Raw to get ~99% of QueryBuilder functionality in find.

[hienngm]

@Rainson12 My workaround is using .setFindOptions to load the relations which need to use the query strategy. And chain it with join, select, etc. to perform a more complicated query.
ex:

I think it makes sense because It will be very confusing when we are using the keyword “join” (leftJoin or innerJoin) and somehow Typeorm changes it to seperate queries with .setRelationLoadStrategy .

[depyronick]

@Rainson12 My workaround is using .setFindOptions to load the relations which need to use the query strategy. And chain it with join, select, etc. to perform a more complicated query. ex: I think it makes sense because It will be very confusing when we are using the keyword “join” (leftJoin or innerJoin) and somehow Typeorm changes it to seperate queries with .setRelationLoadStrategy .

when using this workaround, you cannot do where on relation fields

[hienngm]

Actually, there are two ways to accomplish this. The first option is to pass the "where" condition into .setFindOption, like this:

  this.createQueryBuilder("post")
      .setFindOptions({
        relations: { experience: true, discount: { type: true } },
        relationLoadStrategy: "query",
        where: { discount: { type: { id: "1" } } },
      })

Alternatively, you could use a join and then chain it with .where, like this:

  this.createQueryBuilder("post")
      .setFindOptions({
        relations: { experience: true, discount: { type: true } },
        relationLoadStrategy: "query",
        // where: { discount: { type: { id: "1" } } },
      })
      .innerJoin("post.discount", "discount")
      .innerJoin("discount.type", "discountType")
      .where("discountType.id = 1")

Both methods produce the same behavior as using relationLoadStrategy in Repository. I hope you find it useful.

[depyronick]

Actually, there are two ways to accomplish this. The first option is to pass the "where" condition into .setFindOption, like this:

  this.createQueryBuilder("post")
      .setFindOptions({
        relations: { experience: true, discount: { type: true } },
        relationLoadStrategy: "query",
        where: { discount: { type: { id: "1" } } },
      })

Alternatively, you could use a join and then chain it with .where, like this:

  this.createQueryBuilder("post")
      .setFindOptions({
        relations: { experience: true, discount: { type: true } },
        relationLoadStrategy: "query",
        // where: { discount: { type: { id: "1" } } },
      })
      .innerJoin("post.discount", "discount")
      .innerJoin("discount.type", "discountType")
      .where("discountType.id = 1")

Both methods produce the same behavior as using relationLoadStrategy in Repository. I hope you find it useful.

In my use case, there are several andWhere and orWheres. I could not make it with the second approach. Since I am in need of use of Brackets, the first approach is not suitable for me.

The problem with the second approach is that because it does not join the tables, relation alias is not defined.

[scr4bble]

So what is the recommended solution for using the 'query' relationLoadStrategy in querybuilder?
We are having issues with relationLoadStrategy when being used by find*() methods so if we could switch to querybuilder and use the queried relations without application hanging, it would be great.
These issues are on a similar topic (relationLoadStrategy set to 'query' causing issues):

• Running simultaneous queries with relations stalls typeorm #4738
• Loading entities with query strategy and connection limit set to 1 for mysql driver will cause dead lock #9298

[reactuz]

@pleerock do you have any plans to implement this in near future?

[spanwair]

I guess you forgot to put it to schema.prisma file:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["relationJoins"]
}