Filter query with Prisma using fields of relation (One-to-Many relation)-CodePudding

I am having trouble writing a query with prisma that includes a filter on a model's relation's key.

model Car {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  plate     String  @unique
  place     String

  bookings  Booking[]

  @@map("cars")
}

My booking model is the following :

model Booking{
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  place String

  startDate DateTime
  endDate   DateTime

  carId Int
  car   Car @relation(fields: [carId], references: [id])

  @@map("bookings")
}

I am having trouble expressing a query returning every car that respect a given criteria on startDate and endDate within their bookings relation/key. I would appreciate any idea or clue, thank you in advance.

CodePudding user response：

You can either query for bookings and include the cars:

prisma.booking.findMany({ where: { startDate, endDate }, include: { car: true }})

You could then map the cars from the result. However, you might receive two bookings for the same car (if that's in the data).

Or you can use filter on relations (https://www.prisma.io/docs/concepts/components/prisma-client/relation-queries#filter-on--to-many-relations):

prisma.car.findMany({ where: { bookings: { some: { startDate, endDate } } } });

You can also use more complex queries, e.g. to search for cars with booking in a period of time:

prisma.car.findMany({
  where: {
    bookings: { some: { startDate: { gte: startDate }, endDate: { lte: endDate } } },
  },
});

(All examples assume, that startDate, endDate are initialized with proper values.)