I'm not sure if I'm missing something. But your prisma query basically already generates same SQL query that you wrote below.

Try something like this:-

    await prisma.game.findMany({
  where: {
    OR: [
      // 1st combo of conditions
      {
        AND: [
          {
            field1: {
              contains: "condition1",
            },
          },
          {
            field1: {
              contains: "condition2",
            },
          },
        ],
      },
      // 2nd combo of conditions
      {
        AND: [
          {
            field1: {
              contains: "condition2",
            },
          },
          {
            field1: {
              contains: "condition3",
            },
          },
        ],
      },
      // 3rd combo of conditions
      {
        AND: [
          {
            field1: {
              contains: "condition3",
            },
          },
          {
            field1: {
              contains: "condition1",
            },
          },
        ],
      },
    ],
  },
  select: {},
});

New Explanation:-

Here I am now matching against 1 string field only but with different combination patterns using AND.

I don't think the AND option works if you send an object. It typically takes an array of objects (https://www.prisma.io/docs/reference/api-reference/prisma-client-reference#examples-46).

The simpler way to do the query is to leave out the AND entirely:

await prisma.user.findMany({
  where: {
    user_id: {
      in: users.map(user => user.user_id)
    },
    school_id: {
      in: users.map(user => user.school_id)
    }
  }
})

This is essentially an 'implicit' AND.

I've found the solution here https://github.com/prisma/prisma/discussions/8216#discussioncomment-992302

It should be like this instead apparently.

await prisma.product.findMany({
      where: {
        AND: [
          { price: 21.99 },
          { filters: { some: { name: 'ram', value: '8GB' } } },
          { filters: { some: { name: 'storage', value: '256GB' } } },
        ],
      },
})

Considering this schema file:

generator client {
  provider        = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model test {
  id        Int      @id @default(autoincrement())
  name      String
  updatedAt DateTime @updatedAt
}

You can nest AND and OR operators like this:

  await prisma.test.findMany({
    where: {
      AND: [
        {
          OR: [
            {
              name: 'test1',
            },
            {
              name: 'test2',
            },
          ],
        },
        {},
      ],
    },
  });

By default all the fields in the where clause are combined with AND operator so you might only need to use the OR operator.

For reference: AND and OR Prisma Documentation.

You can use a combination of spread syntax operation and ternary operation to accomplish this:

const result = await prisma.project.findMany({
  where: {
    ...(sortByYear ? { provider_id: 50 } : {}),
  },
});

Spread Syntax:
https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Operators/Spread_syntax

Conditional Ternary:
https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Operators/Conditional_Operator

@ManishKarki1997 👋

I have tested this with random data and this works:

await prisma.product.findMany({
      where: {
        AND: [
          { price: 21.99 },
          { filters: { some: { name: 'ram', value: '8GB' } } },
          { filters: { some: { name: 'storage', value: '256GB' } } },
        ],
      },
})