OR operator in include where produces AND in query

[bazo]

Bug description

query is:

const keys = await db.projectKey.findMany({
    where: {
        projectId,
    },
    include: {
        translationKeys: {
            where: {
                locale: sourceLocale,
                OR: {
                   locale: targetLocale,
                },
            },
         },
     },
});

the resulting query sent to db is:

SELECT * FROM `translation_key` WHERE ((`translation_key`.`locale` = ? AND `translation_key`.`locale` = ?) AND `translation_key`.`key_id` IN (?,?,?,?,?,?))

should be

SELECT * FROM `translation_key` WHERE ((`translation_key`.`locale` = ? OR `translation_key`.`locale` = ?) AND `translation_key`.`key_id` IN (?,?,?,?,?,?))

• is not really there, but the actual fields are not important :)

How to reproduce

have provided schema and use provided query :)

Expected behavior

produces sql query

SELECT * FROM `translation_key` WHERE ((`translation_key`.`locale` = ? OR `translation_key`.`locale` = ?) AND `translation_key`.`key_id` IN (?,?,?,?,?,?))

Prisma information

model ProjectKey {
  id              String           @id @default(cuid())
  projectId       String           @map("project_id")
  translationKeys TranslationKey[]
}

model TranslationKey {
  id             String       @id @default(cuid())
  key            ProjectKey   @relation(fields: [keyId], references: [id])
  keyId          String       @map("key_id")
  locale         String
}

const keys = await db.projectKey.findMany({
    where: {
        projectId,
    },
    include: {
        translationKeys: {
            where: {
                locale: sourceLocale,
                OR: {
                   locale: targetLocale,
                },
            },
         },
     },
});

Environment & setup

• OS: macOS
• Database: Mysql 8
• Node.js version: v18.16.0

Prisma Version

4.16.1

[janpio]

I think your sourceLocale is on the wrong level. Compare with this example: https://www.prisma.io/docs/reference/api-reference/prisma-client-reference#get-all-post-records-where-the-title-field-contains-prisma-or-databases

So it should probably be:

  const keys = await db.projectKey.findMany({
    where: {
      projectId,
    },
    include: {
      translationKeys: {
        where: {
          OR: [
            {
              locale: targetLocale,
            }, 
            {
              locale: sourceLocale,
            }
          ]
        },
      },
    },
  })

[ludralph]

Hey @bazo,

Do you have any more questions around this issue? If no, it would be really helpful if you could quickly mark this Discussion as answered to indicate that your issue has been resolved.

In case you do have any outstanding questions, feel free to let us know and we'd be happy to help!

[ludralph]

Hi there,

To keep our discussions organized and focused on the most relevant topics, we’re reviewing and tidying up our backlog. As part of this process, we’re closing discussions that haven’t had any recent activity and appear to be outdated.

If this discussion is still important to you or unresolved, we’d love to hear from you! Feel free to reopen it or start a new one with updated details.

For more details about our priorities and vision for the future of Prisma ORM, check out our latest blog post: https://www.prisma.io/blog/prisma-orm-manifesto.

Thank you for your understanding and being part of the community!