DT
Drizzle Team•2y ago
Skyler

Many to many relationship between one type

I'm making an application where I have users who are able to follow other users. This means that I need a many-to-many relationship, but both sides are the same type (a user). I'm not sure how to express this with drizzle's relations, as I'm not sure how to specify both sides of the relationship For example... 
export const Users = pgTable("users", {
    id: uuid('id').primaryKey().defaultRandom(),
});

export const UsersRelations = relations(Users, ({ many }) => ({
    followers: many(Followers), // how do I separate these?
    following: many(Followers), // is it even possible?
}))

export const Following = pgTable("followers", {
    userId: uuid('user_id').references(() => Users.id).notNull(),
    followingId: uuid('following_id').references(() => Users.id).notNull(),
}, (table) => ({
    cpk: primaryKey(table.userId, table.followingId)
}));

export const FollowingRelations = relations(Following, ({ one }) => ({
    user: one(Users, {
        fields: [Following.userId],
        references: [Users.id],
    }),
    following: one(Users, {
        fields: [Following.followingId],
        references: [Users.id],
    })
}))
export const Users = pgTable("users", {
    id: uuid('id').primaryKey().defaultRandom(),
});

export const UsersRelations = relations(Users, ({ many }) => ({
    followers: many(Followers), // how do I separate these?
    following: many(Followers), // is it even possible?
}))

export const Following = pgTable("followers", {
    userId: uuid('user_id').references(() => Users.id).notNull(),
    followingId: uuid('following_id').references(() => Users.id).notNull(),
}, (table) => ({
    cpk: primaryKey(table.userId, table.followingId)
}));

export const FollowingRelations = relations(Following, ({ one }) => ({
    user: one(Users, {
        fields: [Following.userId],
        references: [Users.id],
    }),
    following: one(Users, {
        fields: [Following.followingId],
        references: [Users.id],
    })
}))
In the example above, the UserRelations relation needs to have many follower relations twice, but it needs to be one for each side of the relationship. I'm not sure how to specify that these are different 
export const UsersRelations = relations(Users, ({ many }) => ({
    followers: many(Followers), // how do I separate these?
    following: many(Followers), // is it even possible?
}))
export const UsersRelations = relations(Users, ({ many }) => ({
    followers: many(Followers), // how do I separate these?
    following: many(Followers), // is it even possible?
}))
12 Replies
Dan
Dan•2y ago
You can specify the relationName parameter in the 2nd argument, like this: 
followers: many(Followers, { relationName: 'followers' }),
following: many(Followers, { relationName: 'following' }),

...

    user: one(Users, {
        fields: [Following.userId],
        references: [Users.id],
        relationName: 'followers',
    }),
    following: one(Users, {
        fields: [Following.followingId],
        references: [Users.id],
        relationName: 'following',
    })
followers: many(Followers, { relationName: 'followers' }),
following: many(Followers, { relationName: 'following' }),

...

    user: one(Users, {
        fields: [Following.userId],
        references: [Users.id],
        relationName: 'followers',
    }),
    following: one(Users, {
        fields: [Following.followingId],
        references: [Users.id],
        relationName: 'following',
    })
