Drizzle Team•14mo ago

how to use serial within compound primary key

export const refund = pgTable("refund", {
  id: serial("id").primaryKey(),
  purchase_id: integer("purchase_id")
    .references(() => purchase.id)
    .default(-1)
    .notNull(),
  order: integer("order").notNull(),
  timestamp: timestamp("timestamp").defaultNow(),
})

export const refund = pgTable("refund", {
  id: serial("id").primaryKey(),
  purchase_id: integer("purchase_id")
    .references(() => purchase.id)
    .default(-1)
    .notNull(),
  order: integer("order").notNull(),
  timestamp: timestamp("timestamp").defaultNow(),
})

I want the order to go from 0 to n depending on the references shared with purchase_id

21 Replies

Asseater Peter•14mo ago

Could you explain what you mean a little more? I'm not sure I quite get ya

KitAstro•14mo ago

order is the field that determines which order the refund should be applied. each refund attaches to a purchase so its a many to one relation I want to have the order field default to its insertion order something like

order default count(purchase_id) from refund

order default count(purchase_id) from refund

i don't know exactly how to do the sql

Asseater Peter•14mo ago

I'm still uhhhh, not exactly with you on this one. Do you mean that the order field should be the count of purchases that have the purchase_id that correlates to this refund?

KitAstro•14mo ago

it should be the count of refunds linking to the purchase refunds are many purchase is one

Asseater Peter•14mo ago

Well, you definitely wouldn't put that as part of the refund table That seems like a bad idea from the get-go What you should probably do is get the purchase by the id and have a field there that's the magic sql operator thing, counting the refunds

KitAstro•14mo ago

I just want to have an order to apply the refunds in, I can obviously count it manually. But I thought about whether there was a way to do it automatically on sql-insert. ATM I'm not using it much but I just want to be sure that I'm certain about the order in which the refunds should be applied.

Asseater Peter•14mo ago

I'm honestly not sure. There's probably a way to do it, but there's no way that that's a "good idea" or optimal. However. Since the refund id is serial you can just order them by that and then the applying order is completely deterministic. Or by the timestamp. I didn't notice you had a timestamp field.

KitAstro•14mo ago

I'll program some more and find out. I don't expect this to be a problem. Maybe I'm overengineering a bit

Asseater Peter•14mo ago

Also, highly recommend setting up the relations as """proper""" relations i.e.

export const refund = pgTable("refund", {
  id: serial("id").primaryKey(),
  purchase_id: integer("purchase_id").notNull(),
  timestamp: timestamp("timestamp").defaultNow(),
})

export const RefundRelations = relations(refund , ({ one }) => ({
  purchase: one(purchase, {
    fields: [refund .purchase_id],
    references: [purchase.id],
    relationName: 'MessageSender',
  }),

export const refund = pgTable("refund", {
  id: serial("id").primaryKey(),
  purchase_id: integer("purchase_id").notNull(),
  timestamp: timestamp("timestamp").defaultNow(),
})

export const RefundRelations = relations(refund , ({ one }) => ({
  purchase: one(purchase, {
    fields: [refund .purchase_id],
    references: [purchase.id],
    relationName: 'MessageSender',
  }),

That way you can more easily do relational queries later

KitAstro•14mo ago

I have the soft relations all set up already ^^

Asseater Peter•14mo ago

Ah ok that's good

KitAstro•14mo ago

export const purchase_relations = relations(purchase, ({ many }) => ({ items: many(purchase_item), refunds: many(refund), })) export const refund_relations = relations(refund, ({ one, many }) => ({ purchase: one(purchase, { fields: [refund.purchase_id], references: [purchase.id], }), items: many(refund_item), })) message sender?

Asseater Peter•14mo ago

In that case, you don't really need

purchase_id: integer("purchase_id")
    .references(() => purchase.id)
    .default(-1)
    .notNull(),

purchase_id: integer("purchase_id")
    .references(() => purchase.id)
    .default(-1)
    .notNull(),

can just do

purchase_id: integer("purchase_id")
    .notNull(),

purchase_id: integer("purchase_id")
    .notNull(),

if you only intend on using the db with this code, that's just code that makes the db enforce that rule and since your code also enforces that anyways it seems kinda pointless

KitAstro•14mo ago

I want an "null" id

Asseater Peter•14mo ago

Whoops, I copied some code instead of writing the relation from scratch (I'm working on something on the main monitor) and I forgot to delete that part, my bad

Gaming

Programming

how to use serial within compound primary key