请问TypeORM多对多关系如何进行分页查询？

Question

我使用TypeORM的ManyToMany装饰器进行多对多关联，关键代码为

// person.entity.ts
@JoinTable({
    name: 'person_group',
    joinColumn: {
      name: 'personId',
      referencedColumnName: 'id',
    },
    inverseJoinColumn: {
      name: 'groupId',
      referencedColumnName: 'id',
    },
})
@ManyToMany(() => GroupEntity, group => group.persons, { cascade: ['insert', 'update'] })
public groups: Array<GroupEntity>;

// group.entity.ts
@ManyToMany(() => PersonEntity, person => person.groups, { cascade: ['insert', 'update'] })
public persons: Array<PersonEntity>;

生成了3张表，分别是person, group, person_group，我现在需要分页查询group id为1的所有用户，直接使用sql查询为

select * from person where person.id in ( select personId from person_group where person_group.groupId = 1) limit 0,10

请问怎么使用TypeORM的API进行这种查询呢？

Answer 1

const [list, count] = await this.entityManager.createQueryBuilder(PersonEntity, 'person')
    .andWhere(qb => {
        const subQuery = qb.subQuery().from('person_group', 'person_group')
          .select('person_group.personId')
          .where('contact_group.groupId = :groupId')
          .getQuery();
        return 'person.id in ' + subQuery;
    })
    .limit(limit)
    .offset(offset)
    .setParameter('groupId', id)
    .getManyAndCount();