explain relationName please

Could anyone explain how to use relationName when you have many to many for example. Found following on github:

"relationName is already supported, just not documented. So you can use it exactly as you've described. You'll just need to add where clauses to the corresponding with entries."

"relationName is already supported, just not documented. So you can use it exactly as you've described. You'll just need to add where clauses to the corresponding with entries."

https://github.com/drizzle-team/drizzle-orm/issues/811 But doesn't explain how to use where. I have found some information about how to add it in relations, but not in the actual query.

GitHub

[FEATURE]: Allow alias for relational queries · Issue #811 · drizzl...

Describe what you want I have a use case where I need to query related data that is not always related by the same fields (i.e., it has different relations "types"), but I need to always ...

4 Replies

Piotrek•17mo ago

I'm gonna try and explain it based on my actual project. Let's say I have a users table and facilities table. I want a user to be able to create a new facility (be its owner) and to join an existing facility (be its member). I will create two table schemas for that: 1. Facilities

export const facilities = mysqlTable(
  "facilities",
  {
    id: int("id").notNull().autoincrement().primaryKey(),
    name: varchar("name", { length: 255 }).notNull(),
    ownerId: int("owner_id").notNull(),,
  },
  (table) => {
    return {
      ownerIdIdx: index("owner_id_idx").on(table.ownerId),
    };
  }
);

export const facilities = mysqlTable(
  "facilities",
  {
    id: int("id").notNull().autoincrement().primaryKey(),
    name: varchar("name", { length: 255 }).notNull(),
    ownerId: int("owner_id").notNull(),,
  },
  (table) => {
    return {
      ownerIdIdx: index("owner_id_idx").on(table.ownerId),
    };
  }
);

2. Users

export const users = mysqlTable(
  "users",
  {
    id: int("id").notNull().autoincrement().primaryKey(),
    email: varchar("email", { length: 191 }).notNull()
  }
);

export const users = mysqlTable(
  "users",
  {
    id: int("id").notNull().autoincrement().primaryKey(),
    email: varchar("email", { length: 191 }).notNull()
  }
);

3. Users -> Facilities (many-to-many joined table)

export const usersOnFacilities = mysqlTable(
  "users_on_facilities",
  {
    facilityId: int("facility_id").notNull(),
    userId: int("user_id").notNull()
  },
  (table) => {
    return {
      pk: primaryKey(table.facilityId, table.userId),
    };
  }
);

export const usersOnFacilities = mysqlTable(
  "users_on_facilities",
  {
    facilityId: int("facility_id").notNull(),
    userId: int("user_id").notNull()
  },
  (table) => {
    return {
      pk: primaryKey(table.facilityId, table.userId),
    };
  }
);

And of course corresponding relations: 1. Facilities

export const facilitiesRelations = relations(facilities, ({ many, one }) => ({
  owner: one(users, {
    fields: [facilities.ownerId],
    references: [users.id],
  }),
  users: many(usersOnFacilities)
}));

export const facilitiesRelations = relations(facilities, ({ many, one }) => ({
  owner: one(users, {
    fields: [facilities.ownerId],
    references: [users.id],
  }),
  users: many(usersOnFacilities)
}));

2. Users

export const usersRelations = relations(users, ({ many, one }) => ({
  joinedFacilities: many(usersOnFacilities),
  ownedFacilities: many(facilities)
}));

export const usersRelations = relations(users, ({ many, one }) => ({
  joinedFacilities: many(usersOnFacilities),
  ownedFacilities: many(facilities)
}));

3. Users -> Facilities (many-to-many joined table relations)

export const usersOnFacilitiesRelations = relations(usersOnFacilities, ({ one }) => ({
  school: one(facilities, {
    fields: [usersOnFacilities.facilityId],
    references: [facilities.id],
  }),
  user: one(users, {
    fields: [usersOnFacilities.userId],
    references: [users.id],
  }),
}));

