Drizzle Team•13mo ago

Help creating many to many (user has many followers, user has many followees)

is this the right solution ?! my first attempts was putting 2 fields on the users followers, followees and i was corrected to this code : cannot be loaded work says users relation is missing something

import { InferModel, relations, sql } from "drizzle-orm";
import { pgTable, serial, text, varchar, integer, boolean, primaryKey, timestamp } from "drizzle-orm/pg-core";

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

export const usersFollowers = pgTable(
  "users_followers",
  {
    followerId: integer("follower_id").references(() => users.id, {
      onDelete: "cascade",
    }),
    followeeId: integer("followee_id").references(() => users.id, {
      onDelete: "cascade",
    }),
  },
  (t) => ({
    pk: primaryKey(t.followerId, t.followeeId),
  })
);

export const userFollowRelations = relations(usersFollowers, ({ one }) => ({
  follower: one(users, {
    fields: [usersFollowers.followerId],
    references: [users.id],
  }),
  followee: one(users, {
    fields: [usersFollowers.followeeId],
    references: [users.id],
  }),
}));

import { InferModel, relations, sql } from "drizzle-orm";
import { pgTable, serial, text, varchar, integer, boolean, primaryKey, timestamp } from "drizzle-orm/pg-core";

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

export const usersFollowers = pgTable(
  "users_followers",
  {
    followerId: integer("follower_id").references(() => users.id, {
      onDelete: "cascade",
    }),
    followeeId: integer("followee_id").references(() => users.id, {
      onDelete: "cascade",
    }),
  },
  (t) => ({
    pk: primaryKey(t.followerId, t.followeeId),
  })
);

export const userFollowRelations = relations(usersFollowers, ({ one }) => ({
  follower: one(users, {
    fields: [usersFollowers.followerId],
    references: [users.id],
  }),
  followee: one(users, {
    fields: [usersFollowers.followeeId],
    references: [users.id],
  }),
}));

10 Replies

tomeverson•13mo ago

I think you also have to put relationname in userFollowRelations

orenmizr•13mo ago

nice catch @tomeverson ! i added the code below, studio stops throwing errors... i gave them the same realtionNames from above - is that the idea ? or am i mistaken. not sure how it works in drizzle export const userFollowRelations = relations(usersFollowers, ({ one }) => ({ follower: one(users, { fields: [usersFollowers.followerId], references: [users.id], relationName: "followers", }), followee: one(users, { fields: [usersFollowers.followeeId], references: [users.id], relationName: "followees", }), }));

tomeverson•13mo ago

yep

orenmizr•13mo ago

not sure if this is the direction to solve many to many in a single table... you know, i thought the studio will show the users who follows a user and those who are followees of that user. instead i got the connecting table...

tomeverson•13mo ago

Many To Many Connection is hold by the users_followers table not by user table in Drizzle query i think you can do like:

db.query.users.findMany({
  with: {
    followers: {
        followers: true
}
}
})

db.query.users.findMany({
  with: {
    followers: {
        followers: true
}
}
})

according to your schema yep :3

orenmizr•13mo ago

turns out my drizzle crashed:
nitro] [unhandledRejection] Error: Relation followers not found tried to push migrate and got crashed too - error: column "follower_id" is in a primary key... spent 2 hours on this yesterday...

tomeverson•13mo ago

ohh my bad :) I think it should be

db.query.users.findMany({
  with: {
    followers: {
        follower: true
}
}
})

db.query.users.findMany({
  with: {
    followers: {
        follower: true
}
}
})

orenmizr•13mo ago

it did fix the issue... i love this drizzle thing... it forces me to dust off my sql skills... i got this response: followers: [ { followerId: 1, followeeId: 2, follower: [Object] } ], followees: [ { followerId: 2, followeeId: 1, followee: [Object] }, { followerId: 3, followeeId: 1, followee: [Object] }, { followerId: 4, followeeId: 1, followee: [Object] } ] } for user id = 1 sql wise i feel correct... but i thought with the relation and studio - when i click on a follower it will show my the user entity that follows... i don't really care about the connecting table... it is just something i have to do for manytomany i have to continue reading to further understand... i wish someone will publish a drizzle repo will all the possible relations and the gotchas... thanks. it helped a lot.

Noahh•13mo ago

Can you elaborate on this? What do you mean by "all the possible relations"? If I understand more I'd be happy to make some examples

orenmizr•13mo ago

what i have understood since then: - since i had 2 many on the same table, it requested that i use relation name (i think so it can match between them) - i thought the studio is like prisma when you have connected entities if you click user's follower relation - you should see the users.... so i thought the fact that i am seeing the connecting table means i did not correctly defined my relation...

Gaming

Programming

Help creating many to many (user has many followers, user has many followees)