prismaprisma2

How to use where in in Prisma?


I have made a query below that search user using two columns. But it seems not working properly, I assume it is querying the where clause in each column instead of both columns.

Is there a way we could where ~ in for two or more columns?

const users = [
  {
    user_id: 1,
    school_id: 11,
    ..
  },
  {
    user_id: 2,
    school_id: 22
  },
  ..
]

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

The problem it does not search for both user_id and school_id. Instead it search either of the two column. I will ask assistance of you guys, or do you have better approach with the same result. thanks.


Solution

  • 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.