Drizzle Team•9mo ago

one-to-many self-relation

Hello, I'm trying to create a one-to-many self-relation but I get the error There is not enough information to infer relation "comment.replies", i looked at the previous threads but it didn't seem to solve my problem

export const comment = pgTable("comment", {
  id: uuid("id").notNull().unique().primaryKey().defaultRandom(),
  parentId: uuid("parent_id"),
  comment: text("comment").notNull(),
  userId: uuid("user_id").references(() => users.id, { onDelete: "cascade" }).notNull(),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
  deleted: boolean("deleted").default(false),
})

export const commentRelations = relations(comment, (({ one, many }) => ({
  user: one(users, {
    fields: [comment.userId],
    references: [users.id]
  }),

  replies: many(comment, { relationName: "replies" }),
})))

4 Replies

Brooks693•9mo ago

Do you have the inverse of the relation in the schema as well? So something like parentComment: one(comment, { fields: [comment.parentId], references: [comment.id], relationName: "replies" })

Mykhailo•9mo ago

@Brooks693 thank you, this solution helped me too!

export const commentsRelations = relations(comments, ({ one, many }) => ({
  post: one(posts, { fields: [comments.postId], references: [posts.id] }),
  replies: many(comments, { relationName: 'replies' }),
  parentComment: one(comments, {
    fields: [comments.parentId],
    references: [comments.id],
    relationName: 'replies',
  }),
}));

export const commentsRelations = relations(comments, ({ one, many }) => ({
  post: one(posts, { fields: [comments.postId], references: [posts.id] }),
  replies: many(comments, { relationName: 'replies' }),
  parentComment: one(comments, {
    fields: [comments.parentId],
    references: [comments.id],
    relationName: 'replies',
  }),
}));

SyuroOP•9mo ago

Thank you very much, it solved my problem

miad•8mo ago

@Brooks693 @Mykhailo @Syuro Hello there, I got the same problem and this solution fixed it, umm partially. Selecting comments with parentComment works. but selecting comment with replies doesn't. I my case, it's about categories. this is the categories schema:

export const categories = pgTable(
    'categories',
    {
        id: serial('id').primaryKey(),
        createdAt: timestamp('created_at', { precision: 6, withTimezone: true }).defaultNow(),
        updatedAt: timestamp('updated_at', { precision: 6, withTimezone: true })
            .defaultNow()
            .$onUpdate(() => new Date()),
        name: varchar('name', { length: 30 }).unique().notNull(),
        parentId: integer('parent_id'),
    },
    (categories) => {
        return {
            categoryNameIdx: uniqueIndex('category_name_idx').on(categories.name),
        };
    },
);

export const categoriesRelations = relations(categories, ({ one, many }) => ({
    parent: one(categories, {
        fields: [categories.parentId],
        references: [categories.id],
        relationName: 'child_to_parent_category',
    }),
    children: many(categories, { relationName: 'parent_to_children_category' }),
}));

export const categories = pgTable(
    'categories',
    {
        id: serial('id').primaryKey(),
        createdAt: timestamp('created_at', { precision: 6, withTimezone: true }).defaultNow(),
        updatedAt: timestamp('updated_at', { precision: 6, withTimezone: true })
            .defaultNow()
            .$onUpdate(() => new Date()),
        name: varchar('name', { length: 30 }).unique().notNull(),
        parentId: integer('parent_id'),
    },
    (categories) => {
        return {
            categoryNameIdx: uniqueIndex('category_name_idx').on(categories.name),
        };
    },
);

export const categoriesRelations = relations(categories, ({ one, many }) => ({
    parent: one(categories, {
        fields: [categories.parentId],
        references: [categories.id],
        relationName: 'child_to_parent_category',
    }),
    children: many(categories, { relationName: 'parent_to_children_category' }),
}));

Now this query works:

    const categories = await this.drizzleService.db.query.categories.findMany({
            where: ilike(schema.categories.name, getCategoryDto.name),
            with: {
                parent: true,
            },
        });

    const categories = await this.drizzleService.db.query.categories.findMany({
            where: ilike(schema.categories.name, getCategoryDto.name),
            with: {
                parent: true,
            },
        });

But this gives me an error:

        const categories = await this.drizzleService.db.query.categories.findMany({
            where: ilike(schema.categories.name, getCategoryDto.name),
            with: {
                children: true,
            },
        });

        const categories = await this.drizzleService.db.query.categories.findMany({
            where: ilike(schema.categories.name, getCategoryDto.name),
            with: {
                children: true,
            },
        });

And the error is:

There is not enough information to infer relation "categories.children"

There is not enough information to infer relation "categories.children"

Is this even possible? or only joining with parentId would work? Sorry to bother, Found the solution. parent and children need to have the same relation name

Gaming

Programming

one-to-many self-relation