How to model self-referential tables

Questions & Help Questions
,
1

I have a transactions table where a row of transaction can be split (although not my use case: you can think of a bill being split amongst friends). Initially I considered all splits to be stored in a separate table but we will be capturing the exact same information except the amount will be different and they add some extra description field.

schema "transactions" do
    field :date, :date
    field :amount, :float
    field :has_splits, :boolean, default: false

    field :temp_id, :string, virtual: true
    field :delete, :boolean, virtual: true

    belongs_to :parent_transaction, Transactions, source: :parent_transaction_id

    has_many :children_transactions, Transactions, references: :parent_transaction_id
end

When I try to save this changeset:


#Ecto.Changeset<
  action: nil,
  changes: %{
    children_transactions: [
      #Ecto.Changeset<
        action: :insert,
        changes: %{
          amount: 149.0,
          date: ~D[2021-10-21],
          parent_transaction_id: "0b57d9b7-5459-4caa-bdc0-1031893f5ae7",
        },
        errors: [],
        data: #App.Transaction.Transactions<>,
        valid?: true
      >,
      #Ecto.Changeset<
        action: :insert,
        changes: %{
          amount: 100.0,
          date: ~D[2021-10-21],
          parent_transaction_id: "0b57d9b7-5459-4caa-bdc0-1031893f5ae7",
        },
        errors: [],
        data: #App.Transaction.Transactions<>,
        valid?: true
      >
    ]
  },
  errors: [],
  data: #App.Transaction.Transactions<>,
  valid?: true
>

I get this odd error:

[error] GenServer #PID<0.748.0> terminating
** (ArgumentError) unknown field `:transactions_parent_transaction_id` in %App.Transaction.Transactions{__meta__: #Ecto.Schema.Metadata<:built, "transactions">, amount: nil, children_transactions: #Ecto.Association.NotLoaded<association :children_transactions is not loaded>, date: nil, delete: nil, has_splits: false, id: nil, inserted_at: nil,parent_transaction: #Ecto.Association.NotLoaded<association :parent_transaction is not loaded>, parent_transaction_id: nil, temp_id: "b4d90993-f845-42e6-9f13-55e524b5d3e1", updated_at: nil}
    (ecto 3.7.1) lib/ecto/changeset.ex:1234: Ecto.Changeset.put_change/7
    (ecto 3.7.1) lib/ecto/changeset.ex:1213: Ecto.Changeset.put_change/3
    (ecto 3.7.1) lib/ecto/association.ex:811: Ecto.Association.Has.on_repo_change/5
    (ecto 3.7.1) lib/ecto/association.ex:572: anonymous fn/8 in Ecto.Association.on_repo_change/7
    (elixir 1.13.2) lib/enum.ex:2396: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto 3.7.1) lib/ecto/association.ex:568: Ecto.Association.on_repo_change/7
    (elixir 1.13.2) lib/enum.ex:2396: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto 3.7.1) lib/ecto/association.ex:532: Ecto.Association.on_repo_change/4
    (ecto 3.7.1) lib/ecto/repo/schema.ex:905: Ecto.Repo.Schema.process_children/5
    (ecto 3.7.1) lib/ecto/repo/schema.ex:985: anonymous fn/3 in Ecto.Repo.Schema.wrap_in_transaction/6
    (ecto_sql 3.7.1) lib/ecto/adapters/sql.ex:1021: anonymous fn/3 in Ecto.Adapters.SQL.checkout_or_transaction/4
    (db_connection 2.4.1) lib/db_connection.ex:1531: DBConnection.run_transaction/4
    (app 0.1.0) lib/app/live/transactions_live/split_transaction_component.ex:88: AppWeb.TransactionsLive.SplitTransactionComponent.handle_event/3
    (phoenix_live_view 0.17.6) lib/phoenix_live_view/channel.ex:566: anonymous fn/4 in Phoenix.LiveView.Channel.inner_component_handle_event/4
    (telemetry 0.4.3) /Users/dev/elixir/deps/telemetry/src/telemetry.erl:272: :telemetry.span/3
    (phoenix_live_view 0.17.6) lib/phoenix_live_view/diff.ex:206: Phoenix.LiveView.Diff.write_component/4
    (phoenix_live_view 0.17.6) lib/phoenix_live_view/channel.ex:490: Phoenix.LiveView.Channel.component_handle_event/6
    (stdlib 3.17) gen_server.erl:695: :gen_server.try_dispatch/4
    (stdlib 3.17) gen_server.erl:771: :gen_server.handle_msg/6
    (stdlib 3.17) proc_lib.erl:236: :proc_lib.wake_up/3

What’s the right away to model self-referential tables?

Thanks!

2

I don’t know if this helps, but every time I think I need a self-referential table to represent tree-like data such as this one I always end up using another trick, which is basically storing a field that tracks the paths from a parent to its children (an id hierarchy). This is fairly easy to manage and a lot more straightforward to query, since you can basically use String.starts_with?/2 to fetch the whole hierarchy for you.

Also, there’s already a lib that implements this pattern in Elixir which I’ve used before and it’s really easy to set up and use: GitHub - zven21/ancestry: The tree structure implementations for Elixir Ecto..

3

I believe you want the foreign_key option instead of source, and actually you won’t actually need this on the belongs_to because it is inferred from the belongs_to name.

4

I have been looking for an excuse to use PostgreSQL’s LTREE extension for a long time now. There also seems to be an Ecto plugin for it as well.

As far as I can tell this allows you do have a tree-like table with as much data (apart from the tree fields) as you need. Here’s one not-bad article about it.

Not sure how applicable it is for this question in particular but thought I’d just throw this in here since it seems to fit.

5

Thank you everyone for your suggestions. @benwilson512 pointer fixed the bug. Even though foreign_key would have been automatically recognized for the belongs_to, the children_transactions was not mapping to the right field. Adding foreign_key to the parent_transaction_id addresses the issue so far.

I still have more bugs to iron out with other parts of this logic but at least the basic operation is working.

Thank you @dimitarvp and @thiagomajesk for alternative suggestions. ancestry seems quite dated but the native postgres ltree looks very interesting and I actually need something like this for a totally different work. This particular issue is just a simple one-level deep reference so the simple table fix works.

Thanks!