export const usersOnFacilitiesRelations = relations(usersOnFacilities, ({ one }) => ({
  school: one(facilities, {
    fields: [usersOnFacilities.facilityId],
    references: [facilities.id],
  }),
  user: one(users, {
    fields: [usersOnFacilities.userId],
    references: [users.id],
  }),
}));

Now, as you can see, our user will have two arrays of facilities in relations. One of them being ownedFacilities, the second being joinedFacilities. Drizzle does not have any way to actually distinguish them. You have to specify what relation belongs in which place. That's why we have to update our code and separate those 2 situations: 1. Facilities relations

export const facilitiesRelations = relations(facilities, ({ many, one }) => ({
  owner: one(users, {
    fields: [facilities.ownerId],
    references: [users.id],
    relationName: “ownedFacilities” // ← This tells Drizzle to separate it from joinedFacilities relation
  }),
  users: many(usersOnFacilities, {
    relationName: “joinedFacilities” // ← This tells Drizzle to separate it from ownedFacilities relation
  })
}));

export const facilitiesRelations = relations(facilities, ({ many, one }) => ({
  owner: one(users, {
    fields: [facilities.ownerId],
    references: [users.id],
    relationName: “ownedFacilities” // ← This tells Drizzle to separate it from joinedFacilities relation
  }),
  users: many(usersOnFacilities, {
    relationName: “joinedFacilities” // ← This tells Drizzle to separate it from ownedFacilities relation
  })
}));

2. Users relations

export const usersRelations = relations(users, ({ many, one }) => ({
  joinedFacilities: many(usersOnFacilities, {
    relationName: “joinedFacilities” // ← This tells Drizzle to separate it from ownedFacilities relation
  }),
  ownedFacilities: many(facilities, {
    relationName: “ownedFacilities” // ← This tells Drizzle to separate it from joinedFacilities relation
  })
}));

export const usersRelations = relations(users, ({ many, one }) => ({
  joinedFacilities: many(usersOnFacilities, {
    relationName: “joinedFacilities” // ← This tells Drizzle to separate it from ownedFacilities relation
  }),
  ownedFacilities: many(facilities, {
    relationName: “ownedFacilities” // ← This tells Drizzle to separate it from joinedFacilities relation
  })
}));

3. Users -> Facilities (many-to-many joined table relations)

export const usersOnFacilitiesRelations = relations(usersOnFacilities, ({ one }) => ({
  school: one(facilities, {
    fields: [usersOnFacilities.facilityId],
    references: [facilities.id],
    relationName: “joinedFacilities” // ← This tells Drizzle to separate it from ownedFacilities relation
  }),
  user: one(users, {
    fields: [usersOnFacilities.userId],
    references: [users.id],
  }),
}));

export const usersOnFacilitiesRelations = relations(usersOnFacilities, ({ one }) => ({
  school: one(facilities, {
    fields: [usersOnFacilities.facilityId],
    references: [facilities.id],
    relationName: “joinedFacilities” // ← This tells Drizzle to separate it from ownedFacilities relation
  }),
  user: one(users, {
    fields: [usersOnFacilities.userId],
    references: [users.id],
  }),
}));

I hope that's slightly more understandable now 🙂 @robboten So, you don't use it on a query - this just tells Drizzle how to handle and distinguish multiple relations with the same entities Prisma actually has the same system and if you want to give it a read in their docs here's the link: https://www.prisma.io/docs/concepts/components/prisma-schema/relations#disambiguating-relations

Piotrek•17mo ago

Here you go, I even made a small drawing of the situation 🙂

robbotenOP•17mo ago

haha, thx! Am trying to understand how to use with, so far some progress but not fully there. Especially with many to many I get the values from the junction table instead of the one I want to connect with on the other side. But will look thru your examples. Thank you again!

Piotrek•17mo ago

I think that many-to-many actually works that way - maybe I don't know something though haha no problem!

Gaming

Programming

explain relationName please