DT
Drizzle Team•13mo ago
Hocus

`{ onDelete: "cascade" }` when the entity is in one-to-one relationship with multiple entities?

hey guys, whats the best way to use { onDelete: "cascade" } when the entity is in one-to-one relationship with multiple entities? I have a users, friendRequests and locations. users, friendRequests both have a location, and if a user or friendRequests gets deleted, I want to delete the corresponding location as well. 
export const users = pgTable(
  "users",
  {
    id: text("id")
    .primaryKey()
    locationId: text("locationId").references(() => locations.id),
  },
);

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

export const friendRequests = pgTable(
  "friendRequests",
  {
    sentById: text("sentById")
      .references(() => users.id, { onDelete: "cascade" }),
    receivedById: text("receivedById")
      .references(() => users.id, { onDelete: "cascade" }),
    locationId: text("locationId").references(() => locations.id),
  },
  (table) => ({
    pk: primaryKey(table.sentById, table.receivedById),
    sentBy: index("friendRequests_sentBy_idx").on(table.sentById),
    receivedBy: index("friendRequests_receivedBy_idx").on(table.receivedById),
  })
);

export const friendRequestsRelations = relations(friendRequests, ({ one }) => ({
  sentBy: one(users, {
    fields: [friendRequests.sentById],
    references: [users.id],
    relationName: RELATION_NAMES.FRIEND_REQ_SENT,
  }),
  receivedBy: one(users, {
    fields: [friendRequests.receivedById],
    references: [users.id],
    relationName: RELATION_NAMES.FRIEND_REQ_RECEIVED,
  }),
  location: one(locations, {
    fields: [friendRequests.locationId],
    references: [locations.id],
  }),
}));

export const locations = pgTable("location", {
  id: text("id")
    .primaryKey()
  lat: doublePrecision("lat").notNull(),
  lng: doublePrecision("lng").notNull(),
});
export const users = pgTable(
  "users",
  {
    id: text("id")
    .primaryKey()
    locationId: text("locationId").references(() => locations.id),
  },
);

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

export const friendRequests = pgTable(
  "friendRequests",
  {
    sentById: text("sentById")
      .references(() => users.id, { onDelete: "cascade" }),
    receivedById: text("receivedById")
      .references(() => users.id, { onDelete: "cascade" }),
    locationId: text("locationId").references(() => locations.id),
  },
  (table) => ({
    pk: primaryKey(table.sentById, table.receivedById),
    sentBy: index("friendRequests_sentBy_idx").on(table.sentById),
    receivedBy: index("friendRequests_receivedBy_idx").on(table.receivedById),
  })
);

export const friendRequestsRelations = relations(friendRequests, ({ one }) => ({
  sentBy: one(users, {
    fields: [friendRequests.sentById],
    references: [users.id],
    relationName: RELATION_NAMES.FRIEND_REQ_SENT,
  }),
  receivedBy: one(users, {
    fields: [friendRequests.receivedById],
    references: [users.id],
    relationName: RELATION_NAMES.FRIEND_REQ_RECEIVED,
  }),
  location: one(locations, {
    fields: [friendRequests.locationId],
    references: [locations.id],
  }),
}));

export const locations = pgTable("location", {
  id: text("id")
    .primaryKey()
  lat: doublePrecision("lat").notNull(),
  lng: doublePrecision("lng").notNull(),
});
9 Replies
Hocus
HocusOP•13mo ago
I tried to add onDelete to the users, friendRequests tables but this leads to the inverse effect I am trying to archive - it deletes the user/friendRequests when a location is deleted. 
...
locationId: text("locationId").references(() => locations.id, { onDelete: "cascade" }),
...
...
locationId: text("locationId").references(() => locations.id, { onDelete: "cascade" }),
...
Because the friendRequests table doesnt have an id column, I am having trouble to figure out how to do this?
Angelelz
Angelelz•13mo ago
There are several ways people do one-to-one tables The best way imo is to share the same primary key You'll have a table which will be your main table In you case it should be users You will NOT define the reference in your main table, but in your locations table You should add a userId column in your locations, make it reference your users.id and imo it should be the primaryKey Just to make sure it is unique and is true one-to-one
Hocus
HocusOP•13mo ago
if I make the userId the primary key, wont that lock me out of having a friendRequest with a location as well? And my friendRequest table doesn't have a ID, but a derived primaryKey out of sentBy and receivedBy. I want to attach a location to both, user and friendRequest
Angelelz
Angelelz•13mo ago
Lol I didn't notice that You're trying to implement polymorphic association Search for that issue in GH and you'll find several ways to do it
Hocus
HocusOP•13mo ago
thanks if I understand correct, there are two approaches. - multiple fk columns, and only one for each row would be set, the others are null. or - adding a type and a type specific id I need to let that sink in and wrap my head around this. how would that work with my friendRequests table since it doesnt have a id column but derives the PK from pk: primaryKey(table.sentById, table.receivedById) I dont really understand this part: "If you don't include that where, you will fetch comments from cities and countries that have id of 1. I think adding the commentType is a cleaner solution IMO." why would there be multiple comments with the same ID of 1? Arent comment.id unique by the primary key constraint?
Angelelz
Angelelz•13mo ago
This is for the 2nd approach. With this, you won't have foreign key constrains There is no way in SQL to have the same column be a foreign key of different tables at the same tiem time
Hocus
HocusOP•13mo ago
so in my case I have a one-to-one relation and I gave the 1st approach a try. It works with: - multiple FK columns, all null except for 1 - onDelete cascade But has a weird sideeffect: - this creates a weird flow in which I do the following 1. insert new friendRequest and return insertedId 2. insert new location with id from new friendRequest and return insertedId 3. update user created in step 1 with location id returned in step 2 the 2nd approach I am lost, I tried it but didnt get it to work
Angelelz
Angelelz•13mo ago
If you want to keep foreign key integrity, you have to use this approach This workflow isn't too different from any other relational with foreign key approach
Hocus
HocusOP•13mo ago
Yeah I think I am pretty happy with it. Appreciate your help!
Want results from more Discord servers?
Add your server