Drizzle Team•2y ago

Many-to-Many Self Relation

I'm looking to migrate from Prisma, but I'm having trouble trying to figure out how a many-to-many self relation should be defined. The prisma schema I has previously was similar to:

model User {
  id        String          @id
  email     String?         @unique
  followers User[]          @relation("UserFollows")
  following User[]          @relation("UserFollows")
}

model User {
  id        String          @id
  email     String?         @unique
  followers User[]          @relation("UserFollows")
  following User[]          @relation("UserFollows")
}

Currently my schema looks like:

export const user = mysqlTable(
  "User",
  {
    id: varchar("id", { length: 191 }).primaryKey().notNull(),
    email: varchar("email", { length: 191 }),
  },
  ({ email }) => ({ emailKey: uniqueIndex("User_email_key").on(email) }),
);

export const userRelations = relations(user, ({ one, many }) => ({
  account: one(account, {
    fields: [user.id],
    references: [account.userId],
  }),
  cosmetics: many(cosmeticToUser),
  followers: many(userFollows),
  following: many(userFollows),
}));

export const userFollows = mysqlTable(
  "_UserFollows",
  {
    userId: varchar("A", { length: 191 }).notNull(),
    followedBy: varchar("B", { length: 191 }).notNull(),
  },
  ({ userId, followedBy }) => ({
    pk: primaryKey(userId, followedBy),
    abUnique: uniqueIndex("_UserFollows_AB_unique").on(userId, followedBy),
    followedByIdx: index("_UserFollows_B_index").on(followedBy),
  }),
);

export const userFollowsRelations = relations(userFollows, ({ one }) => ({
  user: one(user, {
    fields: [userFollows.userId],
    references: [user.id],
  }),
}));

export const user = mysqlTable(
  "User",
  {
    id: varchar("id", { length: 191 }).primaryKey().notNull(),
    email: varchar("email", { length: 191 }),
  },
  ({ email }) => ({ emailKey: uniqueIndex("User_email_key").on(email) }),
);

export const userRelations = relations(user, ({ one, many }) => ({
  account: one(account, {
    fields: [user.id],
    references: [account.userId],
  }),
  cosmetics: many(cosmeticToUser),
  followers: many(userFollows),
  following: many(userFollows),
}));

export const userFollows = mysqlTable(
  "_UserFollows",
  {
    userId: varchar("A", { length: 191 }).notNull(),
    followedBy: varchar("B", { length: 191 }).notNull(),
  },
  ({ userId, followedBy }) => ({
    pk: primaryKey(userId, followedBy),
    abUnique: uniqueIndex("_UserFollows_AB_unique").on(userId, followedBy),
    followedByIdx: index("_UserFollows_B_index").on(followedBy),
  }),
);

export const userFollowsRelations = relations(userFollows, ({ one }) => ({
  user: one(user, {
    fields: [userFollows.userId],
    references: [user.id],
  }),
}));

I'm just not quite sure how to construct the relations properly for the self-referencing with the many-to-many relation.

35 Replies

Angelelz•15mo ago

I was curious about this, so I did some testing. I got a many to many self relation like this:

export const users = mysqlTable("users", {
    id: int("id").autoincrement().primaryKey(),
    name: text("name").notNull(),
});

export type User = InferSelectModel<typeof users>;
export type NewUser = InferInsertModel<typeof users>;

export const usersRelations = relations(users, ({ many, one }) => ({
    follows: many(follows, { relationName: "follows" }),
    followers: many(follows, { relationName: "followers" }),
}));

export const follows = mysqlTable(
    "follows",
    {
        followerId: int("follower_id")
            .notNull()
            .references(() => users.id),
        followeeId: int("followee_id")
            .notNull()
            .references(() => users.id),
    },
    (t) => ({
        pk: primaryKey(t.followerId, t.followeeId),
    }),
);

export type Follow = InferSelectModel<typeof follows>;
export type NewFollow = InferInsertModel<typeof follows>;

export const followsRelations = relations(follows, ({ one }) => ({
    follower: one(users, {
        fields: [follows.followerId],
        references: [users.id],
        relationName: "follows",
    }),
    followee: one(users, {
        fields: [follows.followeeId],
        references: [users.id],
        relationName: "followers",
    }),
}));

export const users = mysqlTable("users", {
    id: int("id").autoincrement().primaryKey(),
    name: text("name").notNull(),
});

export type User = InferSelectModel<typeof users>;
export type NewUser = InferInsertModel<typeof users>;

export const usersRelations = relations(users, ({ many, one }) => ({
    follows: many(follows, { relationName: "follows" }),
    followers: many(follows, { relationName: "followers" }),
}));

