Mixing
Prisma Datamodel: Primary key as a combination of two relational models
up vote
1
down vote
favorite
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
CustomerandProductbe combined into a primary key inProductReviewmodel, 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!
}
mysql graphql prisma
asked 2 days ago
devautor
8411616
add a comment |
up vote
1
down vote
favorite
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
CustomerandProductbe combined into a primary key inProductReviewmodel, 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!
}
mysql graphql prisma
asked 2 days ago
devautor
8411616
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
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
CustomerandProductbe combined into a primary key inProductReviewmodel, 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!
}
mysql graphql prisma
asked 2 days ago
devautor
8411616
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
CustomerandProductbe combined into a primary key inProductReviewmodel, 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!
}
mysql graphql prisma
mysql graphql prisma
asked 2 days ago
devautor
8411616
asked 2 days ago
devautor
8411616
asked 2 days ago
devautor
8411616
asked 2 days ago
devautor
8411616
asked 2 days ago
devautor
8411616
8411616
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
up vote
2
down vote
accepted
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,
)
}
answered 2 days ago
nburk
14.2k106093
1
Hi @nburk, so the idea is to handle this at the application server level for now. Got it! Have raised a feature request with the same body too. Thanks!
– devautor
2 days ago
add a comment |
up vote
1
down vote
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.
answered 2 days ago
Tim Biegeleisen
209k1380129
Thanks @tim, since Prisma doesn't support this, application level check seems the only way. Would you suggest a "right way" to handle this there?
– devautor
2 days ago
add a comment |
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
accepted
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,
)
}
answered 2 days ago
nburk
14.2k106093
1
Hi @nburk, so the idea is to handle this at the application server level for now. Got it! Have raised a feature request with the same body too. Thanks!
– devautor
2 days ago
add a comment |
up vote
2
down vote
accepted
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,
)
}
answered 2 days ago
nburk
14.2k106093
1
Hi @nburk, so the idea is to handle this at the application server level for now. Got it! Have raised a feature request with the same body too. Thanks!
– devautor
2 days ago
add a comment |
up vote
2
down vote
accepted
up vote
2
down vote
accepted
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,
)
}
answered 2 days ago
nburk
14.2k106093
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,
)
}
answered 2 days ago
nburk
14.2k106093
edited yesterday
answered 2 days ago
nburk
14.2k106093
answered 2 days ago
nburk
14.2k106093
answered 2 days ago
nburk
14.2k106093
14.2k106093
1
Hi @nburk, so the idea is to handle this at the application server level for now. Got it! Have raised a feature request with the same body too. Thanks!
– devautor
2 days ago
add a comment |
1
Hi @nburk, so the idea is to handle this at the application server level for now. Got it! Have raised a feature request with the same body too. Thanks!
– devautor
2 days ago
1
1
Hi @nburk, so the idea is to handle this at the application server level for now. Got it! Have raised a feature request with the same body too. Thanks!
– devautor
2 days ago
Hi @nburk, so the idea is to handle this at the application server level for now. Got it! Have raised a feature request with the same body too. Thanks!
– devautor
2 days ago
add a comment |
up vote
1
down vote
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.
answered 2 days ago
Tim Biegeleisen
209k1380129
Thanks @tim, since Prisma doesn't support this, application level check seems the only way. Would you suggest a "right way" to handle this there?
– devautor
2 days ago
add a comment |
up vote
1
down vote
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.
answered 2 days ago
Tim Biegeleisen
209k1380129
Thanks @tim, since Prisma doesn't support this, application level check seems the only way. Would you suggest a "right way" to handle this there?
– devautor
2 days ago
add a comment |
up vote
1
down vote
up vote
1
down vote
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.
answered 2 days ago
Tim Biegeleisen
209k1380129
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.
answered 2 days ago
Tim Biegeleisen
209k1380129
answered 2 days ago
Tim Biegeleisen
209k1380129
answered 2 days ago
Tim Biegeleisen
209k1380129
answered 2 days ago
Tim Biegeleisen
209k1380129
209k1380129
Thanks @tim, since Prisma doesn't support this, application level check seems the only way. Would you suggest a "right way" to handle this there?
– devautor
2 days ago
add a comment |
Thanks @tim, since Prisma doesn't support this, application level check seems the only way. Would you suggest a "right way" to handle this there?
– devautor
2 days ago
Thanks @tim, since Prisma doesn't support this, application level check seems the only way. Would you suggest a "right way" to handle this there?
– devautor
2 days ago
Thanks @tim, since Prisma doesn't support this, application level check seems the only way. Would you suggest a "right way" to handle this there?
– devautor
2 days ago
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53373101%2fprisma-datamodel-primary-key-as-a-combination-of-two-relational-models%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
