https://www.prisma.io/ logo
Join Slack
Powered by
I guess I have kind of a weird use case here but l...
# orm-help
a
I guess I have kind of a weird use case here but let me try to explain. I’m trying to create a project management system where users will only be able to view and modify projects belonging only to their organization. The models I’m currently working on are Projects, Features and FeatureDetails. A Project has many Features and a Feature has many FeatureDetails. What I’m doing to reduce the number of times Prisma hits the database is calling prisma.project.update() to create a feature because it returns the entire project which I can then pass to a function that mutates the original object on the client side. It’s nice and clean but I’ve run into a couple of problems on the way. The first problem I want to mention is that it’s inconvenient that I have to create a unique index on the project’s id and companyId just to be able to ensure that a user may only access projects belonging to their organization when using the update method. In my opinion it doesn’t make sense that you can’t specify optional fields like the companyId in the where clause even though it’s not technically absolutely necessary to find the project, it would cover my use case. The other problem I’ve run into is that I can’t figure out how to nest a new FeatureDetail into an existing Feature in an existing Project using the update method on project. Is that possible?
Okay, color me surprised. I figured out that it’s totally possible and that you can continue chaining update with where and create to do exactly what I want. Really impressive.
But I’m still wondering what you guys think about the first part.
r
Hey @Aaron Fulkerson 👋 By the first part, do you mean this one?
In my opinion it doesn’t make sense that you can’t specify optional fields like the companyId in the where clause even though it’s not technically absolutely necessary to find the project, it would cover my use case
a
@Ryan that’s what I mean, yes. I feel like I should be allowed to specify optional fields for “security” or other reasons.
r
Yes you can use findFirst for that as you can specify optional fields and get the project that you require. Does that cover your use case? Also I think that a composite index on projectId and companyId is not mandatory just to prevent the user from accessing other projects. This is kind of a high-level implementation of what you have explained in Prisma:
Copy code
model User {
  id             Int           @id @default(autoincrement())
  name           String
  organization   Organization? @relation(fields: [organizationId], references: [id])
  projects       Project[]
  organizationId Int?
}

model Project {
  id             Int          @id @default(autoincrement())
  name           String
  usersWorking   User[]
  belongsTo      Organization @relation(fields: [organizationId], references: [id])
  features       Feature[]
  organizationId Int
}

model Feature {
  id             Int             @id @default(autoincrement())
  name           String
  belongsTo      Project         @relation(fields: [projectId], references: [id])
  featureDetails FeatureDetail[]
  projectId      Int
}

model FeatureDetail {
  id        Int     @id @default(autoincrement())
  belongsTo Feature @relation(fields: [featureId], references: [id])
  featureId Int
}
I think that you can add a rule that check for the projects and only allow the User query for those projects that they are working on.
a
findFirst is great for that but let’s say I want to update a project. The options for where are rather limited in that case.
So as another example I’m trying to delete a FeatureDetail based on its ID and only if it’s not complete. Here’s the code for it:
Copy code
return await db.project.update({
  data: {
    features: {
      update: {
        data: { featureDetails: { delete: { id_complete: { id, complete: false } } } },
        where: { id: featureId },
      },
    },
  },
  include: {
    company: true,
    features: { include: { featureDetails: { orderBy: { id: 'asc' } } } },
    invoices: true,
  },
  where: { id: projectId },
})
r
Yes currently that’s not possible without using the 
many
version like 
deleteMany
.
Open in Slack
PreviousNext
Powered by