Provide additional type information in WHERE clause

[paustint]

• Feature
• Bug Request

Description

When parsing WHERE clauses, we should store indicators of the field type if possible. It would be useful to know if a date literal is used or if the type is a string or some other type so that consumers can more reliably build a compose object to be parsed.

Example:
This query has multiple data types where some need to be quoted and others do not.
SELECT Id FROM Account WHERE dateField != '2018-10-03' AND dateField < LAST_WEEK AND isDeleted = false AND someOTherField = 'someVal'

TODO

• Add type information for fields
• Add type information for date literals and date n literals
• having clause updated with literal types
• update unit tests
  • confirm that any new tests are added for full coverage
• update changelog
• array values in the WHERE clause don't fit the data model for LiteralType - may need to implement breaking change or find some other way to represent the types for these fields
  example:

{
    testCase: 34,
    soql: `SELECT Id FROM Account WHERE Foo IN ('1', '2', '3') OR Bar IN (1, 2, 3) OR Baz IN (101.00, 102.50) OR Bam IN ('FOO', null)`,
    output: {
      fields: [
        {
          text: 'Id',
        },
      ],
      subqueries: [],
      sObject: 'Account',
      where: {
        left: {
          field: 'Foo',
          operator: 'IN',
          value: ["'1'", "'2'", "'3'"],
          literalType: 'STRING',
        },
        operator: 'OR',
        right: {
          left: {
            field: 'Bar',
            operator: 'IN',
            value: ['1', '2', '3'],
            literalType: 'INTEGER',
          },
          operator: 'OR',
          right: {
            left: {
              field: 'Baz',
              operator: 'IN',
              value: ['101.00', '102.50'],
              literalType: 'DECIMAL',
            },
            operator: 'OR',
            right: {
              left: {
                field: 'Bam',
                operator: 'IN',
                value: ["'FOO'", 'null'],
                literalType: 'NULL',
              },
            },
          },
        },
      },
    },
  },

[paustint]

@ChuckJonas @ABenassi87 - do you have any thoughts on this:
I am finding it hard to build a compose object from fields because the structure varies based on the data type and I think it would be useful to have type information from the parsed SOQL query.
* I have type new properties to the left in a WHERE clause: literalType?: LiteralType and dateLiteralVariable?: number
* LiteralType = 'STRING' | 'INTEGER' | 'DECIMAL' | 'BOOLEAN' | 'NULL' | 'DATE_LITERAL' | 'DATE_N_LITERAL';

This is fine and is not a breaking change as it is not used in composed, but is just extra information for the parsed query.
The problem is when there is a SET of values
example:
SELECT Id FROM Account WHERE Foo IN ('1', '2', '3') OR Bar IN (1, 2, 3) OR Baz IN (101.00, 102.50) OR Bam IN ('FOO', null)

There is a rare possibility that there could be differing types in the set.

1. So we could either store the type of the last item in the set (this is the behavior now, not ideal and seems like a "bug" but it is an edge case)
2. Or we could change the structure of the set array to something like [{value: "'1'", literalType: 'STRING'} ... ]
   • I prefer this but it is a breaking change and @ChuckJonas, as far as I know you are the only other person using the library, so I would like to give you a chance to input your thoughts in case you have objections/preference.

[ChuckJonas]

@paustint Here's how I dealt with it in ts-force-qry (I built an conditional abstraction layer over yours, partially to deal with this issue; partially to make the WHERE & HAVING clauses more human readable)

My "Condition" looks like this:

type Value = string | number | boolean | Date | string[];

type BaseCondition = { field: string, op: Operator, not?: boolean, formatter?: (d: Value) => string | string[] }

type PrimitiveConditionParams = { val: Value } & BaseCondition;
type SubQueryConditionParams = { subqry: string } & BaseCondition;

type ConditionParams = PrimitiveConditionParams | SubQueryConditionParams

If formatter function is not supplied then I render a default format based on the value type:

• boolean | numbers just output toString()
• strings | string[] each string gets wrapped in single quote
• Date: format to YYYY-MM-DDThh:mm:ssZ

I might provide some out of the box formatters for common cases like formatting a Date to YYYY-MM-DD or different timezones.

Here's my code where you can see all this together (it's a mess; don't judge 🤦‍♂️ )

Subqueries

While subqueries (EG: WHERE Id IN (SELECT AccountId FROM Contact)) could have been handled with a simple format like so:

{field: 'Id', op: 'IN', val: 'SELECT AccountId FROM Contact', formatter: s => `(${s})`}

I split them out to it's own type which takes a param subqry instead of val so you don't have to think about it. However, I think for the purposes of this library, it might make more sense for Value to just accept a SubQuery, where SubQuery a subset of Query to reflect what types are allowed in a WHERE subquery.

type Value = string | number | boolean | Date | string[] | SubQuery;