CODEWITHSUNDEEP
javascripttypescriptdrizzle
Pavel Perevezencev
Pavel Perevezencev

Reputation: 2978

Ordering by parent then child using Drizzle ORM

I am using Drizzle ORM with TypeScript and I have a self-referencing table where categories can have parent categories. I need to perform a query to retrieve categories ordered first by their parentId and subsequently by the id to maintain the parent-child relationship in the ordering.

Here is 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"
  }),
}));

Example dataset: How it present in DB

CategoryId ParentId Title
--------------------------
1          null     Pets      
2          1        Pet bowls 
3          null     Cloth
4          3        Accessories
5          2        Pet bowls with some specific

How it I should to get:

CategoryId ParentId Title
--------------------------
1          null     Pets      
2          1        Pet bowls
5          2        Pet bowls with some specific
3          null     Cloth
4          3        Accessories

Upvotes: 0

Views: 1010

Answers (1)

Svetoslav Petkov
Svetoslav Petkov

Reputation: 1575

I do not think you can achieve that with the drizzle orm API.

You want very specific ordering, which is not pure column order in drizzle API. This should not be done at the ORM level, as it probably solves an UI representation issue. So, it should be placed in an upper application layer, not in the Persistence adapter (which the ORM is).

This can be solved with a simple algorithm computed in the appropriate place.

const getArangedList = (input: Array<Category>, parent: Category | undefined = undefined, depth = 0): Array<Category> => {
    if (depth >= 50) {
        throw new Error(`Cannot get getArangedList. Max recursion depth exceeded`)
    }

    const result: Array<Category> = [];

    if (parent) result.push(parent);

    const subCategoryesSorted = input.filter((c) => c.parentId === parent?.id).sort(sortByIdAsc);
    const subCategoriesResults = subCategoryesSorted.flatMap((subCategory) => getArangedList(input, subCategory, depth + 1));
    result.push(...subCategoriesResults);

    return result;
}

Full example here

Upvotes: 0

Related Questions