Support nested where clauses

[camchenry]

It would be nice to support nested conjunctions/disjunctions (AND/OR) for creating complex where clause queries.

Example from the Prisma docs:

const users = await prisma.user.findMany({
  where: {
    OR: [
      {
        name: {
          startsWith: 'E',
        },
      },
      {
        AND: {
          profileViews: {
            gt: 0,
          },
          role: {
            equals: 'ADMIN',
          },
        },
      },
    ],
  },
})

Haven't looked into implementing this, but would probably require checking for a special property like AND or OR and handling the property accordingly.

[camchenry]

If it's easier, the library could support non-recursive queries first, and add recursive functionality later? So these queries would be supported:

// Supported currently
const users = db.user.findMany({
  where: {
    name: {
      equals: "something",
    },
  },
});

// NEW: single layer disjunction (OR)
const users = db.user.findMany({
  where: {
    OR: [
      {
        name: {
          equals: "something",
        },
      },
      {
        email: {
          contains: "@example.com",
        },
      },
    ],
  },
});

// NEW: single layer conjunction (AND)
const users = db.user.findMany({
  where: {
    AND: [
      {
        name: {
          equals: "something",
        },
      },
      {
        email: {
          contains: "@example.com",
        },
      },
    ],
  },
});

[kettanaito]

Hey, @camchenry. That's a good proposal. I believe your recursive AND example is missing an array, right?

const users = await prisma.user.findMany({
  where: {
    OR: [
      {
        name: {
          startsWith: 'E',
        },
      },
      {
-        AND: {
+        AND: [{
          profileViews: {
            gt: 0,
          },
          role: {
            equals: 'ADMIN',
          },
        },
-      },
+      }],
    ],
  },
})

If I understand that API correctly, both OR and AND support a list of values but treat matches in those lists differently:

• OR is basically list.some()
• AND is list.every()

[kettanaito]

Please, would you be interested in lending us a hand once more?

I think the barebone for this feature would be to extend the compileQuery function somewhere along these lines:

data/src/query/compileQuery.ts

Line 28 in 154d5b5

return Object.entries(queryChunk).reduce<boolean>(

We can separate the regular which object handling in a new function and then check if where.OR or where.AND exist, and execute that separated function on their lists, treating the matches accordingly.

[camchenry]

@kettanaito I'm actually not sure about the missing braces, I took these examples from the Prisma docs, and there's a few examples where they don't have any brackets. I'm not familiar enough with the difference, but I think if it makes things easier for us to implement, we should just stick to explicit braces for the time being.

I can try to dive into this later, I'm working through adding some more mock APIs in my company's main product, which is how this came up. We have a custom syntax for doing query filtering that looks like /Api?Filter=Name eq 'Something' AND Active eq 'true' which gets converted into a list of filters like [{ field: 'Name', operator: 'eq', value: 'Something' }]. I wanted to be able to convert that into a roughly equivalent query in a pretty straightforward manner.

[roertbb]

As mentioned in readme, the query API is inspired by Prisma and I believe that would be a good choice to extend it in the same way how Prisma is handling queries (Nevertheless, starting first with some simple cases and extending it further is cool idea!).

I've used Prisma a little bit lately, but haven't managed to utilise the AND and OR operators. Nevertheless, I'd like to share my assumptions about that, given what I managed to understand based on Prisma docs, which may give you some insights on how it could be developed further.

If it comes to the example provided in the issue description, I believe it's a valid syntax in Prisma. If I understood it correctly the AND operator can take an array of conditions, for example

const users = await prisma.user.findMany({
  where: {
    AND: [
      { // 1st condition
        profileViews: {
          gt: 0,
        },
      },
      { // 2nd condition
        role: {
          equals: "ADMIN",
        },
      },
    ],
  },
});

or an object with keys, that each keys is some condition

const users = await prisma.user.findMany({
  where: {
    AND: {
      profileViews: { // 1st condition
        gt: 0,
      },
      role: { // 2nd condition
        equals: "ADMIN",
      },
    },
  },
});

or as mentioned in https://www.prisma.io/docs/reference/api-reference/prisma-client-reference#get-all-post-records-where-the-content-field-contains-prisma-and-published-is-false-no-and the AND can be omitted, for example

const users = await prisma.user.findMany({
  where: {
    profileViews: { // 1st condition
      gt: 0,
    },
    role: { // 2nd condition
      equals: "ADMIN",
    },
  },
});

If I got it correctly, all the examples should return the same result, however I'm not an experienced Prisma user and it would be worth it to verify that behaviour - querying admins with at least 1 profile view.

Similarly the OR could take an array of conditions or an object with keys. In case of an object it would allow to specify conditions like key1=value1 OR key2=value2

const users = await prisma.user.findMany({
  where: {
    OR: {
      key1: {
        equals: "value1",
      },
      key2: {
        equals: "value2",
      },
    }
  },
});

However if someone would like to define condition like key1=value1 OR key1=value2 (conditions with the same key1), it will be required to use an array, for example

const users = await prisma.user.findMany({
  where: {
    OR: [
      {
        key1: {
          equals: "value1",
        },
      },
      {
        key1: {
          equals: "value2",
        },
      },
    ],
  },
});

As mentioned before, I'm not 100% sure if that's the way how it's handled by Prisma, but that's how I understood it after reading the docs. I'll try to verify that assumption on a side project I'm working on, which is using Prisma and share results here 😉

EDIT:
After quick messing around with my example, my initial assumptions was partially correct 😅

• First 3 examples with AND provide the same queries
• If it comes with examples with OR operator - The 1st example with an object actually uses AND in generated query (it returns a query with ... WHERE "key1" = "value1" AND "key2 = "value2" ...), while the 2nd case with array of objects returns a query with OR (... WHERE "key1" = "value1" OR "key1 = "value2" ...)

[kettanaito]

Thanks for investigating this, @roertbb! I'm all hands for adopting AND and OR in this library but I could use a hand in doing so. We can start small and implement a special OR property on the where clause first, and then gradually move from that. I will be happy to help with code review to whoever finds the courage to build this!

[oteoe]

wouldn't it be easier to use the graphql parser?

https://dgraph.io/docs/graphql/queries/and-or-not/

https://graphql.org/graphql-js/language/

or the
https://github.com/krsbx/prisma-fqp

[JasieBasie]

@kettanaito Any news regarding this? Right now in the project where this would be crucial (doing query search over few different fields).

[kettanaito]

Hey, @JasieBasie. This will be supported in the next version of this library, I'm currently testing this very functionality. The next version is unlikely to be released this year.