typeorm querybuilder: select relation of relation only

user_10122822 0
Viewed 18589

So I have those entities:

Group {
    id: number;
    name: string;
    persons: Person[];
}

Person {
    name: string;
    items: Item[];
}

Item {
    name: string;
    active: boolean;
}

What I have avaible: an array of group id's groupIds.

My Goal: To get an array of Item but only the one that are containt in a group that group.id is within the group array I have and also that have the property active true.



I tried to build a querybuilder like this:

this.groupRepository.createQueryBuilder('group')
                      .innerJoin('group.persons', 'persons')
                      .innerJoinAndSelect('persons.items', 'items')
                      .where({'items.active': true, 'group.id': In(groupIds)})
                      .getMany();

But I only get an array of group (without any relations) that have a valid item in it.

What do I Need to Change, if this is even possible with a single query?

typeorm nestjs node.js sql
1 Answers

Try to use this:

 this.groupRepository.createQueryBuilder('group')
   .leftJoinAndSelect('group.persons', 'persons')
   .leftJoinAndSelect('persons.items', 'items')
   .where('items.active =:active', {active: true})
   .andWhere('group.id IN (:...groupIds)', {groupIds})       
   .getMany();
user_130700920
Related