Modelling self relations

I have a table categories with a parent fields:

{
  id: text("id").notNull().primaryKey(),
  .... 
  parent: text("parent").references((): AnySQLiteColumn => categories.id) 
}

{
  id: text("id").notNull().primaryKey(),
  .... 
  parent: text("parent").references((): AnySQLiteColumn => categories.id) 
}

Here one category can have one parent category. But, one category can have many subcategories. I have defined my relations:

export const categoryProductsRelation = relations(categories, ({ many, one })=>({
        subcategories: many(categories, { relationName: "subcategories" })
}));

export const parentCategoryRelation = relations(categories, ({ many, one })=>({
    parent: one(categories, {
        fields: [categories.parent],
        references: [categories.id]
    })
}));

export const categoryProductsRelation = relations(categories, ({ many, one })=>({
        subcategories: many(categories, { relationName: "subcategories" })
}));

export const parentCategoryRelation = relations(categories, ({ many, one })=>({
    parent: one(categories, {
        fields: [categories.parent],
        references: [categories.id]
    })
}));

But I am getting this error:

There is not enough information to infer relation \"categories.subcategories\"

There is not enough information to infer relation \"categories.subcategories\"

6 Replies

SPS | ShootmailOP•2y ago

I want to retrieve a category along with its parent and sub-categories. Got it relationName is what I needed, it's not documented though

export const categoryProductsRelation = relations(categories, ({ many, one })=>({
        productsOnCategory: many(products),
        subcategories: many(categories, {relationName: "subcategories"}),
        parentCategory: one(categories, {
            fields: [categories.parent],
            references: [categories.id],
            relationName: "subcategories"
        })
}));

export const categoryProductsRelation = relations(categories, ({ many, one })=>({
        productsOnCategory: many(products),
        subcategories: many(categories, {relationName: "subcategories"}),
        parentCategory: one(categories, {
            fields: [categories.parent],
            references: [categories.id],
            relationName: "subcategories"
        })
}));

ReactPoriyaalar•14mo ago

This should be documented

Angelelz•14mo ago

What do you think it's missing from the docs for this? Or what could be explained better? https://orm.drizzle.team/docs/rqb#declaring-relations

Drizzle Queries - DrizzleORM

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind

ReactPoriyaalar•14mo ago

Unlike prisma docs , self relations are not documented I stand corrected. Just relaized what you shared is actually a self relation of users against users. May be the use case of having one to many self relations could be explained with an example . In general a dedicated section for self relations would clarify this ambiguity

Angelelz•14mo ago

We do have an open issue to add a "Recipes" section to the docs. We'll add examples there

Angelelz•14mo ago

Please add any idea you might have for examples to add to that https://github.com/drizzle-team/drizzle-orm/issues/235

GitHub

Add "Recipes" section to docs · Issue #235 · drizzle-team/drizzle-o...

Conditinally add query parts (join, limit, order by etc.) Conditional field selection Dynamic field selection (user-provided fields) Table self-reference Tables circular reference Using subqueries ...

Gaming

Programming

Modelling self relations