Unable to filter an array inside of a related model with Prisma 2

Question

I'm trying to check if the provided value exists inside of an array. I've been trying to figure this one out and from what I gathered, I have to use has. The array I'm trying to filter is inside of a related model. I tried looking for a solution, but couldn't find much on this subject. Am I doing something wrong? Is it at all possible to filter an array inside of a related model?

Here's my schema. Job and Company models are related, and inside Company we have a parking array.

model Company {
  id         Int       @id @default(autoincrement())
  name       String    @db.VarChar(200)
  state        String    @db.VarChar(30)
  parking    String[]
  ...
  createdAt  DateTime  @default(now())
  updated_at DateTime  @updatedAt
  @@map(name: "company")
}

model Job {
  id              Int      @id @default(autoincrement())
  type            String
  company         Company  @relation(fields: [company_id], references: [id])
  company_id      Int
  createdAt       DateTime @default(now())
  updated_at      DateTime @updatedAt
  UserJobs UserJobs[]
  @@map(name: "job")
}

Below, I'm trying to find many jobs which match various values. One of the values I'm trying to match is inside of an array in the related Company model. Here's what I tried:

const jobs = await prisma.job.findMany({
    where: {
        AND: [
            {
                type: {
                    contains: req.body.type,
                }
            },
            {
                company: {
                    state: {
                        contains: req.body.state
                    }
                }
            },
            ...
                
            {
                company: {
                    parking: {
                        has: req.body.parkingState
                    }
                }
            }
        ]
    },
    include: {
        company: true,
    }
})

Answer 1

If you want to match a single value in a list has should be used, but if you want to match multiple values in a list then you would need to use hasEvery or hasSome depending upon your use case.

Here is the query which matches a single value in a list

  const jobs = await prisma.job.findMany({
    where: {
      AND: [
        {
          type: {
            contains: 'Software Engineer',
          },
        },
        {
          company: {
            state: {
              contains: 'CA',
            },
          },
        },
        {
          company: {
            parking: {
              has: 'Employee',
            },
          },
        },
      ],
    },
    include: {
      company: true,
    },
  });
  console.log(JSON.stringify(jobs, null, 2));
}

Here is the response for the above query:

[
  {
    "id": 1,
    "type": "Software Engineer",
    "company_id": 1,
    "createdAt": "2022-02-28T08:53:03.949Z",
    "updated_at": "2022-02-28T08:53:03.950Z",
    "company": {
      "id": 1,
      "name": "Apple",
      "state": "CA",
      "parking": [
        "Admin",
        "Manager",
        "Employee"
      ],
      "createdAt": "2022-02-28T08:50:50.030Z",
      "updated_at": "2022-02-28T08:50:50.031Z"
    }
  }
]

This is the sample data with which the above query fetched the results.

Job Table: Company Table:

If you want to match multiple values in parking array you could achieve it by replacing has with hasSome in this manner.

  const jobs = await prisma.job.findMany({
    where: {
      AND: [
        {
          type: {
            contains: 'Software Engineer',
          },
        },
        {
          company: {
            state: {
              contains: 'CA',
            },
          },
        },
        {
          company: {
            parking: {
              hasSome: ['Employee', 'Manager'],
            },
          },
        },
      ],
    },
    include: {
      company: true,
    },
  });
  console.log(JSON.stringify(jobs, null, 2));
}