Conditional OR

[MrSwitch]

There is no way to conditionally join a value across two fields. Aka search multiple fields for a value, e.g. (title, body):word, to create the sql WHERE (title = 'word' OR body='word') or indeed fields across many tables WHERE (a.title=? OR meta.fulltext = ?).

Proposal
Support additional syntax where the value of a filter prop is an Array of Objects
e.g....

const filter = {
    '$arbitary': [
         // Local Scope
         {
             title: ?
         },
         // Nested...
         {
             meta: {
                  fulltext: ?
             }
         }
    ]
}

In the above case an Array is passed into the filters, in order to create an cond1 [...OR condN] SQL conditions block.

The Array is given an $arbitrary key prop, whose definition has no meaning and would be ignored. It is simply there to mix conditional OR with regular AND.

The parser currently accepts an array to mean a list of possible values e.g. field IN (1,2,3).
The parser would need to be adapted to decide whether any or all of these values are objects and therefore to apply the CONDITIONAL OR rule.

For example in the below the values are a mix...

const filter = {
    'field': [
         'a',
        'b', 
         {
             alternative: {
                  alternativeField: 'c'
             }
         }
    ]
}

The parser would understand the first and second values to be a definition of the field and the third to define it's own table field and value. But the query would return anything that matches any of the three. i.e. WHERE (field IN ('a', 'b') OR alternative.alternativeField = 'c'). Note that the field is redundant in the third option. And the filter could have also have been written...

const filter = {
    '$something': [
         {
              field: ['a', 'b']
         },
         {
             alternative: {
                  alternativeField: 'c'
             }
         }
    ]
}