Consider the following example. We have a sales class that has instances with the following shape.

{
  date: '2019-04-21',
  total: 1050,
  salesperson: Ref(Class('users'), '1234')
}

Salespeople get a commission on each sale that is equal to 5% of the total. We're creating a page that can calculate the commission for a given salesperson between two dates. Eg. "How much commission did Susan earn between Jan 1st, 2019 and April 21st, 2019?"

First Try

A first attempt may simply group all sales by salesperson

CreateIndex({
  name: 'salesperson_commission',
  source: Class('sales'),
  terms: [{field: ['data', 'salesperson']}]
});

Filter(
    Map(
        Paginate(
            Match(Index('salesperson_commission'), Ref(Class('users'), '1234')),
            {size: 1000}
        ),
        ref => Get(ref),
    ),
    sale =>
        And(
            GTE(Select(['data', 'date'], sale), '2019-01-01'),
            LTE(Select(['data', 'date'], sale), '2019-04-21'),
        ),
);

The main problem with this index is that we're running Get(ref) on every sale associated with this salesperson. This could result in many reads and increase the cost of our query. Additionally, we still need to calculate the commission for each sale every time we run the query.

Second Try

To reduce the number of reads we can add values to the index. This removes the need to Get every instance and also allows us to reduce the search space by using after in our pagination.

CreateIndex({
  name: 'salesperson_commission',
  source: Class('sales'),
  terms: [{field: ['data', 'salesperson']}],
  values: [
    {field: ['data', 'date']},
    {field: ['data', 'total']}
  ]
});

Map(
  Filter(
    Paginate(
      Match(Index('salesperson_commission'), Ref(Class('users'), '1234')),
      { size: 1000, after: ['2019-01-01'] },
    ),
    values => LTE(Select([0], values), '2019-04-21'),
  ),
  values => Select([1], values),
)

This query returns an array of sales totals. All that's left is to sum them together and multiply by 5%.

Final Solution

Now we will pre-compute the commission for each sale using a binding

CreateIndex({
    name: 'salesperson_commission',
    source: {
        class: Class('sales'),
        fields: {
            commission: Query(
                Lambda(
                    'instance',
                    Multiply(Select(['data', 'total'], Var('instance'), 0.05)),
                ),
            ),
        },
    },
    terms: [{ field: ['data', 'salesperson'] }],
    values: [
      { field: ['data', 'date'] },
      { binding: 'commission' }
    ],
});

Add(
  Map(
      Filter(
          Paginate(
              Match(Index('salesperson_commission'), Ref(Class('users'), '1234')),
              { size: 1000, after: ['2019-01-01'] },
          ),
          values => LTE(Select([0], values), '2019-04-21'),
      ),
      values => Select([1], values),
  )
)

We are now able to sum all of the pre-computed commissions and return the total commission all using only a single read operation and minimizing bandwidth usage for the result.

Key Takeaways

• Try to keep the number of read operations to a minimum by avoiding using Get when possible.
• Use terms to sort indexes and reduce the search space
• Pre-compute values using bindings which can be as complex as required. The only limitation is that they reads/writes cannot be performed in bindings.

Extra Credit

How would you change the index definition if the commission structure changed to the following?

• $5 for sales up to $500
• $10 for sales up to $1,000
• $20 for any sale over $1,000