Drizzle Team•17mo 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•17mo ago

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

KitAstroOP•17mo 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•17mo 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?

KitAstroOP•17mo ago

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

Asseater Peter•17mo 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

KitAstroOP•17mo 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•17mo 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.

KitAstroOP•17mo 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•17mo 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

KitAstroOP•17mo ago

I have the soft relations all set up already ^^

Asseater Peter•17mo ago

Ah ok that's good

KitAstroOP•17mo 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•17mo 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

KitAstroOP•17mo ago

I want an "null" id

Asseater Peter•17mo 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

KitAstroOP•17mo ago

But don't know how to do that

Asseater Peter•17mo ago

Well, just don't do .notNull() and then on creation you wouldn't be forced to reference a purchase Did that make sense? I can take a quick break and code something to show what I mean

KitAstroOP•17mo ago

I changed it and it worked I was obviously messing around with the database structure trying to get the correct structure

Asseater Peter•17mo ago

But wouldn't a refund forced to reference a purchase make more sense? I mean, I don't know your usecase so maybe not but it just makes more sense in my brain

KitAstroOP•17mo ago

I'm probably overthinking it I want a refund that applies generally

Asseater Peter•17mo ago

idk that makes no sense to me I mean, if I think of it like a discount or coupon or something then yeah, but then wouldn't you want a many-to-many?

Gaming

Programming

how to use serial within compound primary key