PJ作成
まずはPhoenix PJを作成します。
$ 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
メッセージに表示されるとおり、下記コマンドを実行します。
$ cd naive_tree
$ mix ecto.create
$ mix phx.server
LiveViewのCRUD
LiveViewのCRUDを作成します。
$ 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
生成されたマイグレーションファイルを確認します。
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
マイグレーションを実行します。
$ mix ecto.migrate
テストデータ投入、一覧表示
データを投入します。（seed作ればよかったかもしれない）
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 で生成されたカテゴリー一覧を表示してみます。
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を使います。
/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 にアクセス、編集したカテゴリー一覧を表示してみます。
親子関係を表示、ソートできました
生成される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