TypeORM, Query entity based on relation property

Question

I would like to query an entity based on a related property, for instance:

const x = await repo.findOne({ name: 'foo', parent: { name: 'foo' }});

but it aways returns a null when I query by its related parent

I alread added : relations: ['parent'], already set relation as {eager:true}

When I Query by parent: {id: X} it works. but I must query by its name.

What should I do to get this query working in TypeORM

It would be similar to:

select * from entity inner join parent ... where entity.name = 'foo' and parent.name = 'foo'

Answer 1

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

const x = await repo.createQueryBuilder("foo")
    .innerJoinAndSelect("foo.parent", "parent")
    .where("parent.name = :name", { name })
    .getOne()

Check here for a similar question.

Answer 2

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.

Answer 3

I know this answer would be irrelevant as at when it's needed but for further purposes. the typeorm^0.3 above can now make such query directly just as you want it.