I’m new to functional programming and I’d like to know the idiomatic way to convert a flat list of maps into a nested list.

For some background, I’m experimenting with Ecto. I have a table with a recursive relationship to itself. That is to say that there is a parent_id field which has a foreign key pointing to the id field of the same table. I’m trying to get a list of just the rows where the parent_id is null at the top level with all of its children in a nested list.

flat = [
    %{id: 1, name: "region 1", parent_region_id: nil},
    %{id: 2, name: "subregion 1", parent_region_id: 1},
    %{id: 3, name: "subregion 2", parent_region_id: 1},
    %{id: 4, name: "region 2", parent_region_id: nil},
    %{id: 5, name: "subregion 3", parent_region_id: 4},
    %{id: 6, name: "subregion 4", parent_region_id: 4}
  ]

nested = [
    %{
      id: 1,
      name: "region 1",
      parent_region_id: nil,
      subregions: [%{id: 2, name: "subregion 1", parent_id: 1}, %{id: 3, name: "subregion 2", parent_region_id: 1}]
    },
    %{
      id: 4,
      name: "region 2",
      parent_region_id: nil,
      subregions: [%{id: 5, subregion: "subregion 3", parent_region_id: 4}, %{id: 6, name: "subregion 4", parent_region_id: 4}]
    }
  ]

I can achieve this by using Repo.preload(:subregions), but that would add an unnecessary join. Any tips on an elegant way to convert flat into nested?

Here is the migration script.

defmodule Bazaar.Repo.Migrations.CreateRegions do
  use Ecto.Migration

  def change do
    create table(:regions) do
      add :name, :string
      add :parent_region_id, references(:regions), null: true

      timestamps()
    end
  end
end

And this is the schema.

defmodule Bazaar.Geoscheme.Region do
  use Ecto.Schema
  import Ecto.Changeset

  schema "regions" do
    field(:name, :string)

    belongs_to(:parent_region, Bazaar.Geoscheme.Region)
    has_many(:subregions, Bazaar.Geoscheme.Region, foreign_key: :parent_region_id)

    timestamps()
  end

  @doc false
  def changeset(region, attrs) do
    region
    |> cast(attrs, [:name, :parent_region_id])
    |> validate_required([:name])
  end
end