Elixir/PhoenixでナイーブツリーをDB実装する


## PJ作成

まずはPhoenix PJを作成します。

```elixir
$ mix phx.new naive_tree
* creating naive_tree/config/config.exs
* creating naive_tree/config/dev.exs
* creating naive_tree/config/prod.exs
〜 略 〜

Fetch and install dependencies? [Yn] Y
〜 略 〜

We are almost there! The following steps are missing:

    $ cd naive_tree

Then configure your database in config/dev.exs and run:

    $ mix ecto.create

Start your Phoenix app with:

    $ mix phx.server

You can also run your app inside IEx (Interactive Elixir) as:

    $ iex -S mix phx.server
```

メッセージに表示されるとおり、下記コマンドを実行します。

```elixir
$ cd naive_tree
$ mix ecto.create
$ mix phx.server
```

## LiveViewのCRUD

LiveViewのCRUDを作成します。

```elixir
$ mix phx.gen.live Categories Category categories name:string parent_id:references:categories
```

router.exに追記します。

```lib/naive_tree_web/router.ex
defmodule NaiveTreeWeb.Router do
  use NaiveTreeWeb, :router

  〜略〜
  
  scope "/", NaiveTreeWeb do
    pipe_through :browser
    
    live "/categories", CategoryLive.Index, :index
    live "/categories/new", CategoryLive.Index, :new
    live "/categories/:id/edit", CategoryLive.Index, :edit

    live "/categories/:id", CategoryLive.Show, :show
    live "/categories/:id/show/edit", CategoryLive.Show, :edit
    
    get "/", PageController, :home
  end

  〜略〜
end
```

生成されたマイグレーションファイルを確認します。

```elixir
defmodule NaiveTree.Repo.Migrations.CreateCategories do
  use Ecto.Migration

  def change do
    create table(:categories) do
      add :name, :string
      add :parent_id, references(:categories, on_delete: :nothing)

      timestamps()
    end

    create index(:categories, [:parent_id])
  end
end
```

マイグレーションを実行します。

```bash
$ mix ecto.migrate
```

## テストデータ投入、一覧表示

データを投入します。（seed作ればよかったかもしれない）

```sql
INSERT INTO "public"."categories" ("id", "name", "parent_id", "inserted_at", "updated_at") VALUES
(1, '肉', NULL, '2023-12-02 10:53:40', '2023-12-02 10:53:40'),
(2, '鶏肉', 1, '2023-12-02 10:54:44', '2023-12-02 10:54:44'),
(3, '豚肉', 1, '2023-12-02 10:54:44', '2023-12-02 10:54:44'),
(4, '羊肉', 1, '2023-12-02 10:55:35', '2023-12-02 10:55:35'),
(5, '鶏胸肉', 2, '2023-12-02 10:55:35', '2023-12-02 10:55:35'),
(6, '鶏もも肉', 2, '2023-12-02 10:56:43', '2023-12-02 10:56:43'),
(7, '手羽先', 2, '2023-12-02 10:56:43', '2023-12-02 10:56:43'),
(8, '豚コマ', 3, '2023-12-02 11:00:03', '2023-12-02 11:00:03'),
(9, '豚肩', 3, '2023-12-02 11:02:09', '2023-12-02 11:02:09'),
(10, '豚ロース', 3, '2023-12-02 11:02:09', '2023-12-02 11:02:09'),
(11, '牛肉', 1, '2023-12-02 11:03:08', '2023-12-02 11:03:08'),
(12, 'カルビ', 11, '2023-12-02 11:03:57', '2023-12-02 11:03:57'),
(13, 'ロース', 12, '2023-12-02 11:22:10', '2023-12-02 11:22:10'),
(14, 'ハラミ', 13, '2023-12-02 11:22:50', '2023-12-02 11:22:50');
```

`http://localhost:4000/categories` にアクセス、`mix phx.gen.live` で生成されたカテゴリー一覧を表示してみます。

![スクリーンショット 2023-12-14 14.44.39.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/109744/57c7d8a4-2747-beb6-5836-5f3e99a0ebec.png)

categoriesテーブルの一覧を取得しています。

```/lib/naive_tree_web/live/category_live/index.ex
defmodule NaiveTreeWeb.CategoryLive.Index do
  use NaiveTreeWeb, :live_view

  alias NaiveTree.Categories
  alias NaiveTree.Categories.Category

  @impl true
  def mount(_params, _session, socket) do
    {:ok, stream(socket, :categories, Categories.list_categories())}
  end
```

