How to get accounts where balance > 0 using ex_money?

Questions / Help
1

Trying to find all accounts with a non-zero balance using ex_money in Postgres.

zero_money = Money.new(:USD, 0)

# My schema field: field :current_balance, Money.Ecto.Composite.Type
Repo.all(
  from(b in BankAccount,
    where: b.user_id == ^user.id,
    where: b.current_balance > ^zero_money
  )
)

Error I’m getting:

** (DBConnection.EncodeError) cannot encode anonymous tuple {“USD”, Decimal.new(“0”)}. Please define a custom Postgrex extension that matches on its underlying type:
use Postgrex.BinaryExtension, type: “typeinthedb”
(postgrex 0.17.5) lib/postgrex/type_module.ex:150: Postgrex.DefaultTypes.encode_tuple/3
(postgrex 0.17.5) lib/postgrex/type_module.ex:947: Postgrex.DefaultTypes.encode_params/3
(postgrex 0.17.5) lib/postgrex/query.ex:75: DBConnection.Query.Postgrex.Query.encode/3

I’ve reviewed the docs for both ex_money and ex_money_sql but can’t find any mentioned of where queries.

2

This question highly depends on the type you used for storing the currency.

This part of readme covers deep and wide on how to deal with money type in your database: GitHub - kipcole9/money_sql: Money functions for the serialization of a money data type in Elixir

3

I ran this command per the docs:

execute("CREATE TYPE public.money_with_currency AS (currency_code varchar, amount numeric);")

And my schema field:

field :current_balance, Money.Ecto.Composite.Type

Tried doing a search in the readme file for: where: and couldn’t find anything. Appreciate any pointers.

4

Are you running the creation of this composite type?

defmodule MoneyTest.Repo.Migrations.CreateLedger do
  use Ecto.Migration

  def change do
    create table(:ledgers) do
      add :amount, :money_with_currency
      timestamps()
    end
  end
end
5

Yes I did all that, confirmed.

I found a random Github issue with the solution:

zero_money = Money.new(:USD, 0)
Repo.all(
  from(b in BankAccount,
    where: b.user_id == ^user.id,
    where: b.current_balance > type(^zero_money, b.current_balance)
  )
)

SELECT b0.“id”, b0.“name”, b0.“current_apr”, b0.“promotional_apr_expiration_date”, b0.“real_apr”, b0.“payment_due_date”, b0.“statement_closing_date”, b0.“current_balance”, b0.“minimum_payment”, b0.“user_id”, b0.“inserted_at”, b0.“updated_at” FROM “bank_accounts” AS b0 WHERE (b0.“user_id” = $1) AND (b0.“current_balance” > $2::money_with_currency) [“d37defd7-1576-4198-b2ac-815b67b2fee2”, Money.new(:USD, “3000”)]

So the type keyword is from Ecto, and it’s casting the zero_money variable to the same type as the b.current_balance column?

Is that how this is working?

3 Likes
6

This is an interesting topic in itself, this is the first time I see this kind of composite map types. I would guess digging into postgres side on how they work would be beneficial for you, at the end of the day ecto.sql is just a DSL query builder.

10

I’m very late to this thread, apologies. I’m glad you got a resolution - and I agree that having to use the type/2 macro isn’t very ergonomic. I’ll see if following the error messages advice and building a binary extension can make this better.

1 Like
11

And I should also define custom comparison operators because otherwise Postgres will happily compare two money_with_currency types, even if they are of a different currency. This because Postgres, like the BEAM, has built-in term ordering.

1 Like
12

I’ve sent a proposal to the Ecto mailing list about deriving the database type from an Ecto.ParameterizedType (like Money.Ecto.Composite.Type) and casting to it since Ecto already knows the type involved.

Unless that proposal is accepted (and I consider it a low change of success) it will be necessary to either use:

# as you are already doing
where: b.current_balance > type(^Money.zero(:USD), b.current_balance)

# or
where: b.current_balance > fragment("?::money_with_currency", ^Money.zero(:USD))

Note that Money.zero/1 is an explicit function for returning a 0 amount money in some currency.

I will go ahead and write some custom operators for :money_with_currency since there is still the risk of comparing money of different currencies which is not semantically correct.

3 Likes
13

Thank you @kip!

14

It looks like there isn’t going to be a path to automatically casting the query param which means that the type/2 or fragment/2 approach is going to be required for the foreseeable future.

However I had neglected to mention that there are some helpers in the Money.Ecto.Query.API module. The documentation needs improving but you should find some of them help with the kind of queries you need.

1 Like
15

Looks like this will do the trick. I’ll give it a try later today:

# From the examples:
Organization
|> where([o], amount_ge(o.payroll, 0))
16

@kip out of curiosity, have you considered supporting micro-dollars? I think this is a must for anyone creating currency-agnostic applications.

17

I’m curious @thiagomajesk, why do you think that representation is a “must”?

18

