Adding a One-to-Many Relationship on a Model with Compound ID in Prisma

[masonstevens95]

Question

Forgive my ignorance on creating schema. I am attempting to add a one to many relationship to a model with a compound ID. Here is that model:

model UserPlantSpeciesInventoried {
  userId String
  user   User   @relation("user", fields: [userId], references: [id])

  plantSpeciesId String
  plantSpecies   PlantSpecies @relation("plantSpecies", fields: [plantSpeciesId], references: [id])

  inventoryReadyByDate InventoryReadyByDate[]

  @@id([userId, plantSpeciesId])
}

This model has it's ID autogenerated. It is a unique link between a user and a "Plant Species", indicating it is in the user's inventory.

The user will have some inventory ready at different times. For example, they may have 3 plants ready in July, and 2 in August. This in my opinion would be handled well by another model here:

model InventoryReadyByDate {
  id String @id @default(cuid())
  userPlantSpeciesInventoried   UserPlantSpeciesInventoried @relation(fields: [userPlantSpeciesInventoriedId], references: [userId_plantSpeciesId])
  userPlantSpeciesInventoriedId String

  quantity Int @default(0)

  dateReadyBy DateTime
}

However Prisma throws this error

Error validating: The argument references must refer only to existing fields in the related model UserPlantSpeciesInventoried. The following fields do not exist in the related model: userId_plantSpeciesId

creating a custom ID name does not resolve this error. Thanks for helping.

How to reproduce (optional)

Expected behavior (optional)

No response

Information about Prisma Schema, Client Queries and Environment (optional)

// Add your schema.prisma

// Add any relevant Prisma Client queries here

• OS:

• Database:

• Node.js version:

• Run prisma -v to see your Prisma version and paste it

[ludralph]

Hi @masonstevens95 👋

Prisma Client does not recognize userId_plantSpeciesId as a field in the UserPlantSpeciesInventoried model. In Prisma, when you're defining a relation and specifying the fields and references arguments, the references argument should refer to a unique field or a unique combination of fields in the related model.

In your case, the UserPlantSpeciesInventoried model has a compound ID, which is a combination of userId and plantSpeciesId. When you're defining the relation in the InventoryReadyByDate model, you should reference these fields separately, not as a combined field userId_plantSpeciesId.

model InventoryReadyByDate {
  id String @id @default(cuid())
  
  userId String
  plantSpeciesId String
  userPlantSpeciesInventoried   UserPlantSpeciesInventoried @relation(fields: [userId, plantSpeciesId], references: [userId, plantSpeciesId])

  quantity Int @default(0)

  dateReadyBy DateTime
}

userId and plantSpeciesId are added as separate fields, and the relation to UserPlantSpeciesInventoried references these fields separately.

If this answers your question, it would be great if you could mark this Discussion as answered to indicate that it has been resolved.

Otherwise please let us know how else we can help you further or close the Discussion if it was resolved in some other way 🙏

[ludralph]

Hi there,

To keep our discussions organized and focused on the most relevant topics, we’re reviewing and tidying up our backlog. As part of this process, we’re closing discussions that haven’t had any recent activity and appear to be outdated.

If this discussion is still important to you or unresolved, we’d love to hear from you! Feel free to reopen it or start a new one with updated details.

For more details about our priorities and vision for the future of Prisma ORM, check out our latest blog post: https://www.prisma.io/blog/prisma-orm-manifesto.

Thank you for your understanding and being part of the community!