Drizzle Team•15mo ago

many to many self reference

I'm trying to define "follows" & "followers" relations on a user entity. Obviously this is a many to many relation, but how to I define both "edges" in my relation?

const userToUsers = table("users", {
  userId: text(),
  followsUserId: text(),
}); // rather pseudo-ish code

const userRelations = relations(users, ({many}) => ({
  follows: many(userToUsers),
  followers: many(userToUsers), // hmmm... distinct from follows how?
}));

const userToUsers = table("users", {
  userId: text(),
  followsUserId: text(),
}); // rather pseudo-ish code

const userRelations = relations(users, ({many}) => ({
  follows: many(userToUsers),
  followers: many(userToUsers), // hmmm... distinct from follows how?
}));

I've seen many takes a config param relationName but I don't see where I can define that the relation "follows" references followsUserId and "followers" references userId?

5 Replies

Liltripple_reid•14mo ago

any updates here? I've been trying to do soemthing like this too

Angelelz•14mo ago

See this example of how this can be achieved: https://discord.com/channels/1043890932593987624/1111572543556550738/1148828114345021510

Liltripple_reid•14mo ago

still having issues 😢, pd: I can't use references because I'm using planetscale @Angelelz these are my relations and tables

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

/**
 * TODO: Friendships relations needs work
 */
export const usersRelations = relations(users, ({ many }) => ({
  secrets: many(secrets),
  receiving: many(usersToSecrets),
  friendships: many(friendships, { relationName: "followers" }),
}));

export const usersToSecrets = mysqlTable(
  "users_to_secrets",
  {
    userId: varchar("userId", { length: 255 }).notNull(),
    secretId: int("secretId").notNull(),
  },
  (t) => ({
    pk: primaryKey(t.userId, t.secretId),
  })
);

export const usersToSecretsRelations = relations(usersToSecrets, ({ one }) => ({
  receiver: one(users, {
    fields: [usersToSecrets.userId],
    references: [users.id],
  }),
  secret: one(secrets, {
    fields: [usersToSecrets.secretId],
    references: [secrets.id],
  }),
}));

export const friendships = mysqlTable(
  "friendship",
  {
    userId: varchar("user_id", { length: 255 }).notNull(),
    friendId: varchar("friend_id", { length: 255 }).notNull(),
  },
  (t) => ({
    pk: primaryKey(t.userId, t.friendId),
  })
);

export const friendshipsRelations = relations(friendships, ({ one }) => ({
  follower: one(users, {
    fields: [friendships.userId],
    references: [users.id],
    relationName: "follower",
  }),
  friend: one(users, {
    fields: [friendships.friendId],
    references: [users.id],
    relationName: "friend",
  }),
}));

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

/**
 * TODO: Friendships relations needs work
 */
export const usersRelations = relations(users, ({ many }) => ({
  secrets: many(secrets),
  receiving: many(usersToSecrets),
  friendships: many(friendships, { relationName: "followers" }),
}));

export const usersToSecrets = mysqlTable(
  "users_to_secrets",
  {
    userId: varchar("userId", { length: 255 }).notNull(),
    secretId: int("secretId").notNull(),
  },
  (t) => ({
    pk: primaryKey(t.userId, t.secretId),
  })
);

export const usersToSecretsRelations = relations(usersToSecrets, ({ one }) => ({
  receiver: one(users, {
    fields: [usersToSecrets.userId],
    references: [users.id],
  }),
  secret: one(secrets, {
    fields: [usersToSecrets.secretId],
    references: [secrets.id],
  }),
}));

export const friendships = mysqlTable(
  "friendship",
  {
    userId: varchar("user_id", { length: 255 }).notNull(),
    friendId: varchar("friend_id", { length: 255 }).notNull(),
  },
  (t) => ({
    pk: primaryKey(t.userId, t.friendId),
  })
);

export const friendshipsRelations = relations(friendships, ({ one }) => ({
  follower: one(users, {
    fields: [friendships.userId],
    references: [users.id],
    relationName: "follower",
  }),
  friend: one(users, {
    fields: [friendships.friendId],
    references: [users.id],
    relationName: "friend",
  }),
}));

not sure if the friendships/followers is conflicting with the other relations

Angelelz•14mo ago

Please read this thread, I go over a lot of details there with that person: https://discord.com/channels/1043890932593987624/1111572543556550738 In particular, pay attention to the relationName, it's kind of important BTW, you don't really need the refereces to make them work with drizzle I don't use planetscale because I want my referential integrity, but it'll work

Liltripple_reid•14mo ago

ah found the issue, it was about the relationName, thanks a lot!

Gaming

Programming

many to many self reference