Need to include in query sum of amounts of relations

[mmaksymovych]

Hello, we are using this fantastic lib in our project, but faced a problem, and would like to get some advice on this and see if it even possible to achieve. We have 2 entities:

class Charge {
    amount: number
}

class Transaction {
    charges: Charge[]
}

We need to add filter that filters by the sum of charges amount. Something like this:

const queryBuilder = this.transactionRepository
      .createQueryBuilder('transaction')
      .leftJoinAndSelect('transaction.charges', 'charges')
      .addSelect(`SUM(charges.amount)`, totalAmount)
      .where('totalAmount = :totalAmount', { totalAmount})


return paginate(query, queryBuilder, {...})

As far as I know after some research it's possible to do with getRawMany, but as I know paginate is using getManyAndCount.

Are there any other options how can we achieve this and keep using paginate lib? Appreciate for any feedback.

[ppetzold]

Hi there 👋

This is currently not supported. But looks like a handy use case when using query builder.

Happy to accept a PR :) I think, it should be checkin the query builder for sub queries and then use getRawMany and getCount for pagination.

[mmaksymovych]

@ppetzold just fyi - it is supported, found solution with current version of library :)

const queryBuilder = this.transactionRepository
      .createQueryBuilder('transaction')
      .leftJoinAndSelect('transaction.charges', 'charges')
      .where((qb) =>
        (qb) =>
          qb.subQuery()
            .select('sum(c.amount)')
            .from(Charge, 'c')
            .where('c.transactionId = transaction.id')
            .getQuery() + ' = totalAmount',
        { totalAmount },
      );

return paginate(query, queryBuilder, {...})

this should work

[stek93]

Hi @ppetzold. First of all, awesome lib, well done! 👏🏻
I am using version: 8.1.2 and I have the following problem - aggregated column is not shown. Not sure if its actually related to this specific issue, but wanted to check nevertheless.

For example, this part of the code:

const queryBuilder = await this.customerRepository
      .createQueryBuilder('customer')
      .leftJoinAndSelect('customer.store', 'store')
      .leftJoinAndSelect('customer.referrals', 'referrals')
      .select([
        'customer.id',
        'customer.createdDate',
        'customer.updatedDate',
        'customer.firstName',
        'customer.lastName',
        'customer.email',
        'customer.referralCode',
        'customer.country',
        'store.id',
        'store.name',
        'COUNT(referrals.id) as referralCount',
      ])
      .where('customer.store = :storeId', { storeId: store.id })
      .groupBy('customer.id, store.id')

Doesn't show referralCount - even though it's shown if I execute the command getRawMany and console log it. This part of the code only shows what's actually present in the customer entity (basically all selected fields from the above minus referralCount).

return await paginate(query, queryBuilder, {
    sortableColumns: ['firstName', 'lastName', 'email', 'country'],
    filterableColumns: {
      firstName: [FilterOperator.ILIKE],
      lastName: [FilterOperator.ILIKE],
      email: [FilterOperator.ILIKE],
      country: [FilterOperator.EQ],
      createdDate: [
        FilterOperator.BTW,
        FilterOperator.LT,
        FilterOperator.LTE,
        FilterOperator.GT,
        FilterOperator.GTE,
      ],
    },
  });

Do you have any idea how to solve this or maybe plan to solve it in the future? Or maybe I am doing something wrong here? Thanks in advance! 🙂

[ppetzold]

hmm.. seems like a typeorm mapping issue. we use getManyAndCount.

just found this one: https://github.com/typeorm/typeorm/issues/255

would accept PR supporting getRawMany + getCount with some new config flag raw: true

[stek93]

Yeah, I've actually stumbled across a different set of problems with my approach so I had to add denormalized data in my table and now that is pretty much solved with a plain repository instance without having to use queryBuilder. But I'll take a look and if have time, create a PR to solve this abovementioned issue. 🙂

[minhlu-i]

This issue come from typeorm, so i don't think we need to fix/change/update it in this library.

Instead of, i think we should use some typeorm solution like here: https://pietrzakadrian.com/blog/virtual-column-solutions-for-typeorm

[rharkor]

typeorm give access now to a VirtualDecorator, no longer needed to deal with a workaround. See https://typeorm.io/decorator-reference#virtualcolumn

[pavlyutkin]

My solution (based https://pietrzakadrian.com/blog/virtual-column-solutions-for-typeorm) is as follows:

@VirtualColumn()
distance?: number | null;

const queryBuilder = this.profilesRepository
            .createQueryBuilder('profile')
            .leftJoin('profile.user', 'user')
            .addSelect(
                `ST_Distance(ST_SetSRID(ST_MakePoint(${user.latitude}, ${user.longitude}), 4326), user.location, true)`,
                'distance'
            )

SelectQueryBuilder.prototype.getManyAndCount = async function () {
    const { entities, raw } = await this.getRawAndEntities();
    const total = await this.getCount();

    const items = entities.map((entitiy, index) => {
        const metaInfo = Reflect.getMetadata(VIRTUAL_COLUMN_KEY, entitiy) ?? {};
        const item = raw[index];

        for (const [propertyKey, name] of Object.entries<string>(metaInfo)) {
            entitiy[propertyKey] = item[name];
        }

        return entitiy;
    });

    return [items, total];
};