I am using TypeORM with PostgreSQL and I have the following query:

const queryBuilder = this.usersRepository
      .createQueryBuilder('user')
      .select([
        'user.id',
        'user.username',
      ])
      .leftJoinAndSelect(
        'user.referralBonusTransactions',
        'referral_bonus_transaction'
      );

return paginate((query, queryBuilder, paginateConfig);

It’s returning a paginated result with the package nestjs-paginate

In the user entity referralBonusTransactions is a one to many relationship like so:

  @OneToMany(
    () => ReferralBonusTransaction,
    (referralBonusTransaction) => referralBonusTransaction.user
  )
  referralBonusTransactions: ReferralBonusTransaction[];

On the ReferralBonusTransaction entity there is no foreign key for user id, just a column that stores the user id.

  @RelationId((referralBonus: ReferralBonus) => referralBonus.user)
  @Column({ type: 'uuid', nullable: false })
  userId: string;

The previous query works as expected and for each user it stores the referral bonus transaction in the entity property of the same name. This is an array of objects that map to referralBonusTransaction entity. What I want is to aggregate the amount property of this objects to get the total of the referrals for each user.

I can do it in SQL like so:

SELECT u.id, u.username, SUM(rbt.rebate_amount) AS sum FROM "user" u
LEFT JOIN referral_bonus_transaction rbt
ON rbt.user_id = u.id
GROUP BY u.id, u.username;

but I can’t figure out how to do it in TypeORM.

I think that the problem must be with GROUP BY and paginate but I can’t get it right.