WHERE & HAVING

Structure

The where and having clauses use the following format:

WHERE: = [ CONDITION | ('AND'|'OR') | WHERE ],
CONDITION: = {field, op, val} | {field, op, subqry}

1 AND 2

//...
where: [
    { field: f.select('name'), op: '=', val: 'acme' },
    { field: f.select('annualRevenue'), op: '>=', val: 100 }
]

OUTPUT

WHERE Name = 'acme' AND AnnualRevenue >= 100

1 OR 2

where: [
    { field: f.select('name'), op: '=', val: 'acme' },
    'OR',
    { field: f.select('annualRevenue'), op: '>=', val: 100 }
]

OUTPUT

WHERE Name = 'acme' OR AnnualRevenue >= 100

• putting AND between conditions is optional. If left out, it will be implied (but may be included for readability)

• If op is omitted, it defaults to either = orIN depending on the val type

• nothing prevents multiple LogicalConditions (AND|OR) from occurring back to back. If this happens, the last condition will be used

Nesting Conditions

Conditions can be grouped/nested in parentheses by starting a new array

1 AND (2 OR 3)

 where: [
    { field: f.select('name'), op: '=', val: 'acme' },
    'AND',
    [
        { field: f.select('annualRevenue'), op: '>=', val: 100 },
        'OR',
        { field: f.select('active'), op: '=', val: true }
    ]
]

OUTPUT

WHERE Name = 'acme' 
AND (AnnualRevenue >= 100 OR Active__c = true)

(1 OR 2) AND (3 OR 4)

where: [
    [
        { field: f.select('name'), op: '=', val: 'acme' },
        'OR',
        { field: f.select('name'), op: '=', val: 'stark' },
    ],
    'AND',
    [
        { field: f.select('annualRevenue'), op: '>=', val: 100 },
        'OR',
        { field: f.select('active'), op: '=', val: true }
    ]
]

OUTPUT

WHERE 
  (Name = 'acme' OR Name = 'stark')
  AND 
  (AnnualRevenue >= 100 OR Active__c = true)

1 OR (2 AND (3 OR 4) )

where: [
    { field: f.select('name'), op: '=', val: 'acme' },
    'OR',
    [
        { field: f.select('annualRevenue'), op: '>=', val: 100 },
        'AND',
        [
            { field: f.select('active'), op: '=', val: true },
            'OR',
            { field: f.select('accountSource'), op: '=', val: 'web' }
        ]
    ]
]

OUTPUT

WHERE
  Name = 'acme'
  OR (
    AnnualRevenue >= 100
    AND (
      Active__c = true
      OR AccountSource = 'web'
    )
  )

Value Rendering

Condition values are automatically converted based on their type:

string

{ field: f.select('name'), op: '=', val: 'acme' }
//-> Name = 'acme'

string[]

{ field: f.select('name'), op: 'IN', val: ['acme', 'stark'] }
//-> Name IN ('acme', 'stark')

number

{ field: f.select('annualRevenue'), op: '>', val: 100 } 
//-> AnnualRevenue > 100

boolean

{ field: f.select('active'), op: '=', val: false }
//-> Active__c = false

Date

{ field: f.select('createdDate'), op: '<', val: new Date() }
//-> CreatedDate < 2020-12-23T02:44:49z