Preload all linked records using ecto

I have linked list kind of structure

defmodule Data.Record do
  use Data.Web, :model

  alias Data.{Record, Repo}

  schema "records" do
    field(:date_start, :date)
    field(:date_end, :date)
    field(:change_reason, :string)
    field(:is_active, :boolean, default: true)
    field(:notes, :string)
    belongs_to(
      :changed_from,
      Data.Record,
      foreign_key: :changed_from_id
    )

    belongs_to(
      :changed_to,
      Data.Record,
      foreign_key: :changed_to_id
    )

    timestamps()
  end
end

But the problem is we need all the nested records preloaded dynamically. e.g the list can record1 changed_to -> record2 changed_to -> record 3 changed_to. But ecto doesnt/cant preload dynamically e.g record |> preload([{:changed_to, :changed_to}])

What is the best way/workaround to preload all the linked changed_to records?

Solution

Well, the most (dirty) workaround would be something like this. It builds the arguments for preload to a certain depth:

def preload_args(relation, max_level \\ 50) do
  preload_args(relation, max_level - 1, relation)
end

defp preload_args(_relation, level, acc) when level <= 0, do: acc
defp preload_args(relation, level, acc) do
  preload_args(relation, level - 1, [{relation, acc}])
end

To use it:

Repo.preload record, Record.preload_args(:changed_to)

This will preload every :changed_to relation to a certain level or until there are no more. Of course this is not the solution you would really like to use because it performs a query for every preload and you don't know how long the chain will be upfront, might be much longer than 50 steps.

(please don't roast me for this code/suggestion, you specifically asked for workarounds too. ;)

I think that this comment about a 'closure table' by Aetherus, which pointed me to this article will probably lead you to a better solution. It also strengthens my presumption that you don't need to store both parent and child ids in the first place, the parent_id alone should be enough. That would also make it easier to insert a new Record: you don't need to update the parent too.