CODEWITHSUNDEEP
elixirecto
pablo.meier
pablo.meier

Reputation: 2359

Reference a composite key in a `belongs_to` association in Ecto?

I'm trying to model one entity A as append-only, with another child entity that references the first. So A has a structure like (sorted by inserted_at DESC):

|  id  |  version  |  column | inserted_at |
|------|-----------+---------|-------------|
|   5  |     2     |  "baz"  | 2020-04-20  |
|   3  |     2     |  "zoot" | 2020-04-20  |
|   3  |     1     |  "bar " | 2020-04-18  |
|   5  |     1     |  "foo"  | 2020-04-10  |

(id, version) forms the primary key for A (One could do (id, inserted_at) as well, but the developer decided version numbers were more readable).

Now B belongs to A, and each B will correspond with exactly one (id, version) pair of A. So something like:

|  id  |  a_id | a_version |  column | inserted_at |
|------|-------+-----------+---------+-------------|
|   4  |   5   |    2      |  "pigs" | 2020-05-05  |
|   3  |   5   |    2      |  "goats"| 2020-05-03  |
|   2  |   5   |    1      |  "rams" | 2020-05-02  |
|   1  |   3   |    1      |  "bears"| 2020-04-18  |

my question is, how can I model these with Ecto Schemas? I think I know from reading docs what the A schema looks like, except for the has_many:

defmodule MyASchema do 
  use Ecto.Schema

  @primary_key false
  schema "table_a" do
    field :id, :id, primary_key: true
    field :version, :integer, primary_key: true
    field :column, :string
    field :inserted_at, :utc_datetime
    has_many :bs, MyBSchema  # what goes here for :foreign_key?
  end
end

But the B schema (especially belongs_to) is less clear to me:

defmodule MyBSchema do 
  use Ecto.Schema

  @primary_key
  schema "table_b" do
    field :id, :id, primary_key: true
    field :column, :string
    field :inserted_at, :utc_datetime

    # how does belongs_to work here? would it be 
    #
    # belongs_to :a, MyASchema, primary_key: [:id, :version]
    #
    # or 
    #
    # belongs_to :a, MyASchema, define_key: false
    # field :a_id, :id
    # field :a_version, :integer
    #
    # ? If so, how could I query such that the :a field of the
    # struct is populated?
  end
end

Happy to clarify further, thanks for reading + any help 🙂

Upvotes: 1

Views: 2832

Answers (1)

Everett
Everett

Reputation: 9558

According to the Elixir forum, Ecto does not support compound foreign keys when working with associations.

One solution would be to add a "regular" unique primary key (e.g. an auto-incrementing integer or UUID) and base the references off of that id. Sometimes you make consolations when working with database abstraction layers because relationships are easier to define when the database has a simple one-column primary key (i.e. not a composite).

If altering the database schema is not possible, then you would need to manually resolve the associations in your code. You may need to set up multiple transactions as outlined by this post.

Upvotes: 2

Related Questions