export const follows = mysqlTable(
    "follows",
    {
        followerId: int("follower_id")
            .notNull()
            .references(() => users.id),
        followeeId: int("followee_id")
            .notNull()
            .references(() => users.id),
    },
    (t) => ({
        pk: primaryKey(t.followerId, t.followeeId),
    }),
);

export type Follow = InferSelectModel<typeof follows>;
export type NewFollow = InferInsertModel<typeof follows>;

export const followsRelations = relations(follows, ({ one }) => ({
    follower: one(users, {
        fields: [follows.followerId],
        references: [users.id],
        relationName: "follows",
    }),
    followee: one(users, {
        fields: [follows.followeeId],
        references: [users.id],
        relationName: "followers",
    }),
}));

Let me now if that helps

FuzboOP•15mo ago

Yeah I should have responded to my original post once I had the solution. I had this exact thing, except I did have to remove the .references(() => ...) as I was using the PlanetScale DB driver.

bluesky•15mo ago

@Angelelz I'm trying to currently understand this in the context of categories and subcategories. followers and followees is breaking my brain... what would I call the relation names if these were subcategories and categories...? I've done the following on the intermediate table: subcategory .... one relationName: 'subcategory' and category .... one relationName: 'category' and category table: subcategories: many relationName: 'subcategory' But this gives inverse results (I'd be expecting it to show the subcategories, when it instead shows the parent categories) Is the relationName the other thing being pointed to?

Angelelz•15mo ago

I feel like for your use case you don't need it as complex as my example

bluesky•15mo ago

It's a many-to-many self relation

Angelelz•15mo ago

What I wrote was a double self relation Where a person can have many followers and can be following many others I might have an example for your case somewhere, let me check Here you go:

export const users = mysqlTable("users", {
    id: int("id").autoincrement().primaryKey(),
    name: text("name").notNull(),
    managerId: int("user_id")
        .references((): AnyMySqlColumn => users.id)
        .$default(() => 1),
    createdAt: timestamp("created_at", { fsp: 3, mode: "string" }).default(
        sql`current_timestamp(3)`,
    ),
});

export const users = mysqlTable("users", {
    id: int("id").autoincrement().primaryKey(),
    name: text("name").notNull(),
    managerId: int("user_id")
        .references((): AnyMySqlColumn => users.id)
        .$default(() => 1),
    createdAt: timestamp("created_at", { fsp: 3, mode: "string" }).default(
        sql`current_timestamp(3)`,
    ),
});

bluesky•15mo ago

Ah, but can the user have many managers? Oh... inversely?

Angelelz•15mo ago

export const usersRelations = relations(users, ({ one }) => ({
    manager: one(users, { fields: [users.managerId], references: [users.id] }),
}));

export const usersRelations = relations(users, ({ one }) => ({
    manager: one(users, { fields: [users.managerId], references: [users.id] }),
}));

Ah no lol A manager can have many employees Like a category can have many sub categories

bluesky•15mo ago

I'm pretty sure I must've completely fried my brain... I was making a many-to-many system I'll try implementing this, thank you

Angelelz•15mo ago

Does a subcategory need to have many parent categories?

bluesky•15mo ago

oh crap actually, yeah I forgot damn it That's why it was so complex, yeah They're more like tags A tag can be listed under several tags, and can list several tags

Angelelz•15mo ago

tags are also one to many?

bluesky•15mo ago

A category can: * be listed under several parent categories * list several subcategories

Angelelz•15mo ago

Ok I guess lol

bluesky•15mo ago

yeah, it's a bit of a messy system but it works

Angelelz•15mo ago

Well, my initial example will work for this

bluesky•15mo ago

yes, then my only question would be about the relation names

Angelelz•15mo ago

You gotta name it in a way that makes sense But it doesn't really matter in terms of implementation

bluesky•15mo ago

my understand of relation names is like this it's just that I'm getting the wrong thing when accessing it via the categories table that's really what i'm wondering about and my apologies for wasting this much time

Angelelz•15mo ago

Can you put your code here? I'll try to help Let's see what you got

bluesky•15mo ago

export const categoryRelations = relations(categories, ({ many }) => ({
 subcategories: many(subcategoriesToCategories),
}));

export const subcategoriesToCategoriesRelations = relations(
 subcategoriesToCategories,
 ({ one }) => ({
  subcategory: one(categories, {
   fields: [subcategoriesToCategories.subcategoryId],
   references: [categories.id],
  }),
 })
);

export const categoryRelations = relations(categories, ({ many }) => ({
 subcategories: many(subcategoriesToCategories),
}));

export const subcategoriesToCategoriesRelations = relations(
 subcategoriesToCategories,
 ({ one }) => ({
  subcategory: one(categories, {
   fields: [subcategoriesToCategories.subcategoryId],
   references: [categories.id],
  }),
 })
);

the problem is when i get the subcategories of a category, I'm really getting the parent categories... is this from a misunderstanding of relation names?

Angelelz•15mo ago

It gets trippy, but you have to think in terms of the table you are writing on I would first rename category -> parentCategory Are you missing the other side of the relation in you categoryRelations?

bluesky•15mo ago

I don't need to know the parent category in my case, only the child one Ok so I'll update the code to reflect what you said

Angelelz•15mo ago

So what's the point of tracking that relation if you don't need it?

bluesky•15mo ago

err I added relation names to all of them when I got an error for not having any relation names i see what you're getting that, i'll adjust the code

Angelelz•15mo ago

export const categoryRelations = relations(categories, ({ many }) => ({
 subcategories: many(subcategoriesToCategories, {
  relationName: 'subcategory',
 }),
}));

export const subcategoriesToCategoriesRelations = relations(
 subcategoriesToCategories,
 ({ one }) => ({
  subcategory: one(categories, {
   fields: [subcategoriesToCategories.subcategoryId],
   references: [categories.id],
   relationName: 'parent_category',
  }),
  parentCategory: one(categories, {
   fields: [subcategoriesToCategories.categoryId],
   references: [categories.id],
   relationName: 'subcategory',
  }),
 })
);

export const categoryRelations = relations(categories, ({ many }) => ({
 subcategories: many(subcategoriesToCategories, {
  relationName: 'subcategory',
 }),
}));

export const subcategoriesToCategoriesRelations = relations(
 subcategoriesToCategories,
 ({ one }) => ({
  subcategory: one(categories, {
   fields: [subcategoriesToCategories.subcategoryId],
   references: [categories.id],
   relationName: 'parent_category',
  }),
  parentCategory: one(categories, {
   fields: [subcategoriesToCategories.categoryId],
   references: [categories.id],
   relationName: 'subcategory',
  }),
 })
);

bluesky•15mo ago

oh... so they're opposite ah.... aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaah..........

Angelelz•15mo ago

That should work if you want to keep the many to many self relation

bluesky•15mo ago

i was thinking relation names referred to themselves

Angelelz•15mo ago

I don't think you need it though

bluesky•15mo ago

thank you a ton, sorry this took so long relation names map to the other thing... now i understand

DeivoT•14mo ago

Hello! - @Angelelz I came up with the same solution (just a different naming) when I had to model a many to many self relation, but Im still seeing the "There is not enough information to infer relation" error 😢 do you mind taking a look at my schema and maybe figure out what am I missing?

export const user = pgTable("User", {
  id: text("id").primaryKey(),
  createdAt: timestamp("createdAt").notNull().defaultNow(),
  updatedAt: timestamp("updatedAt")
});

export const userRelations = relations(user, ({ many }) => ({
  followers: many(user, { relationName: "followers" }),
  following: many(user, { relationName: "following" })
}));

export const userNetwork = pgTable(
  "UserNetwork",
  {
    followerId: text("followerId")
      .notNull()
      .references(() => user.id),
    followingId: text("followingId")
      .notNull()
      .references(() => user.id),
    createdAt: timestamp("createdAt").notNull().defaultNow(),
    updatedAt: timestamp("updatedAt")
  },
  (table) => {
    return {
      pk: primaryKey(table.followerId, table.followingId)
    };
  }
);

export const userNetworkRelations = relations(userNetwork, ({ one }) => ({
  follower: one(user, {
    fields: [userNetwork.followerId],
    references: [user.id],
    relationName: "followers"
  }),
  following: one(user, {
    fields: [userNetwork.followingId],
    references: [user.id],
    relationName: "following"
  })
}));

export const user = pgTable("User", {
  id: text("id").primaryKey(),
  createdAt: timestamp("createdAt").notNull().defaultNow(),
  updatedAt: timestamp("updatedAt")
});

export const userRelations = relations(user, ({ many }) => ({
  followers: many(user, { relationName: "followers" }),
  following: many(user, { relationName: "following" })
}));

export const userNetwork = pgTable(
  "UserNetwork",
  {
    followerId: text("followerId")
      .notNull()
      .references(() => user.id),
    followingId: text("followingId")
      .notNull()
      .references(() => user.id),
    createdAt: timestamp("createdAt").notNull().defaultNow(),
    updatedAt: timestamp("updatedAt")
  },
  (table) => {
    return {
      pk: primaryKey(table.followerId, table.followingId)
    };
  }
);

export const userNetworkRelations = relations(userNetwork, ({ one }) => ({
  follower: one(user, {
    fields: [userNetwork.followerId],
    references: [user.id],
    relationName: "followers"
  }),
  following: one(user, {
    fields: [userNetwork.followingId],
    references: [user.id],
    relationName: "following"
  })
}));

and the error is the following -> Error: There is not enough information to infer relation "user.followers"

Angelelz•14mo ago

In a many to many relation, you have to have an intermediary table Your userRelations cannot reference the user table directly

export const userRelations = relations(user, ({ many }) => ({
  followers: many(userNetwork, { relationName: "follower" }),
  following: many(userNetwork, { relationName: "following" })
}));

export const userRelations = relations(user, ({ many }) => ({
  followers: many(userNetwork, { relationName: "follower" }),
  following: many(userNetwork, { relationName: "following" })
}));

DeivoT•14mo ago

Oh I see now what I was missing x) Thank you so muuuchhh 🙏

