I have a huge query that consists of 2 subqueries. Since I intend to use the subqueries at multiple places, I wanted to extract it as a function.

Here is the original query that works perfectly fine and returns expected results:

  public async findUsers(): Promise<UserEntity[]> {
    return this.createQueryBuilder('users')
        .andWhere('users.user_type = :userType', { userType: UserType.Parent })
        .andWhere(qb => {
          const subquery = qb
              .subQuery()
              .select('COUNT(*)')
              .from(EventEntity, 'e')
              .where('e.object_id = users.member_id')
              .andWhere('e.event_type = :eventType', { eventType: EventType.LOGIN })
              .getQuery();
          return subquery + '= 0';
        })
        .andWhere(qb => {
          const subquery = qb
              .subQuery()
              .select('COUNT(*)')
              .from(UserEntity, 'u1')
              .where('u1.primary_Id = users.member_id')
              .andWhere('u1.user_type = :userTypeChild', { userTypeChild: UserType.Child })
              .getQuery();
          return subquery + '= 0';
        })
        .getMany();
  }

However, when I try to put the subquery inside a function, it returns no results. Here is the query:

  public async findUsers(): Promise<UserEntity[]> {
    return this.createQueryBuilder('users')
        .andWhere('users.user_type = :userType', { userType: UserType.Parent })
        .andWhere(`(${this.subquerysql()}) = 0`)
        .andWhere(qb => {
          const subquery = qb
              .subQuery()
              .select('COUNT(*)')
              .from(UserEntity, 'u1')
              .where('u1.primary_Id = users.member_id')
              .andWhere('u1.user_type = :userTypeChild', { userTypeChild: UserType.Child })
              .getQuery();
          return subquery + '= 0';
        })
        .getMany();
  }

  private subquerysql(): string {
    const query = getConnection()
        .createQueryBuilder()
        .select('COUNT(*)')
        .from(EventEntity, 'e')
        .innerJoin('users', 'u','e.object_id = u.member_id')
        .andWhere(`e.event_type = 'LOGIN'`)
        .getSql();
    return query;
  }

I can't seem to figure why the query isn't working(when I put subquery inside a function). I am writing this inside userRepository