Hello, I have a small phoenix app with just one ecto model called Office with the following schema for parent relationship

defmodule Tree.Arma.Office do
  use Ecto.Schema
  import Ecto.Changeset

  schema "offices" do
    field :name, :string
    belongs_to :parent, Tree.Arma.Office
    has_many :children, Tree.Arma.Office
    timestamps(type: :utc_datetime)
  end

  @doc false
  def changeset(office, attrs) do
    office
    |> cast(attrs, [:id, :name, :parent_id])
    |> validate_required([:name])
  end
end

Inside Arma context I have the following helper function to retrieve a linear list of offices in parent-children relations and works perfectly

def list_descendants(id) do
    base_query =
      Office
      |> where(id: ^id)

    recursive_query =
      Office
      |> join(:inner, [next_descendant], parent in "descendants_list",
        on: next_descendant.parent_id == parent.id
      )

    descendants_query = base_query |> union_all(^recursive_query)

    {"descendants_list", Office}
    |> recursive_ctes(true)
    |> with_cte("descendants_list", as: ^descendants_query)
    |> where([o], o.id != ^id)
    |> Repo.all()
  end

I have also defined the following functions to retrieve a nested map of descendants:

  def nested_tree(parent_id) do
    flat_list = list_descendants(parent_id)
    nester(flat_list, parent_id)
  end

  def nester(flat_list, root_id) do
    lookup = Enum.group_by(flat_list, & &1.parent_id)
    with_children(root_id, lookup)
  end

  defp with_children(root_id, lookup) do
    lookup
    |> Map.get(root_id, [])
    |> Enum.map(&nest_one(&1, lookup))

  end

  defp nest_one(row, lookup) do
    %{row | children: with_children(row.id, lookup)}
  end

So, if we have an Office with id=4 and call nested_tree(4) the function returns a nested map of all the descendants from the Office with id=4.

The problem is that when i call nested_tree from inside an iex -S mix shell all works fine; but when i the function is called from the liveview that shows the details for a sibgle Office, nested_tree() return an empty list. This is the code of the liveview:

defmodule TreeWeb.OfficeLive.Show do
  use TreeWeb, :live_view

  alias Tree.Arma

  @impl true
  def mount(_params, _session, socket) do
    {:ok, socket}
  end

  @impl true
  def handle_params(%{"id" => id}, _, socket) do
    {:noreply,
     socket
     |> assign(:page_title, page_title(socket.assigns.live_action))
     |> assign(:office, Arma.get_office!(id))
     |> assign(:office_tree, Arma.nested_tree(id))
    }
  end

  defp page_title(:show), do: "Show Office"
  defp page_title(:edit), do: "Edit Office"
end

I’ve found that if in the liveview the id of the office is hardcoded when calling nested_tree() it works!
So for example if in the liveview I put:

|> assign(:office_tree, Arma.nested_tree(4)

it works!

I’m really stuck with this, so if anybody can help me I really appreciate!!