Ecto: multiple belongs_to associations to same model

Questions / Help
1

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.

2

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
3

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?

4

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.

1 Like
5

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

2 Likes
6

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

1 Like