```/lib/naive_tree/categories.ex
defmodule NaiveTree.Categories do
  import Ecto.Query, warn: false
  alias NaiveTree.Repo
  alias NaiveTree.Categories.Category

  def list_categories do
    Repo.all(Category)
  end
```

## 再帰クエリを実装する

Ecto.Queryの`with_cte/3`を使います。

https://hexdocs.pm/ecto/Ecto.Query.html#with_cte/3

```/lib/naive_tree/categories.ex
defmodule NaiveTree.Categories do
  @moduledoc """
  The Categories context.
  """

  import Ecto.Query, warn: false
  alias NaiveTree.Repo

  alias NaiveTree.Categories.Category # <- 追加

  # 関数を追加
  def list_cte_categories() do
    initial_query =
      from(
        c in Category,
        where: is_nil(c.parent_id),
        select: %{c | depth: fragment("0"), serial_number: fragment("'0000'")}
      )

    recursion_query =
      from(c in Category,
        inner_join: ct in "category_tree",
        on: c.parent_id == ct.id,
        select: %{
          c
          | depth: fragment("?", ct.depth + 1),
            serial_number: fragment("concat(?, '-', to_char(?, '0000'))", ct.serial_number, c.id)
        }
      )

    tree_query =
      initial_query
      |> union(^recursion_query)

    cte_query =
      Category
      |> recursive_ctes(true)
      |> with_cte("category_tree", as: ^tree_query)
      |> join(:left, [c], ct in "category_tree", on: c.id == ct.id)
      |> group_by([c, ct], [c.id, ct.depth, ct.serial_number])
      |> order_by([c, ct], [ct.serial_number])
      |> select_merge([c, ct], %{c | depth: ct.depth, serial_number: ct.serial_number})

    Repo.all(cte_query)
  end
```

```/lib/naive_tree_web/live/category_live/index.ex
defmodule NaiveTreeWeb.CategoryLive.Index do
  use NaiveTreeWeb, :live_view

  alias NaiveTree.Categories
  alias NaiveTree.Categories.Category

  @impl true
  def mount(_params, _session, socket) do
    # 関数を差し替える
    {:ok, stream(socket, :categories, Categories.list_cte_categories())}
  end
```

```lib/naive_tree/categories/category.ex
defmodule NaiveTree.Categories.Category do
  use Ecto.Schema
  import Ecto.Changeset

  schema "categories" do
    field :name, :string
    field :parent_id, :id
    field :depth, :integer, virtual: true
    # ソートに使うバーチャルフィールドを追加する
    field :serial_number, :string, virtual: true

〜略〜
```

```lib/naive_tree_web/live/category_live/index.html.heex
  <:col :let={{_id, category}} label="Name"><%= String.duplicate(">", category.depth) %> <%= category.name %></:col>
```

`http://localhost:4000/categories` にアクセス、編集したカテゴリー一覧を表示してみます。

![スクリーンショット 2023-12-14 22.43.39.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/109744/09a67ed7-0896-a2ac-afee-ca2e2796b7f9.png)

親子関係を表示、ソートできました:clap:

## 生成されるSQL

本記事の実装で生成されるSQLは下記のようになります。
もっとスマートに書ける場合は教えていただけると嬉しいです。

```sql
WITH RECURSIVE "category_tree" AS 
	(SELECT sc0."id" AS "id", sc0."name" AS "name", sc0."parent_id" AS "parent_id", 
	sc0."inserted_at" AS "inserted_at", sc0."updated_at" AS "updated_at", 0 AS "depth",
	'0000' AS "serial_number"
	FROM "categories" AS sc0 
	WHERE parent_id is NULL
	UNION 
	(SELECT sc0."id", sc0."name", sc0."parent_id", 
	sc0."inserted_at", sc0."updated_at", sc1."depth" + 1,
	concat(sc1."serial_number", '-', to_char(sc0."id", '0000'))
	FROM "categories" AS sc0 
	INNER JOIN "category_tree" AS sc1 ON sc0."parent_id" = sc1."id")) 
SELECT
	c0."id", c0."name", c0."parent_id", c0."inserted_at", c0."updated_at", 
	c1."depth", c1."serial_number"
FROM "categories" AS c0 
LEFT OUTER JOIN category_tree AS c1 ON c0."id" = c1."id" 
GROUP BY c0."id", c1.depth, c1.serial_number
ORDER BY c1.serial_number
```