It's not documented yet, unfortunately, but we'll try to add it to the documentation soon
NinjaBunny
NinjaBunny•16mo ago
did you get this to work? I'm trying to implement the same thing, but I keep getting (in my case friends) friends table is not defined in the schema. However I essentially just copied everything from here and it doesn't work :(. If you have any tips it'd be much appreciated nvm I'm sorry to bother you... I forgot to export(using barrel files) it so I can actually query with it i guess bazar since I'm using relational queries and I wasn't importing my friends table because it was just saying friends: true but interesting
AlcaponeYou
AlcaponeYou•16mo ago
@minion3665 did you get this working? In your example above, you have Following = pgTable("followers", { the mismatch between Following and followers is confusing. Did you also have a separate "followings" table? nvm got this working now, I had a typo in the relationName (extra s in followings)
Oreki
Oreki•16mo ago
were you guys able to get this to work? i get an error relation "following" or "followers" does not exist 
export const userRelations = relations(users, ({ one, many }) => ({
  invitee: one(users, {
    fields: [users.invitedBy],
    references: [users.id],
  }),
  // followers: many(following, {
  //   relationName: 'followers',
  // }),
  // following: many(following, {
  //   relationName: 'following',
  // }),
  posts: many(post),
  comments: many(comment),
  likes: many(like),
}));

export const following = pgTable(
  'following',
  {
    userId: text('user_id')
      .references(() => users.id)
      .notNull(),
    followingId: text('following_id')
      .references(() => users.id)
      .notNull(),
  },
  (table) => ({
    cpk: primaryKey(table.userId, table.followingId),
  }),
);

export const FollowingRelations = relations(following, ({ one }) => ({
  user: one(users, {
    fields: [following.userId],
    references: [users.id],
    relationName: 'followers',
  }),
  following: one(users, {
    fields: [following.followingId],
    references: [users.id],
    relationName: 'following',
  }),
}));
export const userRelations = relations(users, ({ one, many }) => ({
  invitee: one(users, {
    fields: [users.invitedBy],
    references: [users.id],
  }),
  // followers: many(following, {
  //   relationName: 'followers',
  // }),
  // following: many(following, {
  //   relationName: 'following',
  // }),
  posts: many(post),
  comments: many(comment),
  likes: many(like),
}));

export const following = pgTable(
  'following',
  {
    userId: text('user_id')
      .references(() => users.id)
      .notNull(),
    followingId: text('following_id')
      .references(() => users.id)
      .notNull(),
  },
  (table) => ({
    cpk: primaryKey(table.userId, table.followingId),
  }),
);

export const FollowingRelations = relations(following, ({ one }) => ({
  user: one(users, {
    fields: [following.userId],
    references: [users.id],
    relationName: 'followers',
  }),
  following: one(users, {
    fields: [following.followingId],
    references: [users.id],
    relationName: 'following',
  }),
}));
AlcaponeYou
AlcaponeYou•16mo ago
Uncomment the blocks
Oreki
Oreki•16mo ago
Lmao they're commented because if I uncomment them it breaks Will you be able to show me just the followers/following part of your schema? If not that's fine, what I thought of doing is, I'd just run raw SQL command to generate followers/following many to many relational tables and introspect it with drizzle kit
AlcaponeYou
AlcaponeYou•16mo ago
Yes I'll be home in about an hour
Oreki
Oreki•16mo ago
Thank you!
AlcaponeYou
AlcaponeYou•16mo ago
Instead of a "followings" table, I have a "relations" table that describes friendship + followerships, but the idea is the same: users: 
export const usersRelations = ormRelations(users, ({ many }) => ({
  followers: many(relations, { relationName: RELATION_NAMES.FOLLOWERS }),
  followings: many(relations, { relationName: RELATION_NAMES.FOLLOWINGS }),
}))
export const usersRelations = ormRelations(users, ({ many }) => ({
  followers: many(relations, { relationName: RELATION_NAMES.FOLLOWERS }),
  followings: many(relations, { relationName: RELATION_NAMES.FOLLOWINGS }),
}))
relations: 
export const relations = sqliteTable(
  'relations',
  {
    userId: text('user_id')
      .references(() => users.id, { onDelete: 'cascade' })
      .notNull(),
    friendId: text('friend_id')
      .references(() => users.id, {
        onDelete: 'cascade',
      })
      .notNull(),
    status: int('status').default(RelationsStatus.RequestedFriend).notNull(),
  },
  (table) => ({
    pk: primaryKey(table.userId, table.friendId),
    user: index('relations_user_idx').on(table.userId),
    friend: index('relations_friend_idx').on(table.friendId),
  })
)
export const relationsRelations = ormRelations(relations, ({ one }) => ({
  user: one(users, {
    fields: [relations.userId],
    references: [users.id],
    relationName: RELATION_NAMES.FOLLOWERS,
  }),
  friend: one(users, {
    fields: [relations.friendId],
    references: [users.id],
    relationName: RELATION_NAMES.FOLLOWINGS,
  }),
}))
export const relations = sqliteTable(
  'relations',
  {
    userId: text('user_id')
      .references(() => users.id, { onDelete: 'cascade' })
      .notNull(),
    friendId: text('friend_id')
      .references(() => users.id, {
        onDelete: 'cascade',
      })
      .notNull(),
    status: int('status').default(RelationsStatus.RequestedFriend).notNull(),
  },
  (table) => ({
    pk: primaryKey(table.userId, table.friendId),
    user: index('relations_user_idx').on(table.userId),
    friend: index('relations_friend_idx').on(table.friendId),
  })
)
export const relationsRelations = ormRelations(relations, ({ one }) => ({
  user: one(users, {
    fields: [relations.userId],
    references: [users.id],
    relationName: RELATION_NAMES.FOLLOWERS,
  }),
  friend: one(users, {
    fields: [relations.friendId],
    references: [users.id],
    relationName: RELATION_NAMES.FOLLOWINGS,
  }),
}))
and an example query: 
export async function getFriends(id: string) {
  return config.db.query.users.findFirst({
    where: eq(users.id, id),
    columns: {},
    with: {
      followers: {
        columns: {},
        where: sql`${relations.status} & ${
          RelationsStatus.Friend | RelationsStatus.Followed
        }`,
        with: {
          friend: true,
        },
      },
      followings: {
        columns: {},
        where: sql`${relations.status} & ${
          RelationsStatus.Friend | RelationsStatus.Followed
        }`,
        with: {
          user: true,
        },
      },
    },
  })
}
export async function getFriends(id: string) {
  return config.db.query.users.findFirst({
    where: eq(users.id, id),
    columns: {},
    with: {
      followers: {
        columns: {},
        where: sql`${relations.status} & ${
          RelationsStatus.Friend | RelationsStatus.Followed
        }`,
        with: {
          friend: true,
        },
      },
      followings: {
        columns: {},
        where: sql`${relations.status} & ${
          RelationsStatus.Friend | RelationsStatus.Followed
        }`,
        with: {
          user: true,
        },
      },
    },
  })
}
Oreki
Oreki•16mo ago
Oh this is advanced, I'll try recreating this as per my following/followers tomorrow, thanks! this seems to be working, thank you!
AlcaponeYou
AlcaponeYou•16mo ago
np, did you figure out what you did wrong before
Oreki
Oreki•16mo ago
I did not, but I learned that having a many-to-many relation would be an advanced concept for me I'm creating a blog application so I need to create relations for bookmarks etc A post can be bookmarked by many, a user can bookmark many posts etc
Want results from more Discord servers?
Add your server