How to findMany() with nested array of objects using Prisma?

ghz 9months ago ⋅ 140 views

I am trying to create filters in my web application. This is the structure of my database and products (simplified). I want to use findMany() to go through and return all products that have "cheeks" within the placement array (would return the first product). Or return all products that have at least "cheeks" and "eyes" listed in the placement array (would return both products).

    [
      // product 1
      {
        "palette": [
          {
            "id": "mondodb ID xxx",
            "hexColor": "#e9b190",
            "name": "color name 1",
            "properties": {
              "blue": 0.5647,
              "placement": [
                "cheeks"
              ],
              "green": 0.6941,
              "red": 0.9137
            }
          },
          {
            "id": "mondodb ID xxx",
            "hexColor": "#fc9981",
            "name": "color name 2",
            "properties": {
              "blue": 0.5059,
              "placement": [
                "cheeks",
                "lips"
              ],
              "green": 0.6,
              "red": 0.9882
            }
          }
        ],
        "id": "mondo db id xxxx",
        "status": "APPROVE",
        "title": "Product title"
      },
      // product 2 
      {
        "palette": [
          {
            "id": "mondodb ID xxx",
            "hexColor": "#d67768",
            "name": "color name 3",
            "properties": {
              "blue": 0.4078,
              "placement": [
                "eyes"
              ],
              "green": 0.4667,
              "red": 0.8392
            }
          }
        ],
        "id": "mondo db id xxxx",
        "status": "APPROVE",
        "title": "Product title 2"
      }
    ]

This is my prisma schema:

model product {
  id            String @id @default(auto()) @map("_id") @db.ObjectId
  palette       ProductPalette[]
  status        Status @default(NEEDS_REVIEW)
  title         String
  @@unique([id, palette.id])
}

type ProductPalette {
  id                  String  @map("_id") @db.ObjectId
  hexColor            String?
  properties          Properties
  name                String
}

type Properties {
  blue                Float? @default(0)
  placement           Placement[]
  green               Float? @default(0)
  red                 Float? @default(0)
}

enum Placement {
  lips
  eyelid
  cheeks
}

enum Status {
  APPROVE
  NEEDS_REVIEW
  REMOVE_ITEM
}

I am having an issue with prisma's findMany() and constructing the correct "where" clause. I currently have this which will not work. This is a Next.js application with typescript and MongoDb.

        (await prisma?.product?.findMany({
          skip: ITEMS_PER_PAGE * (Number(page) - 1),
          take: ITEMS_PER_PAGE,
          orderBy: {
            createdDate: (orderBy as Prisma.SortOrder) ?? 'asc',
          },
          where: {
            palette: {
             some: {
              properties: {
                placement: [Placement.cheeks]
              }
             }
            }
          }
        }))

OS: macOS Database: MongoDB Node.js version: v20.8.1 Prisma version: 5.11.0

Answers

To construct the correct where clause for filtering products based on the placement array in the properties object within the palette array, you need to use Prisma's nested filtering capabilities. You can achieve this by using the some filter operator.

Here's how you can modify your findMany query to filter products that have "cheeks" within the placement array:

import { Prisma, Placement } from '@prisma/client';

const productsWithCheeks = await prisma.product.findMany({
  where: {
    palette: {
      some: {
        properties: {
          placement: {
            has: Placement.cheeks
          }
        }
      }
    }
  }
});

This query will return all products where at least one item in the palette array has "cheeks" listed in the placement array within the properties object.

If you want to filter products that have at least "cheeks" and "eyes" listed in the placement array, you can modify the query like this:

import { Prisma, Placement } from '@prisma/client';

const productsWithCheeksAndEyes = await prisma.product.findMany({
  where: {
    palette: {
      some: {
        properties: {
          placement: {
            hasEvery: [Placement.cheeks, Placement.eyes]
          }
        }
      }
    }
  }
});

This query will return all products where at least one item in the palette array has both "cheeks" and "eyes" listed in the placement array within the properties object.

Ensure that you have imported the Placement enum from Prisma and specified the correct path to it in your project. Also, make sure that the Prisma version you are using supports nested filtering operations.

#mongodb