Typeorm: Allow WHERE clause on joined columns.

[Techniv]

Issue type:

[ ] question
[ ] bug report
[x ] feature request
[ ] documentation issue

Hi!

As it said in the documentation of FindManyOptions#where:

Querying a column from an embedded entity should be done with respect to the hierarchy in which it was defined.

It is possible to extend this capability to the joined columns ?

For exemple with 2 entities:

@Entity()
class Contact {
  @PrimaryGeneratedColumn()
  id: number;
  @Column()
  name: string;
  @ManyToOne(type => Address)
  address: Address;
}

class Address {
  @PrimaryGeneratedColumn()
  id: number;
  @Column()
  street: string;
  @Column()
  city: string;
}

Make a request like this:

contactRepository.find({
  relations: [ 'address' ],
  where: {
    address: {
      city: 'Paris'
    }
  }
});

SQL:

SELECT `Contact`.`id` AS `Contact_id`
    `Contact`.`id` AS `Contact_id`
    `Contact`.`name` AS `Contact_name`
    `Contact`.`addressId` AS `Contact_addressId`
    `Contact_address`.`id` AS `Contact_address_id`
    `Contact_address`.`street` AS `Contact_address_street`
    `Contact_address`.`city` AS `Contact_address_city`
  FROM `contact` `Contact`
  LEFT JOIN `address` `Contact_address`
    ON `Contact_address`.`id` = `Contact`.`addressId`
  WHERE `Contact_address`.`city` = "Paris";

I don't have seen any other options in TypeORM documentation to apply where clauses on joined columns. If there another way I am interested about it.

Thank you in advance for your help.

[periman2]

@Techniv

The way I do it is by using query builder like this:

const findContract = await connection
.getRepository(Contract)
.createQueryBuilder("contract")
.innerJoin("contract.address", "address")
.where("address.city= :city", { city: "Paris" })
.getOne();

(innerJoin will NOT select the address entity so it will not be available in the contract.address received object, it will only query through the join, if you want to select it also then use leftJoinAndSelect instead of innerJoin)

Let me know if this works for you.

[Techniv]

Thanks for your help @periman2.
But my needed is to filter result with a deep where clause by the find (or findMany) method in a generic service (model agnostic) where conditions are given from HTTP query parameters.

So, I don't have any doubt about your solution with the query builder, but if it's possible, I prefer a solution where I don't need to make an ORM over an ORM. XD

I will remember your proposition in last resort. But if there are a way to integrate this feature in TypeORM, it will be cool.

Thank you again.

[periman2]

@Techniv

Well the cool thing about query builder is that it's just an object, you can pass it around and mutate it as you wish. Which means you can build generic methods that populate conditionally "qb.where" and "qb.andWhere" methods to the queryBuilder object using a requestDto model (which can have a number of filters and whatnot) . You can also extend this with any specific info you want to pass for specific models. That's how I'm actually using it in production.

I do understand the need for having this somehow ready for us in the typeorm functionality though. It would be awesome!

[SuitoYoshiki]

I am also eager for this function! It's cool.

[Meg4mi]

Same here, this function would be helpful.

[jun9358]

+1

[nadrane]

Would the maintainers be willing to accept a PR that implements this functionality? It's a pretty common use case. I want to avoid to dropping down to the query builder because it's verbose and isn't type-safe (unless perhaps it can be and I'm missing something?).

[Calamitizer]

I'm also strongly in support of this feature. I've developed an entire API using find, declaratively, instead of a query builder, only to find out that this extremely common use case is mysteriously absent.

I think it's important to recognize that different developers have different styles & are optimizing for different things when they choose one or the other. These are the reasons I prefer not to use query builders (in descending order of importance, why not):

A) It is _extremely_ clunky & illegible to use in functional, declarative programming. My team's _entire webapp_ is written functionally.
B) It's not (as) type-safe. Locking this down is extremely important to me, especially in such an intrinsically type-unsafe process as DB record-fetching.
C) It really can be quite verbose.

Here's an example of query builder used functionally, with just three query parameters compared by basic equals.

  private getRecordsByQueryParams = async (queryParams: QueryParams) =>
    Object.keys(queryParams)
      .reduce((qb, param, i) => {
        const queryClause = `${User.ALIAS}.${param} = :${param}`;
        const value = { [param]: queryParams[param] };

        return i === 0
          ? qb.where(queryClause, value)
          : qb.andWhere(queryClause, value);
      }, (await connection).getRepository(User).createQueryBuilder(User.ALIAS))
      .getMany();

