Currently, I am utilizing Drizzle ORM paired with TypeScript, and within my database schema, I have a table that references itself for categories, allowing them to have parent categories. My objective is to execute a query that retrieves the categories sorted by their parentId first, followed by the id, in order to maintain the hierarchical relationship between parent and child categories.
Below is the structure of the schema:
import { pgTable, uuid, timestamp, varchar, text } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
export type CategoryId = number & { __typeName: 'Category' };
export const categories = pgTable('categories', {
id: uuid('id').$type<CategoryId>().defaultRandom().primaryKey(),
createdAt: timestamp('createdAt').defaultNow().notNull(),
title: varchar('title').notNull(),
description: text('description').notNull(),
parentId: uuid('parentId').references((): AnyPgColumn => categories.id),
});
export type Category = typeof categories.$inferSelect;
export const categoriesRelation = relations(categories, ({ many, one }) => ({
subcategories: many(() => categories, { relationName: "subcategories" }),
parent: one(() => categories, {
fields: [categories.parentId],
references: [categories.id],
relationName: "subcategories"
}),
}));
Sample dataset: Representation in the Database
CategoryId ParentId Title
--------------------------
1 null Pets
2 1 Pet bowls
3 null Cloth
4 3 Accessories
5 2 Pet bowls with some specific
The desired output:
CategoryId ParentId Title
--------------------------
1 null Pets
2 1 Pet bowls
5 2 Pet bowls with some specific
3 null Cloth
4 3 Accessories