TypeORM QueryBuilder：如何获取符合条件的关联关联Item数据？

Problem Context #

I've defined these entities:

Group { id: number; name: string; persons: Person[]; }
Person { name: string; items: Item[]; }
Item { name: string; active: boolean; }

Given an array of group IDs (groupIds), I want to get an array of Items that meet these criteria:

• The Item belongs to a Group whose ID is in groupIds
• The Item's active property is true

I tried building this QueryBuilder query:

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

But it only returns an array of Groups that contain valid Items (without the associated data). How can I modify this to get the Item array directly? Can this be done in a single query?

Solution #

The core issue here is that your current query returns Group entities, but you need a collection of Item entities instead. You absolutely can achieve this with a single query—here's how:

Option 1: Query Directly from the Item Repository (Recommended) #

This is the cleanest approach, as it targets the Item entity directly and uses the full association chain to filter by Group IDs:

this.itemRepository.createQueryBuilder('item')
  // Join to the associated Person (ensure Person has a `group` property linking to Group)
  .innerJoin('item.person', 'person')
  // Join from Person to its parent Group
  .innerJoin('person.group', 'group')
  // Filter for active Items
  .where('item.active = :active', { active: true })
  // Filter for Groups in your target ID array
  .andWhere('group.id IN (:...groupIds)', { groupIds })
  // Get the array of Item entities
  .getMany();

Key Notes:

• Repository Choice: Starting with itemRepository ensures the query returns Item[] directly, which matches your end goal.
• Association Setup: You'll need to make sure your Person entity has a defined association to Group (e.g., a @ManyToOne decorator for a group: Group property) to traverse the chain from Item → Person → Group.
• IN Clause Syntax: Using :...groupIds is TypeORM's recommended way to pass array parameters for IN queries—it handles array formatting automatically.

Option 2: Query from Group Repository (If Required) #

If you must start the query from groupRepository for some reason, you can explicitly select only Item data and retrieve raw results (note: this returns plain objects, not full Item entity instances):

this.groupRepository.createQueryBuilder('group')
  .innerJoin('group.persons', 'person')
  .innerJoin('person.items', 'item')
  // Select only the fields you need from Item
  .select(['item.id', 'item.name', 'item.active'])
  .where('item.active = :active', { active: true })
  .andWhere('group.id IN (:...groupIds)', { groupIds })
  // Get raw objects instead of Group entities
  .getRawMany();

Your original query returned Groups without associated data likely because while you used innerJoinAndSelect for items, the query was still structured to fetch Group entities, and TypeORM might not have loaded the nested associations correctly—but since you need Items anyway, Option 1 is the optimal path.

内容的提问来源于stack exchange，提问作者sirzento