CODEWITHSUNDEEP
mysqlgraphqlprisma
devautor
devautor

Reputation: 2586

Prisma Datamodel: Primary key as a combination of two relational models

I have a problem in Prisma data modeling where I have to constrain that a user can submit only one review for a product. I have following design for the non-constrained situation.

Should Customer and Product be combined into a primary key in ProductReview model, or should this constraint be imposed at the application server level, and not at the database level?

Datamodel for now (non-constrained version):

type Product {
  id: ID! @unique
  title: String!
  reviews: [ProductReview!]! @relation(name: "ProductReviews", onDelete: CASCADE)
}

type Customer {
  id: ID! @unique
  email: String @unique
}

type ProductReview {
  id: ID! @unique
  forProduct: Product! @relation(name: "ProductReviews", onDelete: SET_NULL)
  byCustomer: Customer!
  review: String!
  ratinng: Float!
}

Upvotes: 11

Views: 22771

Answers (5)

Cymen
Cymen

Reputation: 14419

Prisma v2 introduced composite primary keys:

https://newreleases.io/project/github/prisma/prisma/release/2.0.0-preview023

An example from that link:

model User {
  firstName String
  lastName  String
  email     String

  @@id([firstName, lastName])
}

So in the given question example, it is now possible to add to ProductReview:

@@id([id, forProduct])

Upvotes: 21

unloco
unloco

Reputation: 7320

In my case, it was enough to do a string concatenation on the id

So the id is for example "120-15" for product #120 and customer #15

Upvotes: 1

Sanjay SP
Sanjay SP

Reputation: 150

There is a workaround. To implement the concept of multiple primary keys like SQL. The idea is simple, Create one more field called "UniqueCustomerReview" under "ProductReview". And while on mutation set the "UniqueCustomerReview" value to "[customerEmail]_[productID]". SO we can now use the default unique of the prisma.

Your data model will look like:

type Product {
id: ID! @unique
  title: String!
  reviews: [ProductReview!]! @relation(name: "ProductReviews", onDelete: CASCADE)
}

type Customer {
  id: ID! @unique
  email: String @unique
}

type ProductReview {
  id: ID! @unique
  forProduct: Product! @relation(name: "ProductReviews", onDelete: SET_NULL)
  byCustomer: Customer!
  review: String!
  ratinng: Float!
  UniqueCustomerReview:String!  # adding a extra field
}

Creating or Mutation query :

mutation{
createProductReview(
data:{
forProduct: {"connect":{"id":"<Replacec_with_product_id>"}}
byCustomer: {"connect":{"email":"<Replacec_with_customer_email>"}}
review: "my product review..."
ratinng: 5.0
UniqueCustomerReview:"[email protected]_<Poductid>" # replace the string with user email and product id. this will create a unique product review for the user alone.
      }
                   )
{
UniqueCustomerReview
# ... any requied fields
}
        }

Upvotes: 2

nburk
nburk

Reputation: 22731

I have to constrain that a user can submit only one review for a product. I have following design for the non-constrained situation.

Unfortunately, this is currently not possible with Prisma. There already is an open feature request asking for this functionality, please leave your 👍 on the issue!

To get that functionality in your application, you'll need to implement that constraint manually on the application layer (e.g. express, apollo-server or graphql-yoga).

You can take a look at this page of How to GraphQL where there's a similar situation with the User, Link and Vote types. Here's how the resolver to create a Vote and ensurs no votes from that user already exist is implemented with graphql-yoga:

async function vote(parent, args, context, info) {
  // 1
  const userId = getUserId(context)

  // 2
  const linkExists = await context.db.exists.Vote({
    user: { id: userId },
    link: { id: args.linkId },
  })
  if (linkExists) {
    throw new Error(`Already voted for link: ${args.linkId}`)
  }

  // 3
  return context.db.mutation.createVote(
    {
      data: {
        user: { connect: { id: userId } },
        link: { connect: { id: args.linkId } },
      },
    },
    info,
  )
}

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

I will answer from the MySQL perspective. If you want to enforce that a given customer can only be associated with a given product once, then you should make (cusotmer_id, product_id) a unique key (maybe primary) in the ProductReview table:

ALTER TABLE ProductReview ADD UNIQUE KEY uk_cust_prod (customer_id, product_id);

This means that any attempt to insert a record for a given customer and product, when such a relation already exists, would fail at the database level.

If you also want to add an application level check for this, you of course may do so, and perhaps handle it there first.

Upvotes: 1

Related Questions