Drizzle Team•17mo ago

DEEPLY NESTED QUERY using { with }

const categories = await db.query.category.findMany({
        with: {
          categories: true
        }
 })

const categories = await db.query.category.findMany({
        with: {
          categories: true
        }
 })

im querying by the above query my schema is below

export const category = pgTable("category", {
  id: uuid("id").primaryKey().defaultRandom(),
  slug: varchar("slug", { length: 255 }).notNull(),
  name: varchar("name", { length: 255 }).notNull(),
  parent_id: uuid("parent_id").references((): AnyPgColumn => category.id)
});

export const category = pgTable("category", {
  id: uuid("id").primaryKey().defaultRandom(),
  slug: varchar("slug", { length: 255 }).notNull(),
  name: varchar("name", { length: 255 }).notNull(),
  parent_id: uuid("parent_id").references((): AnyPgColumn => category.id)
});

it can return 1 deep but after that child is not populating help?

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;

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

-- 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;