Ecto: multiple belongs_to associations to same model

Hi, I have a “Trades” table which references the same table “Currency”, twice.

So there’s a table “currencies” with “USD”, “EUR”, “GBP”, etc.
And every forex trade has a “bought” column, referencing "currencies, a “sold” column which also references “currencies”, an exchange rate and a timestamp.

  use Ecto.Migration

  def change do
    create table(:trades) do
      add :bought, references(:currencies)
      add :sold, references(:currencies)
      add :rate, :float
      timestamps()
    end
  end

What I don’t know (sorry for the noob question) is how to code the schema for this association on the two Ecto schemas I have, nor how to set the foreign keys here.

If someone can help out or point me to some good article on the issue I’d be very grateful.

Hi,

If you only have two types of trades (i.e. bought and sold) it might be less complex to have a trades table like

def change do
    create table(:trades) do
      add :currency, references(:currencies)
      add :trade_type, :string
      add :rate, :float
      timestamps()
    end
  end

If that does not work for your solution you can reference the same table by setting the foreign key in the schema like this

belongs_to(:bought, Currencies, foreign_key: :bought_id)
belongs_to(:sold, Currencies, foreign_key: :sold_id)

I am doing this from memory, if it does not help let me know and I will dig out some code.

Andrew

** I went back to my code so made some changes

2 Likes

Hi, thanks for your answer.

If I try your solution below it tells me the columns “bought_id” and “sold_id” do not exist so I can’t insert it in the Repo.

Guess I would have to specify those names in the migration somehow?

OK so the above code by Andrew:

belongs_to(:bought, Currencies, foreign_key: :bought_id)
belongs_to(:sold, Currencies, foreign_key: :sold_id)

Works if I change the columns in the migration to:

def change do
    create table(:trades) do
      add :bought_id, references(:currencies)
      add :sold_id, references(:currencies)
      add :rate, :float
      timestamps()
    end
  end

That works nicely but on running Repo.get, it says:

__meta__: #Ecto.Schema.Metadata<:loaded, "trades">,
  bought: #Ecto.Association.NotLoaded<association :bought is not loaded>,
  bought_id: 1,
  id: 1,
  inserted_at: ~N[2020-11-09 19:53:04],

Is there any way to deal with this? Thanks.

It is not an error, it means associations are not loaded. You need to tell explicitely what You want to preload.

1 Like

Check this out https://hexdocs.pm/ecto/Ecto.Query.html#preload/3