Is there a way to limit the number of records for a user using Prisma?-CodePudding

The simplest example I can give, is a User that can create multiple Posts. A one-to-many relationship where multiple posts can be tied to a single user.

But what if I want the User to only be able to have a max of 10 Posts? Ideally there'd be some kind of query I can run when creating a new Post, and if the limit has been reached, to reject creating that Post (or possibly replace a Post).

I'm kind of stumped on this. And I'm not sure if there is a way I can model this to create the desired outcome.

Otherwise, the only real solution I see is to fetch all Posts for a User, and count them before trying to create a new Post. But that would require two calls to the db instead of one which is the problem I am trying to avoid.

CodePudding user response：

Have you considered a database trigger? Below example is taken from this StackExhange post:

CREATE OR REPLACE FUNCTION check_number_of_row()
RETURNS TRIGGER AS
$body$
BEGIN
    IF (SELECT count(*) FROM your_table) > 10
    THEN 
        RAISE EXCEPTION 'INSERT statement exceeding maximum number of rows for this table' 
    END IF;
END;
$body$
LANGUAGE plpgsql;

CREATE TRIGGER tr_check_number_of_row 
BEFORE INSERT ON your_table
FOR EACH ROW EXECUTE PROCEDURE check_number_of_row();

Unfortunately triggers don't seem to be supported in Prisma yet, so you will have to define it in SQL: https://github.com/prisma/prisma/discussions/2382

CodePudding user response：

You can achieve it with interactive transaction, here's my example code:

const createPost = async (post, userId) => {
  return prisma.$transaction(async (prisma) => {
    // 1. Count current total user posts
    const currentPostCount = await prisma.posts.count({
      where: {
        user_id: userId,
      },
    })

    // 2. Check if user can create posts
    if (currentPostCount >= 10) {
      throw new Error(`User ${userId} has reached maximum posts}`)
    }

    // TODO
    // 3. Create your posts here
    await prisma.posts.create({
      data: post
    })
  })
}