This “micro-dollar” abstraction seems “hacky” to me. This article mentions storing the value as bigint but ex_money stores the amount as a string.

In the referenced Google article, they using micro-dollar for their API return type. This makes sense for an API since a naive client will expect money to be a number, not a string, and it preserves a moderate amount of precision when we multiply by 1 million.

However, for internal storage and computation, using strings for persistence and Decimal for the amount results in as much precision as anybody could want.

19

Otherwise, you have to deal with different formats… The micro-dollars notation stores it without that information, it makes the data “agnostic” and formatting becomes a view concern.

I don’t have a lot of practical experience in this field, but I have read many articles in the past that don’t recommend using decimals/floats/doubles to represent currency - I think it has to do with precision.

20

Floating point yes, this includes both float and double. Decimal however can store the number in the exact form, it is widely used to deal with currency.

1 Like
21

That only works until a currency comes around, which requires higher precision. E.g. some of those crypto currencies subdivide to tiny fractions.

1 Like
22

Thanks, @D4no0 and @LostKobrakai it seems I was putting decimals, floats, and doubles in the same bag. If someone stumbles across this thread, here’s a good article comparing the different approaches: Storing currency values: data types, caveats, best practices · cardinalby. I still think it might be useful to support micro-unit notation if you are looking into making the ecto type of the library parameterized though.

23

@thiagomajesk, its a good conversation and always appropriate to talk about storage of and calculations on money. Here are some of my thoughts on microdollars, floating point and serialising money in general.

Floating point is a bad idea for representing money

As you noted, there is a general recommendation not to use floats/doubles. Floating point is, by definition, making tradeoffs between size, performance and precision and accuracy that are different to the requirements for money. At the simplest level, floating point representations have issues with precision and associativity. For example:

# Accuracy cannot be guaranteed
iex> 0.1 + 0.2
0.30000000000000004

# Not associative
iex> a = 1.0000001
iex> b = 2.0000002
iex> c = 3.0000003
iex> (a + b) + c
6.0000006
iex> a + (b + c)
6.000000600000001

Integers aren’t perfect either

Integers are a better fit, they can at least represent a set of real numbers up to a certain precision (limited by the machine word size typically). It works appropriately (for money) for addition, subtraction and multiplication - but we still have issues with division. A good example is what happens when we divide 10 by 3:

# The infinite series of 3.3333...... is rounded
iex> 10 / 3
3.3333333333333335
# Integer division won't round trip
iex> div(10, 3) * 3
9

The issue of integer division isn’t solved by microdollars. This is one of the reasons why the Money.split/2 exits. It does division but also returns any remainder so that (money / number) * number + remainder == money is a guarantee.

Decimals

Decimal implementations, in computers, make different tradeoffs to precision, scale and speed. Whereas floating point emphasises speed, decimal tends to prioritise precision and scale. In many implementations, including the Decimal, the precision can be arbitrarily large but defaults to 28 digits.

iex> Decimal.Context.get()
%Decimal.Context{
  precision: 28,
  rounding: :half_up,
  flags: [],
  traps: [:invalid_operation, :division_by_zero]
}

Note too the rounding: :half_up. There are several rounding strategies in Decimal and its worth being familiar with them if you’re working with money. ex_money uses :half_even as its default rounding since that is most common in financial applications and is even known as banker’s rounding.

So why can Decimal preserve prevision better than floating point? In part because many decimal libraries, including Decimal represent the decimal number as an integer with an associated scale factor (to indicate where the decimal place is located). Decimal also stores an exponent which can optimise the size of the integer part without losing precision. Postgres which has a very similar type called numeric stores only the integer and scale.

Serialisation

Not all interchange formats or storage formats support decimal data. Thankfully Postgres and its many descendant databases do with the numeric data type. In addition ex_money_sql provides Ecto and Postgres data types that combines the currency code with the money amount into a single data type to maximise data integrity - the design goal is to never be in a position where the currency type is unknown or ambiguous.

For interchange formats that have no decimal data type, like JSON, the amount is cast to a string since thats the only JSON data type that can preserve precision and scale. In fact money is cast to a JSON object of the form {\"currency\":\"USD\",\"amount\":\"100\"}.

On Precision and Scale

The microdollar format has the advantage of performance while maintaining a fixed scale or 6 digits. However financial calculations are expected to retain 7 or 8 digits of scale during calculations - think stock market, home loan interest. And as @LostKobrakai points out, Digital Tokens (crypto) may require more than 6 digits of scale. Bitcoin calculations may need up to 10 digits of scale.

Finally

Money and Float make different tradeoffs amongst performance, precision, scale and accuracy. Float emphasises performance. ex_money prioritises precision, scale and accuracy. microdollar aims for a middle ground. Each approach has its benefits and compromises.

When it comes to money, I believe precision, scale and accuracy are paramount and thats why ex_money uses Decimal for Elixir representation, a composite data type called money_with_currency for Postgres and a string-based object format for JSON (including embedded Ecto schemas.

9 Likes