Ecto unions with join condition to preload records recursively

Questions / Help
, , ,
1

related resouces: simplest-way-to-do-a-recursive-self-join and schema example and details for this question. Here is the schema

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

Problem
How to Preload all linked records using ecto? 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}]).

Solution
Recursive join is the solution which is easily doable in raw query.

But the problem is I cant use raw query. I need to apply multiple filters along with dynamically loading linked resources. Does ecto support unions with join(I dont think it supports as for as I have gone through docs)? How to make query(raw query or ecto union on which I can apply further filters) in ecto. Any help/workaround is highly appreciated. thanks

3 Likes