Here's an actual API node from our app, with ten query parameters:

  private createQueryConditions = async ({
    id,
    type,
    name,
    geo,
    segment,
    industry,
    startIndex,
    stopIndex,
    stage,
    moment,
  }: QProps): Promise<FindManyOptions<AccountRecord>> => ({
    where: {
      ...(id === undefined ? {} : { id }),
      ...(type === undefined ? {} : { id: In(await this.getIdsForType(type)) }),
      ...(name === undefined ? {} : { name }),
      ...(geo === undefined ? {} : { geo }),
      ...(segment === undefined ? {} : { segment }),
      ...(industry === undefined ? {} : { industry }),

      ...(stage === undefined && moment === undefined
        ? {}
        : this.createMomentCondition({ stage, moment })),
    },

    ...this.createSliceConditions({ startIndex, stopIndex }),
  });

Can you imagine trying to cram the (relatively simple!) conditional logic into the reducer callback, conditioning on the value of param? This is really the issue that comes up with method-chaining APIs -- when you need to conditionally call-or-not-call one of those methods. Like d3, which someone reaaally needs to rewrite for modern web.

[Calamitizer]

Update: I did find a work-around for the issue in question here, although it's really hacky. I had the idea to use the Raw find operator to run an arbitrary query, e.g.

const condition: FindManyOptions<ParentRecord>  = {
  where: {
    child: Raw(() => `SUBSTRING_INDEX(SUBSTRING_INDEX(${fieldName}, ',', ${i}), ',', -1) = ${moment}`),
  },
};

Here, fieldName is the name in the join table for the child entity field you want to query on (you can see this by enabling logging and checking the query). But of course, this relies on using the field name directly, which is quite brittle since TypeOrm has its own dynamic naming system. Also, since child was a joined field, it turns out TypeOrm straight-up ignored it, so I had to add a dummy field to the parent entity like so:

  @Column({
    type: boolean,
    nullable: true,
    select: false,
  })
  dummyField!: null;

This is inelegant and error-prone... but it's the only timely alternative to rewriting large chunks of our API.

[Blacate]

+1

[vytautas-pranskunas-]

+1

[saudar82]

+1

[jd1386]

+1

[m4kamran]

+1

[LarsBekhof]

+1

[mehtamanan]

+1

[f1mp3r]

Since this is an essential feature, as many people here agree, if I (or someone else) submit a PR, will it be accepted?

[avin-kavish]

@pleerock please accept

[leandrodeveng]

+1

[ganshiqingyuan]

+1

[glennreyes]

I was just looking at the docs for this, and as a non-sql/orm expert I would find it super helpful if there was at least following:

• A note in the docs that this is currently not supported
• A TS hint that putting joined columns into the where clause are not allowed/supported

Instead, I assumed it's supported, because of false positives TS not complaining about it. /cc @pleerock.

Since this is an essential feature, as many people here agree, if I (or someone else) submit a PR, will it be accepted?

@f1mp3r PRs are always welcomed, feel free to open one. It likely depends on the tradeoffs the PR would introduce wether it will accepted or not.

Anyway, this project is awesome, thanks for the great work of all maintainers & contributors! ❤️

[zaro]

The lack of where conditions on relations , greatly diminishes the usefulness of find IMO

[artz]

If it's any motivation to knock this feature out, Sequelize has this. 😁

[Yasukity]

+1

[takamukut]

+1

[makoll]

+1

[imnotjames]

Per the original request - you missed @Entity() on your address but I created a test case:

        const addressRepository = connection.getRepository(Address);
        const contactRepository = connection.getRepository(Contact);

        const paris = await addressRepository.save({ city: 'Paris' });
        const london = await addressRepository.save({ city: 'London' });

        await contactRepository.save({ name: "Foo", address: paris });
        await contactRepository.save({ name: "bar", address: paris });
        await contactRepository.save({ name: "Tea", address: london });

        const result = await contactRepository.find({
            relations: [ 'address' ],
            where: {
                address: {
                    city: 'Paris'
                }
            }
        });
        expect(result.length).to.be.equal(2);

