I have a table categories
# model
schema "categories" do
field :name, :string
many_to_many :subcategories, Category, join_through: "subcategories", join_keys: [category_id: :id, subcategory_id: :id]
# migrations
def change do
create table(:subcategories, primary_key: false) do
add :category_id, references(:categories, on_delete: :delete_all)
add :subcategory_id, references(:categories, on_delete: :delete_all)
end
end
So lets say we have a Category with nested records:
%Category{
name: "Top Category",
subcategories: [
{name: "Second Level Category 1",
subcategories: []
},
{name: "Second Level Category 2",
subcategories: []
}
]}
Now, if I can recursively preload this nested record within an SQL transaction, its much better than using any code level logic. I am thinking of using a CTE expression using row to json for this. Here’s my shot at it:
Repo.all(Category,
from cat in Category,
join: subcat in "subcategories", on: cat.id == subcat.category_id,
join: frag in fragment(
"""
WITH RECURSIVE cattree AS (
WITH ctree AS (
SELECT
cat.id,
json_agg(subcat.*) as subcategories
FROM (
SELECT
cat.*,
subcategories
FROM category
) AS cat
GROUP BY cat.name
)
SELECT
subcat.*,
null::json as subcategories,
COALESCE(ct.subcategories, '[]') as subcategories
FROM subcategory subcat
LEFT JOIN catsubcat ct
USING(id)
WHERE subcategory_id is NULL
UNION ALL
SELECT
subcat.*,
row_to_json(subct.*) as subcategories,
COALESCE(ct.subcategories, '[]') as subcategories
FROM subcategory subcat
LEFT JOIN catsubcat ct
ON ct.id = subcat.category_id
)
SELECT *
FROM ctree
WHERE id == ?
""",
cat.id))
I have only tried coming up with this after going through various sources but this is incorrect. I am just conveying the idea, that using row_to_json it is possible to come up with recursively nested structure.
How can I query and return nested json structure, for this particular example, using CTE expressionso that I can load the category with subcategories?
