fix: better support of one-to-one and many-to-one relation-based properties in where clauses

[imnotjames]

Description of change

updates the QueryBuilder to support building WHERE clauses via ObjectLiterals that go across relations.

Most of this was done to migrate the whereInIDs over to using native QueryBuilder features, and abstract the query compilation away from the QueryBuilder interface.

for example

const postsWithFoo = await connection.createQueryBuilder(Post, "post")
            .select("post.id")
            .leftJoin("post.category", "category_join")
            .disableEscaping()
            .where({
                "category": {
                    "name": "Foo"
                }
            })
            .getMany()

This also applies to FindOptions

const postsWithFoo = await connection
  .getRepository(Post).findOne({ where: { "category": { "name": "Foo" } } })

Fixes #1847
Fixes #2707
Fixes #7251
Fixes #4906
Fixes #6264
Fixes #4837
Fixes #4787
Fixes #5323
Fixes #7583

Pull-Request Checklist

• Code is up-to-date with the master branch
• npm run lint passes with this change
• npm run test passes with this change
• This pull request links relevant issues as Fixes #0000
• There are new or updated unit tests validating the change
• Documentation has been updated to reflect this change
• The new commits follow conventions explained in CONTRIBUTING.md

[imnotjames]

I guess documentation needs to be updated as well.. EDIT: Just a little bit. Really, documentation should be rewritten in some areas..

[imnotjames]

Also this sort of continues a bit of an anti-pattern. It applies to the where clause directly in the case of *-to-many

We probably want to instead do existence checks to confirm the primary entity we're querying HAS the relations that match the where clause rather than the way we're doing it now.

However, that really breaks the assumptions users might have over how the query works..

Well, right now they assume it just doesn't work at all.

[gentritabazi]

This is big. Thanks.

where: {
    firstName: "Timber",
    lastName: "Saw",
    profile: {
        userName: "tshaw"
    }
}

[nick-lehmann]

Is there any timeline for when these changes will published in a new version of typeorm?

[afonsomatos]

This feature is a must, please release it soon enough.

[wiatrM]

Hello, is this feature already relased?

[clevercodenl]

when is this getting released ?

[wiatrM]

when is this getting released ?

It is already relased in 0.2.35 @

[afonsomatos]

If I have a *-to-many relationship, how do I do it?

following doesn't work:

const channels = await getRepository(models.Channel).find({
    relations: ["users"],
    where: {
        users: {
            id: userId
        }
    }
});

[codepushr]

True, one-to-many fails sadly...

[NenadJovicic]

also OneToOne realtions query fails when using some field that is not id

I have this case

@Entity()
class Record {
  @OneToOne(() => Report, { nullable: false })
  @JoinColumn({ name: 'entry_report_id' })
  entryReport: Report;
}

@Entity()
class Report {
  @ManyToOne(() => Record)
  record: Record;
  @ManyToOne(() => User)
  pacient: User;
}

// and then, build query like this
return this.recordRepository.find({
      where: { 
        entryReport: { 
          pacient: { id: id } 
        } 
      }
});

[sgronblo]

I'm trying to understand why the restriction for one-to-many exists. Is the assumption that WHERE should always filter the primary entity instead of the relation entity? In my case I would like filter the relation entity by a nested WHERE.

[phil294]

I'm trying to understand why the restriction for one-to-many exists. Is the assumption that WHERE should always filter the primary entity instead of the relation entity? In my case I would like filter the relation entity by a nested WHERE.

Yes exactly, James explained this here / see previous comments over there. You most likely want a subquery for *toMany where clauses, and this is currently only possible manually using QueryBuilder

[sgronblo]

I see. Thanks for clarifying. It's a bit unfortunate you cannot do it, even if you are aware of the limitations.

[giovanni-orciuolo]

Reference

@phil294 could you show an example?

[phil294]

@giovanni-orciuolo check this comment and maybe also the entire thread.

In short, there's currently two ways to do this with TypeORM (if I didn't miss any recent advancements regarding this):

SELECT * FROM addresses a
JOIN contacts c ON c.address_id = a.id
WHERE a.city = 'Colorado'
AND c.name = 'John';

getRepository(Address).createQueryBuilder("a")
  .leftJoinAndSelect("a.contacts", "c")
  .where("a.city = :city", { city: 'Colorado' })
  .andWhere("c.name = :name", { name: 'John' })
  .getMany()

SELECT * FROM addresses a
JOIN contacts c ON c.address_id = a.id
WHERE a.id IN (
  SELECT a1.id FROM addresses a1
  JOIN contacts c1 ON c1.address_id = a1.id
  WHERE a1.city = 'Colorado'
  AND c1.name = 'John'
)

getRepository(Address).createQueryBuilder("a")
  .leftJoinAndSelect("a.contacts", "c")
  .where(qb => {
    const sub_query = qb.subQuery()
      .select("a1.id")
      .from(Address, "a1")
      .leftJoinAndSelect("a1.contacts", "c1")
      .where("a1.city = :city", { city: 'Colorado' })
      .andWhere("c1.name = :name", { name: 'John' })
      .getQuery()
    return "a.id IN " + sub_query
  })
  .getMany()

It's important to understand the difference between those two, as they return two different things: The first only ever one contact per address (John), the second one all contacts of each address.

I haven't manually tested those queries but in principle they should work, I'm using them myself in different contexts all the time.

Because it's not entirely clear which of these two versions should be the default, TypeORM does not offer to do this for you with find options, that's why relation-based find queries aren't supported for *toMany right now (see the linked issue for more details)

[giovanni-orciuolo]

@phil294 thank you so much for the examples, they are very clear. However, I do not understand the part where you concatenate a.id with sub_query. I get [object Object] as a result and the query doesn't work. If I do the concatenation with getSql(), then I don't get the parameters translated (they remain written as $1, $2 etc). How can I solve this?

[phil294]

@giovanni-orciuolo did you forget to .getQuery()? It returns a string. Please check the docs https://typeorm.io/select-query-builder#using-subqueries

[giovanni-orciuolo]

@giovanni-orciuolo did you forget to .getQuery()? It returns a string. Please check the docs https://typeorm.io/select-query-builder#using-subqueries

Thank you for the response, I forgot to update my comment. Indeed I forgot the getQuery call in my own code