This returns the results:

[
  Contact {
    id: 1,
    name: 'Foo',
    address: Address { id: 1, city: 'Paris' }
  },
  Contact {
    id: 2,
    name: 'bar',
    address: Address { id: 1, city: 'Paris' }
  }
]

So I'm pretty sure this is actually implemented and has been for a while.

I'll be closing this issue, but please correct me if I'm misunderstanding and there's other requested features here that I don't understand.

---

Side note:

I'm not sure I understand why everyone is writing +1 to this - that's not helpful and just fills up the comments with cruft. If you want this done, either open a PR with info, add a :+1: to reaction, or add a MEANINGFUL COMMENT that adds to the conversation.

[imnotjames]

PS if anyone disagrees please comment with a reproducible example which shows that it's not working so I can reopen the issue.

[tchak]

It is not working for me. I am using a ManyToMany relation.

I tried with both relations and join options. I am getting EntityColumnNotFound: No entity column \"users\" was found. error in both cases.

I am using 0.2.28. Should I try 0.3.0 ?

@Entity()
export class Document {
  @PrimaryGeneratedColumn('uuid')
  id!: string;

  @ManyToMany(() => User, ({ documents }) => documents)
  users!: User[];
}

@Entity()
export class User {
  @PrimaryGeneratedColumn('uuid')
  id!: string;

  @ManyToMany(() => Document, ({ users }) => users)
  @JoinTable()
  documents!: Document[];
}

await documentRepository.find({
  relations: ['users'],
  where: {
    users: {
      id: '1'
    }
  }
});

await documentRepository.find({
  join: {
    alias: 'document',
    leftJoin: {
      'document.users': 'users',
    }
  },
  where: {
    users: {
      id: '1'
    }
  }
});

[ldiego08]

Also not working for me with the same error as @tchak. TypeORM seems to try to look for a column rather than going into the relationship.

@imnotjames can you provide an exact example of how should this be used? Or when you say here that this is working, do you mean using the query builder or in a specific version of the library? What code did you use to test this?

[imnotjames]

@ldiego08 In my comment above in this issue I have an example that I included that matches the example included in the first comment - along with the results. https://github.com/typeorm/typeorm/issues/2707#issuecomment-709495954

@tchak Thanks for the reproducible example. I'll take a look at that shortly.

[imnotjames]

Wonderful, replicated with that example.

[ggaabe]

So is this on track for getting fixed and implemented anytime soon?

[etomanon]

@imnotjames
When I change the string ('Paris') to a find options operator then it does not work:
Error: column was not found in the entity

import { IsNull, Not } from 'typeorm'

 relations: [ 'address' ],
  where: {
    address: {
      city: Not(IsNull())
      }
    }

[victiondev]

Is there any update on this?

[ngoding]

Hey, just found a solution. Not sure if it's best practice, but it works for me.

contactRepository.find({
  relations: [ 'address' ],
  where: {
    address: {
      city: 'Paris'
    }
  }
});

Change it to:

contactRepository.find({
  relations: [ 'address' ],
  where: qb => {
    qb.where('Contact__address.city = :city', {city: 'Paris'})
  }
});

[ldiego08]

@ngoding having to resource to that kind of syntax washes off all the elegance from the find options alternative. I rather use the regular query builder. At least, things would be consistent that way. 😅

[ngoding]

@ldiego08 Yeah agreed. For my case I was using findAndCount, just want to keep it short. 😄

[chinanderm]

Hey, just found a solution. Not sure if it's best practice, but it works for me.

contactRepository.find({
  relations: [ 'address' ],
  where: {
    address: {
      city: 'Paris'
    }
  }
});

Change it to:

contactRepository.find({
  relations: [ 'address' ],
  where: qb => {
    qb.where('Contact__address.city = :city', {city: 'Paris'})
  }
});

Thanks for this interim solution! Solved my problem. Looking forward to the bug fix.

[nebkat]

I'd like to implement this but we should probably discuss a more robust proposal first. Here are some questions remaining (and some of my proposed answers):

Important

1. Manual/non-relation/sub-query joins: Should there be a mechanism for adding conditions on joins that are not relations?
Selected relations are converted to joins in the background so currently its just a convenience function in EntityManager.find(), but how would the where object refer to a manual/non-relation/sub-query join?

