Relation queries in prisma

Question

i have the following prisma model:

model Product {
  id            String         @id @default(uuid()) @map("id")
  name          String         @map("name")
  subCategories SubCategory[]
  description   String?        @default("") @map("description")
  price         Decimal?       @map("price")
  updatedAt     DateTime       @default(now()) @updatedAt @map("updated_at")
  createdAt     DateTime       @default(now()) @map("created_at")

  @@index([id])
  @@map("products")
}

model Category {
  id            Int           @id @default(autoincrement()) @map("id")
  name          String        @map("name")
  subCategories SubCategory[]
  updatedAt     DateTime      @default(now()) @updatedAt @map("updated_at")
  createdAt     DateTime      @default(now()) @map("created_at")

  @@index([id])
  @@map("categories")
}

model SubCategory {
  id         Int       @id @default(autoincrement()) @map("id")
  name       String    @map("name")
  Category   Category  @relation(fields: [categoryId], references: [id], onDelete: Cascade)
  categoryId Int
  Product    Product[]
  updatedAt  DateTime  @default(now()) @updatedAt @map("updated_at")
  createdAt  DateTime  @default(now()) @map("created_at")

  @@index([id])
  @@map("sub_categories")
}

basically i have categories, every category can have multiple subcategories, and a product can have multiple subcategories, the problem is when i try to filter products by its subcategories, the behaviour that i want is that if i provide the subcategories with id 2 and 3 inside the where statement i have to get back all the products that have at least both of the subcategories specified, if a product has more subcategories than those two it's fine but at least they must have those two.

I've tried using some and every but with some i also get product from only one of the ids provided and with every i must provide all the subcategories ids associated to that product or i get back nothing

Answer 1

to avoid this behavior you may use AND operator with some

  const idsOfSubCategories = [2, 3];

  const filterBySubCategories = idsOfSubCategories.map((id) => {
    return {
      subCategories: {
        some: {
          id,
        },
      },
    };
  });

  await prisma.product.findMany({
    where: {
      AND: filterBySubCategories,
    },
  });