Ecto type for two fields in database

Questions / Help
1

I have two fields in the database: price_amount and price currency and I use this scheme:

  schema "items" do
    field :price_amount, :integer
    field :price_currency, :string
  end

Can I somehow describe the custom Ecto type, so that it would work with one custom field price instead of two?

Maybe like this:

  schema "items" do
    field :price, Money.Ecto.Type
  end
1 Like
2

As far as I know, Ecto does not support having a type whose data is put into more than one database field.

The following alternative approaches are possible in Ecto and might suit your needs:

  1. Have a special schema/database table that stores Prices, which contains a belongs_to to your items table.
  2. Create an embedded_schema for the Money datatype, and add an embeds_one field on your “items” schema. This will mean that the price will be stored in a single column. The drawback is that it will be stored in a way that might not be easily searchable (in Postgres the jsonb type is used, which might be searchable, and in other databases, the map is stored as a JSON string).

It is also possible to have a virtual price field that is filled in as soon as an Item is fetched from the database, and cast back to the two :price_amount and :price_currency when an item is about to be stored (see for instance prepare_changes).

3

AFAIK, if the data is in two fields in the database, it needs to be two separate entries in the schema. The schema just maps table columns to native Elixir types.

4

Thank you.
I thought about json and custom data types in the database, but that’s not the best way to store it.
I think the best way to make abstraction of the virtual price field as you described in the last paragraph.

1 Like
5

If you’re using postgres, you can create new composite type like this:

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

  def up do
    execute """
    CREATE TYPE public.money_with_currency AS (currency_code char(3), amount numeric(20,8))
    """
    create table(:ledgers) do
      add :amount, :money_with_currency
      timestamps()
    end
  end
end

defmodule Money.Ecto.Composite.Type do
  @behaviour Ecto.Type

  def type do
    :money_with_currency
  end

  def load({currency, amount}) do
    {:ok, Money.new(currency_code, amount)}
  end

  def dump(%Money{} = money) do
    {:ok, {to_string(money.currency), money.amount}}
  end
  ...
end

So in schema, it looks like you want:

  schema "items" do
    field :price, Money.Ecto.Composite.Type
  end

Look here for details - https://github.com/kipcole9/money#serializing-to-a-postgres-database-with-ecto

10 Likes