Drizzle Team•15mo ago

Need help implementing one to many relations for a table on itself

const comments = pgTable("comments", {
  id: text("id").notNull().primaryKey(),

  parentCommentId: text("parent_comment_id").references(
    (): AnyPgColumn => comments.id
  ),
});

const commentsRelations = relations(comments, ({ many, one }) => ({
  childComments: many(comments, {
    relationName: "child_comments_from_parent_comment",
  }),

  parentComment: one(comments, {
    fields: [comments.parentCommentId],
    references: [comments.id],
    relationName: "parent_comment_from_child_comment",
  }),
}));

const comments = pgTable("comments", {
  id: text("id").notNull().primaryKey(),

  parentCommentId: text("parent_comment_id").references(
    (): AnyPgColumn => comments.id
  ),
});

const commentsRelations = relations(comments, ({ many, one }) => ({
  childComments: many(comments, {
    relationName: "child_comments_from_parent_comment",
  }),

  parentComment: one(comments, {
    fields: [comments.parentCommentId],
    references: [comments.id],
    relationName: "parent_comment_from_child_comment",
  }),
}));

I want to implement a one to many self relation on the comments table, where a comment can have child comments recursively. The comment where parentCommentId is null would be the root comment that has no parent comment. Somehow this doesn't work. What's the problem in this schema? Here's the error:-

There is not enough information to infer relation "comments.childComments"

There is not enough information to infer relation "comments.childComments"

5 Replies

Aidan Laycock•15mo ago

Won't you need to add the fields and references for this? Like you've got in parentComment? As it will be looking for a comment_id (Or something similar) most-likely in the query to join on.

xoldyckk•15mo ago

in a one to many relation no since the parent can never know how many children it has a child has exactly one parent referenced by the parentCommentId field present on it querying for the child comments is an implementation detail abstracted away from us i believe only the relationName field is available when defining relations with many keyword if it's not abstracted away then we probably have to do it manually when using query client, then perhaps the only use of defining relations table is for typescript intellisense

Aidan Laycock•15mo ago

Yes I get you, although to resolve the error you've posted I think it needs the additional info so that comments.childComments will work 🙂 I'm not 100% sure though as I've not got something to test it on right now.

xoldyckk•15mo ago

Okay some stuff happened, idk what but it suddenly started pain

Aidan Laycock•15mo ago

Haha, no worries! Glad it's working! 😄

Gaming

Programming

Need help implementing one to many relations for a table on itself