Finding entity with with relation condition

Issue type:

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

Database system/driver:

[ ] cordova [ ] mongodb [ ] mssql [ X] mysql / mariadb [ ] oracle [ ] postgres [ ] cockroachdb [ ] sqlite [ ] sqljs [ ] react-native [ ] expo

TypeORM version:

[X ] latest [ ] @next [ ] 0.x.x (or put your version here)

Hello,

Is it possible to query an entity by placing a condition on one of its join columns?

For example, let’s say I have the user entity that is in relation to the photos entity. (one-to-many relationship)

Why can’t I use the following find configuration to filter the photos based an owning user’s property. ?

Eg. I want all the photos for the users that have the firstName=‘Dan’

let photos = await photosRepo.find({
  where: {
      user: {
          firstName: 'Dan'
      }
  }
})

Issue Analytics

State:
Created 4 years ago
Reactions:66
Comments:27 (4 by maintainers)

Top GitHub Comments

188reactions

alphardercommented, Dec 16, 2019

There’s a workaround for filtering based on relation fields for findOne()/find() methods that I’ve discovered recently. The problem with filtering related table fields only exists for ObjectLiteral-style where, while string conditions work perfectly.

Assume that we have two entities – User and Role, user belongs to one role, role has many users:

@Entity()
export class User {
  name: string;

  @ManyToOne(() => Role, role => role.users)
  role: Role;
}

@Entity()
export class Role {
  @OneToMany(() => User, user => user.role)
  users: User[];
}

Now we can call findOne()/find() methods of EntityManager or repository:

roleRepository.find({
  join: { alias: 'roles', innerJoin: { users: 'roles.users' } },
  where: qb => {
    qb.where({ // Filter Role fields
      a: 1,
      b: 2
    }).andWhere('users.name = :userName', { userName: 'John Doe' }); // Filter related field
  }
});

You can omit the join part if you’ve marked your relation as an eager one.

46reactions

jeffersonlicetcommented, May 15, 2021

Hi, is anyone working on this? I can take this issue and try to send a pull request.

Top Results From Across the Web

TypeORM, Query entity based on relation property

find / findOne doesn't allow filtering by nested relation properties. Go for QueryBuilder instead with something like const x = await repo.

Working with Relations - typeorm - GitBook

RelationQueryBuilder is a special type of QueryBuilder which allows you to work with your relations. Using it, you can bind entities to each...

Find Options | TypeORM Docs

Querying a column from an embedded entity should be done with respect to the hierarchy in which it was defined. Example: userRepository.find({ relations: ......

Class Repository<Entity> - typeorm

Finds entities that match given find options. Also counts all entities that match given conditions, but ignores pagination settings (from and take options)....

TypeORM Relations Tutorial - FULL details! - YouTube

We're going to cover all the typical table relationships you might ... using the Find API 39:34 - Querying entities with their relations...