Building a JSON map for a self-referencing Ecto model

Question

I have an Ecto model as such:

defmodule Project.Category do
  use Project.Web, :model

  schema "categories" do
    field :name, :string
    field :list_order, :integer
    field :parent_id, :integer
    belongs_to :menu, Project.Menu
    has_many :subcategories, Project.Category, foreign_key: :parent_id
    timestamps
  end

  @required_fields ~w(name list_order)
  @optional_fields ~w(menu_id parent_id)

  def changeset(model, params \\ :empty) do
    model
    |> cast(params, @required_fields, @optional_fields)
  end
end

As you can see the Category model can reference itself via the subcategories atom.

Here is the view associated with this model:

defmodule Project.CategoryView do
  use Project.Web, :view

  def render("show.json", %{category: category}) do
    json = %{
      id: category.id,
      name: category.name,
      list_order: category.list_order
      parent_id: category.parent_id
    }
    if is_list(category.subcategories) do
      children = render_many(category.subcategories, Project.CategoryView, "show.json")
      Map.put(json, :subcategories, children)
    else
      json
    end
  end
end

I have an if condition on subcategories so that I can play nice with Poison when they are not preloaded.

Finally, here are my 2 controller functions that invoke this view:

defmodule Project.CategoryController do
  use Project.Web, :controller

  alias Project.Category

  def show(conn, %{"id" => id}) do
    category = Repo.get!(Category, id)
    render conn, "show.json", category: category
  end

  def showWithChildren(conn, %{"id" => id}) do
    category = Repo.get!(Category, id)
               |> Repo.preload [:subcategories, subcategories: :subcategories]
    render conn, "show.json", category: category
  end
end

The show function works fine:

{
  "parent_id": null,
  "name": "a",
  "list_order": 4,
  "id": 7
}

However, my showWithChildren function is limited to 2 levels of nesting because of how I use preloading:

{
  "subcategories": [
    {
      "subcategories": [
        {
          "parent_id": 10,
          "name": "d",
          "list_order": 4,
          "id": 11
        }
      ],
      "parent_id": 7,
      "name": "c",
      "list_order": 4,
      "id": 10
    },
    {
      "subcategories": [],
      "parent_id": 7,
      "name": "b",
      "list_order": 9,
      "id": 13
    }
  ],
  "parent_id": null,
  "name": "a",
  "list_order": 4,
  "id": 7
}

For example, the category item 11 above also has subcategories but I am unable to reach them. Those subcategories can also have subcategories themselves, so the potential depth of the hierarchy is n.

I am aware that I need some recursive magic but since I'm new to both functional programming and Elixir, I cannot wrap my head around it. Any help is greatly appreciated.

Answer 1

You can consider doing the preloading in the view, so it works recursively:

def render("show.json", %{category: category}) do
  %{id: category.id,
    name: category.name,
    list_order: category.list_order
    parent_id: category.parent_id}
  |> add_subcategories(category)
end

defp add_subcategories(json, %{subcategories: subcategories}) when is_list(subcategories) do
  children =
    subcategories
    |> Repo.preload(:subcategories)
    |> render_many(Project.CategoryView, "show.json")
  Map.put(json, :subcategories, children)
end

defp add_subcategories(json, _category) do
  json
end

Keep in mind this is not ideal for two reasons:

1. Ideally you don't want to do queries in views (but this is is recursive, so it is easier to piggyback in the view rendering)

2. You are going to emit multiple queries for the second level of subcategories

There is a book called SQL Antipatterns and, if I am not mistaken, it covers how to write tree structures. Your example is exposed as an antipattern in one of the free chapters. It is an excellent book and they explore solutions for all antipatterns.

PS: you want show_with_children and not showWithChildren.