Preload all linked records using ecto

Question

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?

if there is a way to do with raw sql that will also be great. (I can go over the records to convert them to ecto records.) — Tanweer, Mar 12 '19 at 06:03
You can walk through the list and call [`Ecto.Repo.preload/3`](https://hexdocs.pm/ecto/Ecto.Repo.html#c:preload/3) which allows _“to preload structs after they have been fetched from the database”_. — Aleksei Matiushkin, Mar 12 '19 at 06:19
What you are looking for is called a "Recursive Join", apparently. A T-SQL example can be found here: https://stackoverflow.com/questions/1757260/simplest-way-to-do-a-recursive-self-join — Christophe De Troyer, Mar 12 '19 at 08:03
@AlekseiMatiushkin you mean preload recursively if it has changed_to set — Tanweer, Mar 12 '19 at 08:03
There's a database design pattern called "closure table" that is invented specifically for storing and querying trees/forests. If you treat a linked list as a "unary tree" (a tree that each node has at most one child), then you can use the "closure table" on your data structure. — Aetherus, Mar 13 '19 at 04:56

score 1 · Accepted Answer · answered Mar 13 '19 at 13:56

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.

Preload all linked records using ecto

1 Answers1