Kai Revona•12mo ago

Schema

import { InferInsertModel, InferSelectModel, relations } from "drizzle-orm";
import {
  mysqlTable,
  varchar,
  text,
  timestamp,
  serial,
  primaryKey,
  int,
} from "drizzle-orm/mysql-core";

export const users = mysqlTable("users", {
  id: int("id").autoincrement().primaryKey(),
  name: text("name").notNull(),
});

export type User = InferSelectModel<typeof users>;
export type NewUser = InferInsertModel<typeof users>;

export const usersRelations = relations(users, ({ many }) => ({
  follows: many(follows, { relationName: "follows" }),
  followers: many(follows, { relationName: "followers" }),
}));

export const follows = mysqlTable(
  "follows",
  {
    followerId: int("follower_id").notNull(),
    followeeId: int("followee_id").notNull(),
  },
  (t) => ({
    pk: primaryKey({
      columns: [t.followerId, t.followeeId],
    }),
  })
);

export type Follow = InferSelectModel<typeof follows>;
export type NewFollow = InferInsertModel<typeof follows>;

export const followsRelations = relations(follows, ({ one }) => ({
  follower: one(users, {
    fields: [follows.followerId],
    references: [users.id],
    relationName: "follows",
  }),
  followee: one(users, {
    fields: [follows.followeeId],
    references: [users.id],
    relationName: "followers",
  }),
}));

