How to get accounts where balance > 0 using ex_money?

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 - ex-money/money_sql: Money functions for the serialization of a money data type in Elixir · GitHub

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.

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

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?

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.

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.

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.

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.

Thank you @kip!

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.