Reizel Posts - Answer Overflow

Reizel

•Created by Reizel on 10/12/2024 in #help

How to update/insert id of a reference when I have a different constraint

I am trying to update/insert a record that has an fk constraint on another table that has an id as the primary key, and a unique constraint on appId and code. At the time of update/insert, I only have the unique constraint. How can I update/insert the record without fetching the id? Something like this:

const table1 = pgTable(
  "table_1",
  {
    id: serial("id").notNull(),
    // appId refers to a 3rd table
    appId: integer("app_id").notNull(),
    code: varchar("code").notNull(),
  },
  (t) => ({
    unq: unique().on(t.appId, t.code),
  })
);

const table2 = pgTable(
  "table_2",
  {
    userId: integer("user_id").references(() => userTable.id).notNull(),
    appId: integer("app_id").references(() => table3.id).notNull(),
    achievementId: integer("achievement_id").references(() => table1.id).notNull(),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.userId, t.appId, t.achievementId] })
  }),
);

// When upserting my record, I don't have achievementId, but I have the appId and code:

await db
  .insert(table_2)
  .values({
    userId,
    appId,
    achievementId: sqlMagicToGetAchievementId(appId, code),
  })
  .onConflictDoUpdate({
    target: [
      table_2,userId,
      table_2.appId,
      table_3.achievementId,
    ],
    set: { ... },
  })

const table1 = pgTable(
  "table_1",
  {
    id: serial("id").notNull(),
    // appId refers to a 3rd table
    appId: integer("app_id").notNull(),
    code: varchar("code").notNull(),
  },
  (t) => ({
    unq: unique().on(t.appId, t.code),
  })
);

const table2 = pgTable(
  "table_2",
  {
    userId: integer("user_id").references(() => userTable.id).notNull(),
    appId: integer("app_id").references(() => table3.id).notNull(),
    achievementId: integer("achievement_id").references(() => table1.id).notNull(),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.userId, t.appId, t.achievementId] })
  }),
);

// When upserting my record, I don't have achievementId, but I have the appId and code:

await db
  .insert(table_2)
  .values({
    userId,
    appId,
    achievementId: sqlMagicToGetAchievementId(appId, code),
  })
  .onConflictDoUpdate({
    target: [
      table_2,userId,
      table_2.appId,
      table_3.achievementId,
    ],
    set: { ... },
  })

8 replies

Gaming

Programming