findOne with additional constraints

[meyer9]

Problem

Currently, you can only select by unique fields in findOne. I want to be able to add additional constraints in findOne. For example, find a record by ID that also satisfies some proprerty:

TodoItem.findOne({
  where: {
    id: 10,
    user: {
      id: 100
    }
  }
})

Solution

I want to be able to select by different additional properties in findOne. Maybe require at least one unique property, but also allow other properties. That doesn't change the fact the findOne will always return at most a single record.

This is similar to #1167 but I just want to be able to select by additional properties to @unique properties.

[Aidurber]

Without supporting additional constraints, it forces users to do patterns such as:

Getting a single item by multiple constraints

export async function getProject(user: UserDto, id: number) {
  const projects = await prisma.project.findMany({
    where: {
      id,
      organisationId: user.organisationId,
    },
  });
  return projects ? projects[0] : null;
}

Updating a single item by multiple constraints

export async function updateProject(
  user: UserDto,
  id: number,
  dto: UpdateProjectDto
) {
  await prisma.project.updateMany({
    where: {
      id,
      organisationId: user.organisationId,
    },
    data: {
      description: dto.description,
      title: dto.title,
    },
  });
  return getProject(user, id);
}

Deleting an item by multiple constraints

export async function deleteProject(user: UserDto, id: number) {
  await prisma.project.deleteMany({
    where: {
      id,
      organisationId: user.organisationId,
    },
  });
}

I tried adding @@unique([id, organisationId]) to see if it would be an escape hatch, since I can't add @unique to organisationId in my case.

Schema

model Project {
  @@map("projects")
  @@unique([id, organisationId])
  id             Int           @default(autoincrement()) @id
  title          String
  description    String?
  organisation   Organisation  @relation(fields: [organisationId], references: [id])
  organisationId Int
  tasks          Task[]
  createdAt      DateTime      @default(now())
  updatedAt      DateTime      @updatedAt
  projectNotes   ProjectNote[]
}

I think it's a pretty common pattern to query/mutate based on multiple constraints for security. To prevent users from getting or modifying something that doesn't belong to them. Without the support of multiple constraints, it makes working with Prisma a little coarse.

As a small aside, I'm really loving Prisma so far! Great work!

[baba43]

I love Prisma too (for now) but its current behavior doesn't make sense to me.

There are many ORMs out there that utilize findOne and findMany, however none of them restricts findOne fields to unique identifiers.

It took me a whilte to figure out that there was nothing wrong with the schema / introspection when I did not get my regular fields to work in select.

If you would like to provide a fast method for fetching by idenitifers, I think you might better add something like findById instead (Mongoose is doing it this way).

Just my two cents

[pantharshit00]

More discussion on this here: #2954

I think this issue tracks findOne where one unique field is specified and we can do further filtering.

[onemen]

@Aidurber try this:

const projects = await prisma.$queryRaw<Project[]>(
  `UPDATE project SET description = $1, title = $2 WHERE id = $3 AND "organisationId" = $4 RETURNING *;`,
  dto.description,
  dto.title,
  id,
  user.organisationId
);
return projects ? projects[0] : null;

replace Project[] with your actual type if you need the return type

[zaosoula]

findFirst has been implemented and was shipped in release https://github.com/prisma/prisma/releases/tag/2.8.0

I think this can be closed now :)

#2954 (comment) (@pantharshit00)

[pantharshit00]

Indeed @zaosoula

Closing as this is implemented: https://www.prisma.io/docs/concepts/components/prisma-client/crud#findfirst

[OzzieOrca]

This doesn't seem to solve the update or delete use cases mentioned in #1924 (comment). Maybe that should be a new issue. I added more detail here #4185 (reply in thread).

[LinusU]

@pantharshit00 did you see the comment ☝️ above here?

Currently it isn't possible to do something like:

prisma.project.delete({
  where: {
    id: 'foo',
    userId: 'bar'
  }
} 

To make sure that the user can only see the project that itself owns.

The only workaround I can find involves adding an additional unique index, and query on that:

prisma.project.delete({
  where: {
    id_userId: {
      id: 'foo',
      userId: 'bar'
    }
  }
} 

Surely we shouldn't be required to add additional unique constraints on combinations with id? 🤔

[LinusU]

And it also doesn't work at all for ensuring things only get deleted once, since @@unique exclude null values:

prisma.project.update({
  where: {
    id_userId_deletedAt: {
      id: 'foo',
      userId: 'bar',
      deletedAt: null
    }
  },
  data: {
    deletedAt: new Date()
  }
} 

(property) deletedAt: string | Date
Type 'null' is not assignable to type 'string | Date'.ts(2322)

[janpio]

This issue was about findOne. None of the things you mention use findOne.

There are multiple issues about being to select by non unique fields, e.g. #7290
Does this cover your request?

[LinusU]

@janpio sorry for missing that issue, I will post my comments there as well, thanks! 👍

[janpio]

All good, that is what I am here for 🤣