typeorm: Where clause on related table does not work

Issue type:

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

Database system/driver:

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

TypeORM version:

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

Steps to reproduce or a small repository showing the problem:

I have a OneToOne relationship between a table called WsUser and WsUserAuthentication. I find that the query generator does not include a WHERE clause at all for this query:

this.entities.findOne(WsUser,
            {
                where: {
                    authentication: {
                        googleId: googleId
                    }
                },
                relations: ['authentication']
            });

However, this query works as expected and returns the records as expected:

this.entities.findOne(WsUserAuthentication,
            {
                where: {
                    googleId: googleId
                },
                relations: ['user']
            });

However, there are a lot of places in code where I am expecting to be able to filter on multiple columns from different tables that are joined together. Not all my queries are as easy to reverse as this one.

Do I perhaps need to use leftJoinAndSelect instead of relations? The documentation doesn’t really mention this, it just says that a join is an “Extended version of "relations".”

About this issue

Original URL
State: closed
Created 6 years ago
Comments: 16 (10 by maintainers)

Most upvoted comments

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.

alpharder on Dec 16, 2019

Thanks so much for the workaround, @alpharder! What typing would be used for qb? I tried QueryBuilder but that did not work.

bestickley on Mar 19, 2020