DT
Drizzle Team•17mo ago
McPizza

Relationships: Self one-one & one-many

I have a table called convoMessages Trying to add a self relation of one to one But also a self relation of one to many Drizzle Studio is throwing an error of Error: There are multiple relations between "convoMessages" and "convo_messages". Please specify relation name I would have thought this syntax is fine: 
replies: many(convoMessages),
replyTo: one(convoMessages, {
fields: [convoMessages.replyToId],
references: [convoMessages.id]
}),
replies: many(convoMessages),
replyTo: one(convoMessages, {
fields: [convoMessages.replyToId],
references: [convoMessages.id]
}),
No description
5 Replies
McPizza
McPizzaOP•17mo ago
got further by adding {relationName: 'replies'} to the many type, but now does not have enough info to infer the relation
Angelelz
Angelelz•17mo ago
When you're doing more that one relation with the the same table (self or otherwise), you need to provide a name, so that drizzle can connect the correct reference column to the correct relation type The name have to be provided to both sides of your relation. Can you show the snippet of code to see where the problem might be?
McPizza
McPizzaOP•16mo ago
Ended up creating a separate relationship table and doing it through there Appreciate the help @angelelz
Liltripple_reid
Liltripple_reid•15mo ago
@McPizza can you show the table & relations you used to make it work? I'm struggling trying to setup something similar my table is something like this I'm trying to work on one-to-many relations for the same table, any idea how to implement it? saw this is still open 
// schema

export const users = mysqlTable("user", {
  id: varchar("id", { length: 255 }).notNull().primaryKey(),
  name: varchar("name", { length: 255 }),
  email: varchar("email", { length: 255 }).notNull(),
  emailVerified: timestamp("emailVerified", {
    mode: "date",
    fsp: 3,
  }).defaultNow(),
  image: varchar("image", { length: 255 }),
});

export const usersRelations = relations(users, ({ many }) => ({
  friends: many(users, { relationName: "friends" }), // error here
}));
// schema

export const users = mysqlTable("user", {
  id: varchar("id", { length: 255 }).notNull().primaryKey(),
  name: varchar("name", { length: 255 }),
  email: varchar("email", { length: 255 }).notNull(),
  emailVerified: timestamp("emailVerified", {
    mode: "date",
    fsp: 3,
  }).defaultNow(),
  image: varchar("image", { length: 255 }),
});

export const usersRelations = relations(users, ({ many }) => ({
  friends: many(users, { relationName: "friends" }), // error here
}));
throws errors
McPizza
McPizzaOP•15mo ago
I ended up going with a separate relationship table dont have any examples to hand right now But for you, you'd have a table called "Friendships" two columns: "Source" - "Friend" Relationships on the friendships one to one to users On users Relationships > many "Source" and many "Friend" Also use relationship names on both sides of the relationships Should work fine

Did you find this page helpful?