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?"
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.
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%.
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.
- Try to keep the number of
read
operations to a minimum by avoiding usingGet
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.
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