Working with Monetary Amounts

For working with money, heap tables support a special field type called Money, which represents a semantically correct representation of monetary amounts. It has the following properties:

• Stores the amount along with the currency.

• Values are represented as instances of a special class Money, which provides methods for operations on money that are correct in terms of rounding and currency.

• Filtering and sorting methods for the table support special syntax and semantics for the money type.

Filtering

Filtering by the money type supports several options:

• By money as a whole - the amount considering the currency. This is the most "safe" option.

• By amount without considering the currency. This can be risky, but in skilled hands, it can be useful.

• Separately by currency.

Safe Filtering by Money Considering Currency

If a field money is declared in the table as Heap.Money(), you can "compare" it in filtering:

• With an instance of the Money class

  import { Money } from '@app/heap'
  await table.findAll(ctx, { where: { money: new Money(10, 'RUB')} })
  

• With a pair amount+currency in the form [10, 'RUB']

  await table.findAll(ctx, { where: { money: { $lte: [1000, 'RUB'] } } })
  

• With zero. Comparing with any other values of the amount without currency is unsafe.

  await table.findAll(ctx, { where: { money: { $gt: 0 } } })
  

In this mode (when we "address" the field directly during filtering), the search occurs within the fixed currency from the right side of the filter, except for the comparison with zero in the third option.

IN filtering is not supported in this mode when an array of values is specified on the right side. If you need to compare with a list of values, you can use comparison with the pseudo-field amount (see below).

Filtering Separately by Amount and Currency (Pseudo-fields amount and currency)

For cases where the developer knows what they are doing, fields of type Heap.Money support filtering separately by amount, separately by currency, and their combinations. For this, special child pseudo-fields amount and currency can be used in the filter. IN filtering by a list of values is supported for each of them, but standard comparison operators are not supported for currency.

• Only amount:

  await table.findBy(ctx, { money: { amount: { $gt: 100 } } })
  await table.findAll(ctx, { where: { money: { amount: [10, 20, 30, 40] } } })
  

• Only currency:

  await table.findBy(ctx, { money: { currency: 'USD' } })
  await table.findAll(ctx, { where: { money: { currency: ['RUB', 'EUR'] } } })
  

• When both fields are specified, it corresponds to two conditions combined via AND:

  await table.findBy(ctx, { money: { amount: [10, 20, 30, 40], currency: 'USD' } })
  await table.findAll(ctx, { where: { money: { amount: { $lt: 1000 }, currency: ['USD', 'EUR'] } } })
  

Sorting

If a field of type Heap.Money is used in the order property of the findAll method, the following principles apply:

• First, records are sorted by the currency code in alphabetical order (according to the chosen sort direction).
• Each currency group is sorted by the numerical value of the amount separately according to the chosen sort direction.

Sorting by amount without considering the currency, as well as separately by currency, does not make practical sense and is therefore not supported. If you need to obtain a strictly ordered list by amount, you should filter it by one currency using the pseudo-field currency.

await table.findAll(ctx, { where: { money: { currency: 'USD' } },
order: { money: 'desc' } })

Automatic conversion of values to a single currency for correct sorting is also not supported, but can easily be done in regular JS code if exchange rates are available.