If it is a joinAndMap the mapped property could be used, but that seems unnecessary for non-selected joins. Without the property however, there will be a typing issue for find(). Using the alias somehow could work but I think the alias should really be kept away from the ORM side of things (ideally it would be computed automatically for all joins).

Perhaps one solution would be to add an extra where parameter to QueryBuilder.join(), which would solve the typing issue, but then is this any different to just using the ON condition with an inner join? Is it then missing the point entirely?

Alternatively we could restrict to real relations only.

2. Unjoined relations: What should happen if a relation is referenced in the where object but is not joined?
The logical first step for this would be to make the where parameters be computed at the final step before execution, rather than as soon as they are added, so there is a chance to add the join later. But what if it is never added?*

Should we _a)_ throw an error, _b)_ do a non selecting join on the relation automatically or _c)_ ignore this clause altogether (unless it is an ID only and we have the foreign key columns)?

3. Find operators: How should find operators act on relation conditions?

qb.where({
    address: In(
        {city: 'Paris'}, {'city: 'Amsterdam'}
    )
})

In really shouldn't be used here, it's more of an OR condition, but technically it makes sense. I know there have been some other issues about OR since Brackets really isn't the optimal solution but this may just complicate it further. If an array is provided to qb.where() all the options are ORed, so should this be the special case if an array is used on a relation property?

This question can actually be extended to embedded columns as well, as it should all feel consistent.

Less Important

4. Non-metadata wheres: What is the behavior when QueryBuilder is used without entity metadata?
Currently if there is no metadata tied to the query builder and a where object is passed the keys of the object are just treated as columns. We can't do much about that since we have no info about relations, but there should still be an elegant way to apply conditions to joined columns.

If we use aliases, how do we differentiate between an alias and a column on the object? If we don't use aliases, should whatever alternative is chosen be the preferred method even when metadata is available?

Personally I think the non-metadata queries have caused a lot of unnecessary complication to the query builders and would be better off having their own simpler system (or even use an external library for query building?), but that is a discussion for another day. Until then, this problem remains.

5. One-to-many/many-to-many relations: What should the default behavior be?
When there are multiple joined rows per source row, we will simply filter away the joined rows that do not match? Is there a situation where we don't want to join multiple rows, but rather filter the source row by the existence of _some_ matching join row?

These queries are complicated enough as is... the two approaches are running multiple queries or grouping results to aggregate the joined relation into an array, neither of which is pretty.

6. Nested relations: What happens when there is a condition on a nested relation?
Are there special considerations needed here? All parent relations presumably must be joined?

7. Custom relation joins: What happen if a relation is joined differently than designed for a specific query?
Standard relation joining is done using the join columns only, is there something we should be careful of if the user intentionally performs a join of a relation column using different join direction or conditions or something strange like that? It probably doesn't matter, and I'm not sure why it would ever be done, but the possibility exists anyway.

This question does slightly bring back to the idea proposed in question 1, which is to have an additional where parameter to QueryBuilder.join(). Then if the user decides to do a strange relation join, the where conditions are set right there and there is no overlap with the "standard" relation join.

We could add a QueryBuilder.relation() method which guarantees the relation is joined using the "standard" method, and is assigned the appropriate alias for filtering from the overall where object.

Observations

8. Relation IDs: What should happen if a relation object is provided that includes the primary keys?

qb.where({
    address: {
        id: 1,
        city: Not('Paris')
    }
});

For performance we should definitely short circuit address.id to addressId in the main entity if it is already present as a foreign key, but we must then still join for the sake of checking that address.city is not "Paris"?

9. Relation IDs: What should happen if an ID value is passed?
If address: 1 is passed then we should act like EntityManager.update() and treat it as an ID of Address? Composite keys would be dealt with by the previous question. If manual/non-relation/sub-query joins are allowed, what happens?

---

Most of these questions probably have some obvious answer but perhaps someone could put together a more concrete spec so we know what to aim for? If you have any input or examples feel free to add them.

_As always, I share the frustration about the slow progress but please avoid spam comments so we can keep the discussion centered around the issue itself. When @pleerock and @imnotjames do eventually review we can have some useful information here._