LeulAria
DTDrizzle Team
•Created by LeulAria on 8/8/2024 in #help
DEEPLY NESTED QUERY using { with }
-- Recursive case: Join with the base case to find children
SELECT
c.id,
c.slug,
c.name,
c.parent_id,
c.description,
c.image,
c.created_at,
c.updated_at,
json_build_object(
'id', c.id,
'slug', c.slug,
'name', c.name,
'parent_id', c.parent_id,
'description', c.description,
'image', c.image,
'created_at', c.created_at,
'updated_at', c.updated_at,
'children', '[]'::json -- Initialize children as an empty array
) AS category_data
FROM category c
INNER JOIN category_tree ct ON c.parent_id = ct.id
)
-- Aggregate and build hierarchical JSON structure
SELECT
id,
slug,
name,
parent_id,
description,
image,
created_at,
updated_at,
(
SELECT json_agg(child_data)
FROM (
SELECT
id,
slug,
name,
parent_id,
description,
image,
created_at,
updated_at,
COALESCE((
SELECT json_agg(child_data)
FROM category_tree child_data
WHERE child_data.parent_id = ct.id
), '[]'::json) AS children
FROM category_tree ct
WHERE ct.parent_id = category_tree.id
) AS child_data
) AS children
FROM category_tree
WHERE parent_id IS NULL;
-- Recursive case: Join with the base case to find children
SELECT
c.id,
c.slug,
c.name,
c.parent_id,
c.description,
c.image,
c.created_at,
c.updated_at,
json_build_object(
'id', c.id,
'slug', c.slug,
'name', c.name,
'parent_id', c.parent_id,
'description', c.description,
'image', c.image,
'created_at', c.created_at,
'updated_at', c.updated_at,
'children', '[]'::json -- Initialize children as an empty array
) AS category_data
FROM category c
INNER JOIN category_tree ct ON c.parent_id = ct.id
)
-- Aggregate and build hierarchical JSON structure
SELECT
id,
slug,
name,
parent_id,
description,
image,
created_at,
updated_at,
(
SELECT json_agg(child_data)
FROM (
SELECT
id,
slug,
name,
parent_id,
description,
image,
created_at,
updated_at,
COALESCE((
SELECT json_agg(child_data)
FROM category_tree child_data
WHERE child_data.parent_id = ct.id
), '[]'::json) AS children
FROM category_tree ct
WHERE ct.parent_id = category_tree.id
) AS child_data
) AS children
FROM category_tree
WHERE parent_id IS NULL;
7 replies
DTDrizzle Team
•Created by LeulAria on 8/8/2024 in #help
DEEPLY NESTED QUERY using { with }
and tried this and it work for now
WITH RECURSIVE category_tree AS (
-- Base case: Select all categories with no parent (top-level categories)
SELECT
id,
slug,
name,
parent_id,
description,
image,
created_at,
updated_at,
json_build_object(
'id', id,
'slug', slug,
'name', name,
'parent_id', parent_id,
'description', description,
'image', image,
'created_at', created_at,
'updated_at', updated_at,
'children', '[]'::json -- Initialize children as an empty array
) AS category_data
FROM category
WHERE parent_id IS NULL
UNION ALL
WITH RECURSIVE category_tree AS (
-- Base case: Select all categories with no parent (top-level categories)
SELECT
id,
slug,
name,
parent_id,
description,
image,
created_at,
updated_at,
json_build_object(
'id', id,
'slug', slug,
'name', name,
'parent_id', parent_id,
'description', description,
'image', image,
'created_at', created_at,
'updated_at', updated_at,
'children', '[]'::json -- Initialize children as an empty array
) AS category_data
FROM category
WHERE parent_id IS NULL
UNION ALL
7 replies
DTDrizzle Team
•Created by LeulAria on 8/8/2024 in #help
DEEPLY NESTED QUERY using { with }
i have tried this
this is producing sql query below
const categories = await db.query.category.findMany({
with: {
categories: true
}
})
const categories = await db.query.category.findMany({
with: {
categories: true
}
})
SELECT
"category"."id",
"category"."slug",
"category"."name",
"category"."parent_id",
"category"."description",
"category"."image",
"category"."created_at",
"category"."updated_at",
"category_categories"."data" AS "categories"
FROM
"category"
LEFT JOIN LATERAL (
SELECT
COALESCE(
json_agg(
json_build_array(
"category_categories"."id",
"category_categories"."slug",
"category_categories"."name",
"category_categories"."parent_id",
"category_categories"."description",
"category_categories"."image",
"category_categories"."created_at",
"category_categories"."updated_at"
)
),
'[]'::json
) AS "data"
FROM
"category" "category_categories"
WHERE
"category_categories"."parent_id" = "category"."id"
) "category_categories"
ON true;
SELECT
"category"."id",
"category"."slug",
"category"."name",
"category"."parent_id",
"category"."description",
"category"."image",
"category"."created_at",
"category"."updated_at",
"category_categories"."data" AS "categories"
FROM
"category"
LEFT JOIN LATERAL (
SELECT
COALESCE(
json_agg(
json_build_array(
"category_categories"."id",
"category_categories"."slug",
"category_categories"."name",
"category_categories"."parent_id",
"category_categories"."description",
"category_categories"."image",
"category_categories"."created_at",
"category_categories"."updated_at"
)
),
'[]'::json
) AS "data"
FROM
"category" "category_categories"
WHERE
"category_categories"."parent_id" = "category"."id"
) "category_categories"
ON true;
7 replies
DTDrizzle Team
•Created by LeulAria on 8/8/2024 in #help
DEEPLY NESTED QUERY using { with }
@Raphaël M (@rphlmr) ⚡ any suggestions
7 replies