Drizzle Team•15mo ago

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]
}),

5 Replies

McPizzaOP•15mo ago

got further by adding {relationName: 'replies'} to the many type, but now does not have enough info to infer the relation

Angelelz•15mo 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?

McPizzaOP•15mo ago

Ended up creating a separate relationship table and doing it through there Appreciate the help @angelelz

Liltripple_reid•14mo 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

McPizzaOP•14mo 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

Gaming

Programming

Relationships: Self one-one & one-many