Add support multiple nested filtering options when using OR | AND operators

[romcok]

Hi,
I cannot get to work this where condition, it is not possible to write it in typescript. Is there any other option than to write this condition?

      where: {
        OR: {
          AND: {
            status: {
              EQ: 'SCHEDULED',
            },
            startAt: {
              LT: before24h().toISOString(),
            }
          },
          AND: {
            status: {
              EQ: 'EXPIRED',
            },
            startAt: {
              LT: new Date().toISOString(),
            } 
          }
        }

This was my where condition in dynamodbtoolbox which worked fine:

filters: [
        { attr: 'entity', eq: 'Event' },
        [
          [
            { attr: 'status', eq: 'SCHEDULED' },
            { attr: 'startAt', lt: before24h().toISOString() },
          ],
          [
            { or: true, attr: 'status', eq: 'EXPIRED' },
            { attr: 'startAt', lt: new Date().toISOString() },
          ]
        ]
      ]

[whimzyLive]

@romcok
Can you enable the verbose logging on the requests, so that I can verify what the generated expression looks like?

[romcok]

I cant. because I don't know how to write this condition to work for me. The condition should look like this:
( status EQ 'SCHEDULED' AND startAt LT '<timestamp>' ) OR ( status EQ 'EXPIRED' AND startAt LT '<timestamp>') so it's ( AND ) OR ( AND ) which is not writeable to where condition.

      where: {
        OR: {
          AND: {
            status: {
              EQ: 'SCHEDULED',
            },
            startAt: {
              LT: before24h().toISOString(),
            }
          },
          AND: {
            status: {
              EQ: 'EXPIRED',
            },
            startAt: {
              LT: new Date().toISOString(),
            } 
          }
        }

There are two AND keys in the object, and you cannot have two identical keys in the object.

[whimzyLive]

Hello @romcok,

Thanks for raising this issue. The current filter input implementation doesn't support specifying multiple conditions in OR | AND operators.
I will look into prioritizing this.

In the meanwhile maybe try filtering at the application layer using two separate calls to TypeDORM.
Something like

// query 1
      where: {
          AND: {
            status: {
              EQ: 'SCHEDULED',
            },
            startAt: {
              LT: before24h().toISOString(),
            }
          }
        }

// query 2
      where: {
          AND: {
            status: {
              EQ: 'EXPIRED',
            },
            startAt: {
              LT: new Date().toISOString(),
            } 
          }
        }

[ebenz99]

Seems related, but might open a separate issue instead--it also seems like it isn't possible to use different filter criteria on the same attribute with OR. E.g.:

where: {
    OR: {    
        status: {
          EQ: 'SCHEDULED',
        },    
        status: {
          EQ: 'IN_PROGRESS',
        },
    }
}

It seems to me like an error is thrown when only one type of attribute is specified because the second instance overwrites the first.

Would also be happy to hear that I'm just missing something though :)

[joshstrange]

I think is related to not being able to add multiple NOT conditions. If I put 2 attributes within a NOT then I get:

Value for operator "NOT" can not contain more than 1 attributes

But there isn't an alternative way that I can see to do it within an AND (which seems to be required if you want to use where with more than 1 thing).

I think at the core the filter implementation needs to support arrays for places where we need to do something like:

{
  where: {
    AND: [
      {
        NOT: {
          attribute1: 'ATTRIBUTE_EXISTS`
        }
      },
      {
        NOT: {
          attribute2: 'ATTRIBUTE_EXISTS`
        }
      },
    ]
  }

}