search

kipcole9 / money_sql

Money functions for the serialization of a money data type in Elixir
Other
28 stars 18 forks source link

Aggregate function treating null as zero amount #22

Closed kipcole9 closed 2 years ago

kipcole9 commented 2 years ago

@sergiotapia has expressed interest in aggregating NULL values as zero-amount money.

The current aggregate function is STRICT meaning that NULL values are not processed. In some cases it is desirable to return a zero-amount money rather than NULL.

Changing the current function to remove STRICT may have unexpected results and therefore a new function that explicitly has this behaviour may be required.

kipcole9 commented 2 years ago

On reflection I suspect this is not a fruitful path to explore.

  1. The current SUM function skips NULL rows (because it is STRICT) and returns NULL if there are no non-NULL values. This is consistent with expectations of the SUM function.

  2. In the case where no rows have non-NULL values then the money amount can be set to zero, but its not possible to set the currency code.

Therefore there is no practical path to return a zero money in the aggregate function.

It may be possible to set a default on the Ecto type :money_with_currency since its a parameterised type. I'll close this issue for now, feel free to re-open if you see an alternative approach.