Drizzle Team•12mo ago

How do you define one-to-one relations in the same table?

export const listings = pgTable(
  'Listing',
  {
    id: text('id')
      .primaryKey()
      .$defaultFn(() => sql`gen_random_uuid()::text`)
      .notNull(),
    physicalListingId: text('physicalListingId'),
  },
  (table) => ({
    listingPhysicalListingIdFkey: foreignKey({
      columns: [table.physicalListingId],
      foreignColumns: [table.id],
      name: 'Listing_physicalListingId_fkey'
    })
      .onUpdate('cascade')
      .onDelete('set null'),
    physicalListingIdKey: uniqueIndex('Listing_physicalListingId_key').on(table.physicalListingId),
  })
);

export const listings = pgTable(
  'Listing',
  {
    id: text('id')
      .primaryKey()
      .$defaultFn(() => sql`gen_random_uuid()::text`)
      .notNull(),
    physicalListingId: text('physicalListingId'),
  },
  (table) => ({
    listingPhysicalListingIdFkey: foreignKey({
      columns: [table.physicalListingId],
      foreignColumns: [table.id],
      name: 'Listing_physicalListingId_fkey'
    })
      .onUpdate('cascade')
      .onDelete('set null'),
    physicalListingIdKey: uniqueIndex('Listing_physicalListingId_key').on(table.physicalListingId),
  })
);

export const listingRelations = relations(listings, ({ many, one }) => ({
  digitalListing: one(listings),
  physicalListing: one(listings, {
    fields: [listings.physicalListingId],
    references: [listings.id]
  }),
}));

export const listingRelations = relations(listings, ({ many, one }) => ({
  digitalListing: one(listings),
  physicalListing: one(listings, {
    fields: [listings.physicalListingId],
    references: [listings.id]
  }),
}));

How would you define a relationName here if there is no field for the id of the digitalListing (this schema was working in prisma world) basically i have a table of listings, and one can be linked to another the problem is that if i want to define a relationName in the config for digitalListing, it expects fields and references

9 Replies

jakeleventhalOP•12mo ago

In prisma, it is defined like so:

  /// The digital listing associated with the listing.
  digitalListing                        Listing?          @relation(name: "digitalListing")

  /// The physical listing associated with the digital listing.
  physicalListing          Listing?               @relation(fields: [physicalListingId], references: [id], name: "digitalListing")
  /// The id of the physical listing associated with the digital listing.
  physicalListingId        String?                @unique

  /// The digital listing associated with the listing.
  digitalListing                        Listing?          @relation(name: "digitalListing")

  /// The physical listing associated with the digital listing.
  physicalListing          Listing?               @relation(fields: [physicalListingId], references: [id], name: "digitalListing")
  /// The id of the physical listing associated with the digital listing.
  physicalListingId        String?                @unique

bump

myydraal•12mo ago

do you have a table for digital listings? it doesn't make sense for a table to reference themselves as the point of a reference is to provide a joining point between two tables. I am not sure what you were doing in prisma did anything either? I think what you want is either a new field - so a table that has digitalListing, physicalListing as the two fields in a row, as it seems like you may always want that relationship - Otherwise, you want two tables - a physical listing table and digital listing table, each with relavent fields, and then each referencing the other by their unique id If you just want one table - you don't need to define a relationship at all - the fact they are in the same row is the 'relation' by itself. relations are only meant for joining two tables

jakeleventhalOP•12mo ago

the goal that i am trying to accomplish is that i can link digital listings to physical listings. both are in the Listing table. the only distinction between the two types is a boolean, Listing.isDigital in the database, im trying to create the association of linking digital listings to physical listings. the point of this is so that if i want to fetch a digital listing, i can easily also fetch the corresponding physical listing by just doing with: { physicalListing: true } or vice versa

Angelelz•12mo ago

I can't say I've seen this pattern before. A one-to-one self relation So how would you like to link a lising to a digitalListing? I don't think this is a one-to-one self relation though. The way it is defined, doesn't prevent a physicalListing to have more that one listings Meaning more than one listing can have the same physicalListing. So the way this is defined, it's a one-to-many self relation Is that what you want?

jakeleventhalOP•12mo ago

well it should be one-to-one because of the unique constriant on physicalListingId

Angelelz•12mo ago

Ok, I just didn't see it there Usually one-to-one relations share the primary key to ensure uniqueness This seems like a constrained one-to-many relation In any case, Why don't you just define the digitalListing the same way you defined the physical one? With a foreign key

jakeleventhalOP•12mo ago

i suppose that could work as well. so when i make the connection, just make sure assign both foreign keys

Angelelz•12mo ago

I'm curious how did that work before. How did you connect the listing to a digitalListing? Or was it itself?

jakeleventhalOP•12mo ago

a digital listing and a physical listing were each separate rows in the database

Gaming

Programming

How do you define one-to-one relations in the same table?