Drizzle Team•4mo ago

Defining disambiguating one-to-one relational query

I'm working with a schema that involves one-to-one disambiguating relationships, and I'm encountering an issue when trying to define these relationships concisely. Here's a simplified version of my schema:

model Transfer {
  id          String            @id
  salePayment Sale? @relation("Sale_payment")
  saleTax     Sale? @relation("Sale_tax")
}

model Sale {
  id        String                 @id
  payment   Transfer @relation("Sale_payment", fields: [paymentId], references: [id])
  paymentId String                 @unique
  tax       Transfer @relation("Sale_tax", fields: [taxId], references: [id])
  taxId     String                 @unique
}

model Transfer {
  id          String            @id
  salePayment Sale? @relation("Sale_payment")
  saleTax     Sale? @relation("Sale_tax")
}

model Sale {
  id        String                 @id
  payment   Transfer @relation("Sale_payment", fields: [paymentId], references: [id])
  paymentId String                 @unique
  tax       Transfer @relation("Sale_tax", fields: [taxId], references: [id])
  taxId     String                 @unique
}

Ideally, I'd like to define the relationships in Transfer by just specifying the relationName, similar to this:

export const transfersRelations = relations(transfers, ({ one }) => ({
  salePayment: one(sales, { relationName: "Sale_payment" }),
  saleTax: one(sales, { relationName: "Sale_tax" })
}));

export const transfersRelations = relations(transfers, ({ one }) => ({
  salePayment: one(sales, { relationName: "Sale_payment" }),
  saleTax: one(sales, { relationName: "Sale_tax" })
}));

However, this approach results in a type error, suggesting that specifying only the relationName is not sufficient. The current workaround involves specifying the fields and references explicitly, which makes these fields required:

...
salePayment: one(sales, {
  relationName: "Sale_payment"
  fields: [transfers.id],
  references: [sales.paymentId],
}),
...

...
salePayment: one(sales, {
  relationName: "Sale_payment"
  fields: [transfers.id],
  references: [sales.paymentId],
}),
...

Similarly, one-to-many has solution for this https://orm.drizzle.team/docs/rqb#disambiguating-relations My question is: Is there a way to define these one-to-one disambiguating relationships in Drizzle without making the fields required, preferably using just the relationName? If not, are there any plans to support this kind of concise relationship definition in the future?

3 Replies

rphlmr ⚡•4mo ago

👋 is it what you are trying to achieve? (v1) https://drizzle.run/tbhc04badz7ftakv6o8tpk6p

Drizzle Run

One-to-one disambiguating relationships - Drizzle Run

rphlmr ⚡•4mo ago

~~To achieve that without foreign keys in ⁠transfer, Prisma creates a top-level logic (another table or pure JS). Drizzle only does what SQL would do.~~ edit: actually there is a simpler version without foreign keys in transfer

rphlmr ⚡•4mo ago

nevermind, simpler version: https://drizzle.run/kj8f8jjoe8wfjeow14of8foi

Drizzle Run

One-to-one disambiguating relationships - Drizzle Run

Gaming

Programming

Defining disambiguating one-to-one relational query