import { InferInsertModel, InferSelectModel, relations } from "drizzle-orm";
import {
  mysqlTable,
  varchar,
  text,
  timestamp,
  serial,
  primaryKey,
  int,
} from "drizzle-orm/mysql-core";

export const users = mysqlTable("users", {
  id: int("id").autoincrement().primaryKey(),
  name: text("name").notNull(),
});

export type User = InferSelectModel<typeof users>;
export type NewUser = InferInsertModel<typeof users>;

export const usersRelations = relations(users, ({ many }) => ({
  follows: many(follows, { relationName: "follows" }),
  followers: many(follows, { relationName: "followers" }),
}));

export const follows = mysqlTable(
  "follows",
  {
    followerId: int("follower_id").notNull(),
    followeeId: int("followee_id").notNull(),
  },
  (t) => ({
    pk: primaryKey({
      columns: [t.followerId, t.followeeId],
    }),
  })
);

export type Follow = InferSelectModel<typeof follows>;
export type NewFollow = InferInsertModel<typeof follows>;

export const followsRelations = relations(follows, ({ one }) => ({
  follower: one(users, {
    fields: [follows.followerId],
    references: [users.id],
    relationName: "follows",
  }),
  followee: one(users, {
    fields: [follows.followeeId],
    references: [users.id],
    relationName: "followers",
  }),
}));

And code

await db.insert(users).values({ name: "John" });
  await db.insert(users).values({ name: "Jane" });
  await db.insert(follows).values({ followeeId: 1, followerId: 2 });
  const result = await db.query.users.findMany({
    with: {
      followers: true,
      follows: true,
    },
  });
  console.log(result[0].followers[0].followeeId);

await db.insert(users).values({ name: "John" });
  await db.insert(users).values({ name: "Jane" });
  await db.insert(follows).values({ followeeId: 1, followerId: 2 });
  const result = await db.query.users.findMany({
    with: {
      followers: true,
      follows: true,
    },
  });
  console.log(result[0].followers[0].followeeId);

But when i populate the followers, it only return followerId and followeeId, but i want it return value like user table, include name. Plese help me

Gaming

Programming

Many-to-Many Self Relation