Repository: paustint/soql-parser-js (ts)

Issue:
Semi-Joins with IN and Anti-Joins with NOT IN are not supported
<!--
  Please include the following classification.
  If your classification is not added, you can modify the list below
-->
- [ ] Feature
- [x] Bug Request

<!--If this is a bug, please include the steps to reproduce the bug-->
## Description
I took this examples from: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_comparisonoperators.htm
#### Query 1:
``` sql
SELECT Id, Name 
FROM Account 
WHERE Id IN 
  ( SELECT AccountId
    FROM Opportunity
    WHERE StageName = 'Closed Lost' 
  )
```
#### Parsed Output:
```
Cannot read property 'data' of null
```
#### Query 2:
``` sql
SELECT Id 
FROM Account 
WHERE Id NOT IN
  (
    SELECT AccountId
    FROM Opportunity
    WHERE IsClosed = false
  )
```
#### Parsed Output:
```
Cannot read property 'data' of null
```
#### Query 3:
``` sql
SELECT Id, Name 
FROM Account
WHERE Id IN
  (
    SELECT AccountId
    FROM Contact
    WHERE LastName LIKE 'apple%'
  )
  AND Id IN
  (
    SELECT AccountId
    FROM Opportunity
    WHERE isClosed = false
  )
```
#### Parsed Output:
```
Cannot read property 'currConditionOperation' of null
```

The parser page displays the message: 'the query is invalid' for each of them but they are valid. 
toLabel function is parsed as a field in the WHERE clause
<!--
  Please include the following classification.
  If your classification is not added, you can modify the list below
-->
- [ ] Feature
- [x] Bug Request

<!--If this is a bug, please include the steps to reproduce the bug-->
## Description
I tried to parse this SOQL:
``` sql
SELECT Company, toLabel(Status)
FROM Lead
WHERE toLabel(Status) = 'le Draft'
```
I took this soql query from: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_tolabel.htm
### Result;
``` json
{
    "fields": [
        {
            "text": "Company"
        },
        {
            "fn": {
                "text": "toLabel(Status)",
                "name": "toLabel",
                "parameter": "Status"
            }
        }
    ],
    "subqueries": [],
    "sObject": "Lead",
    "whereClause": {
        "left": {
            "field": "toLabel(Status)",
            "operator": "=",
            "value": "'le Draft'"
        }
    }
}
```

The toLabel(status) function in the WHERE clause is parsed as a field. I think that it should be parsed as a function as it happens in the SELECT clause.
### Expected result:
``` json
{
    "fields": [
        {
            "text": "Company"
        },
        {
            "fn": {
                "text": "toLabel(Status)",
                "name": "toLabel",
                "parameter": "Status"
            }
        }
    ],
    "subqueries": [],
    "sObject": "Lead",
    "whereClause": {
        "left": {
             "fn": {
                "text": "toLabel(Status)",
                "name": "toLabel",
                "parameter": "Status"
            },
            "operator": "=",
            "value": "'le Draft'"
        }
    }
}
```


Pull request description:
where fn where subqueries #23 #24
modified the way WHERE clause field functions are displayed to be consistent with other functions
Added support for subqueries in the WHERE clause

resolves #23
resolves #24

Patch:
diff --git a/docs/package.json b/docs/package.json
index 7040b51..53567d3 100644
--- a/docs/package.json
+++ b/docs/package.json
@@ -21,7 +21,7 @@
     "predeploy": "npm run build",
     "deploy": "gh-pages -d build",
     "gh-pages": "gh-pages --help",
-    "update": "npm install soql-parser-js@latest && git add package*.json && git commit -m \"Updated docs version\" && git push"
+    "update": "npm install soql-parser-js@latest && git add package*.json && git commit -m \"Updated docs version\" && git push && npm run deploy"
   },
   "devDependencies": {
     "@types/jest": "^23.3.5",
diff --git a/docs/src/components/sample-queries.tsx b/docs/src/components/sample-queries.tsx
index 14dda3d..12e19c8 100644
--- a/docs/src/components/sample-queries.tsx
+++ b/docs/src/components/sample-queries.tsx
@@ -166,6 +166,22 @@ export default class SampleQueries extends React.Component<ISampleQueriesProps,
         soql: `SELECT amount, FORMAT(amount) Amt, convertCurrency(amount) editDate, FORMAT(convertCurrency(amount)) convertedCurrency FROM Opportunity where id = '12345'`,
       },
       { key: 39, num: 39, soql: `SELECT FORMAT(MIN(closedate)) Amt FROM opportunity` },
+      { key: 40, num: 40, soql: `SELECT Company, toLabel(Status) FROM Lead WHERE toLabel(Status) = 'le Draft'` },
+      {
+        key: 41,
+        num: 41,
+        soql: `SELECT Id, Name FROM Account WHERE Id IN (SELECT AccountId FROM Opportunity WHERE StageName = 'Closed Lost')`,
+      },
+      {
+        key: 42,
+        num: 42,
+        soql: `SELECT Id FROM Account WHERE Id NOT IN (SELECT AccountId FROM Opportunity WHERE IsClosed = false)`,
+      },
+      {
+        key: 43,
+        num: 43,
+        soql: `SELECT Id, Name FROM Account WHERE Id IN (SELECT AccountId FROM Contact WHERE LastName LIKE 'apple%') AND Id IN (SELECT AccountId FROM Opportunity WHERE isClosed = false)`,
+      },
     ];
   };
 
diff --git a/lib/SoqlComposer.ts b/lib/SoqlComposer.ts
index b9a758d..3b38beb 100644
--- a/lib/SoqlComposer.ts
+++ b/lib/SoqlComposer.ts
@@ -166,7 +166,11 @@ export class Compose {
           ? new Array(where.left.openParen).fill('(').join('')
           : '';
       output += `${utils.get(where.left.logicalPrefix, ' ')}`;
-      output += `${where.left.field} ${where.left.operator} ${utils.getAsArrayStr(where.left.value)}`;
+      output += where.left.fn ? this.parseFn(where.left.fn) : where.left.field;
+      output += ` ${where.left.operator} `;
+      output += where.left.valueQuery
+        ? `(${this.parseQuery(where.left.valueQuery)})`
+        : utils.getAsArrayStr(where.left.value);
       output +=
         utils.isNumber(where.left.closeParen) && where.left.closeParen > 0
           ? new Array(where.left.closeParen).fill(')').join('')
diff --git a/lib/SoqlListener.ts b/lib/SoqlListener.ts
index 0fa7cf6..239524f 100644
--- a/lib/SoqlListener.ts
+++ b/lib/SoqlListener.ts
@@ -22,11 +22,14 @@ export type currItem = 'field' | 'typeof' | 'from' | 'where' | 'groupby' | 'orde
 
 export interface Context {
   isSubQuery: boolean;
+  isWhereSubQuery: boolean;
+  whereSubquery: Query;
   currSubqueryIdx: number;
   currWhereConditionGroupIdx: number;
   currentItem: currItem;
   inWhereClauseGroup: boolean;
   tempData: any;
+  tempDataBackup: any; // used to store tempDate while in WHILE subquery
 }
 
 export class SoqlQuery implements Query {
@@ -50,11 +53,14 @@ export class SoqlQuery implements Query {
 export class Listener implements SOQLListener {
   context: Context = {
     isSubQuery: false,
+    isWhereSubQuery: false,
+    whereSubquery: null,
     currSubqueryIdx: -1,
     currWhereConditionGroupIdx: 0,
     currentItem: null,
     inWhereClauseGroup: false,
     tempData: null,
+    tempDataBackup: null,
   };
 
   soqlQuery: SoqlQuery;
@@ -101,7 +107,13 @@ export class Listener implements SOQLListener {
   }
 
   getSoqlQuery(): Query {
-    return this.context.isSubQuery ? this.soqlQuery.subqueries[this.context.currSubqueryIdx] : this.soqlQuery;
+    if (this.context.isSubQuery) {
+      return this.soqlQuery.subqueries[this.context.currSubqueryIdx];
+    }
+    if (this.context.isWhereSubQuery) {
+      return this.context.whereSubquery;
+    }
+    return this.soqlQuery;
   }
 
   enterKeywords_alias_allowed(ctx: Parser.Keywords_alias_allowedContext) {
@@ -214,6 +226,9 @@ export class Listener implements SOQLListener {
     if (this.context.currentItem === 'field') {
       this.context.tempData.alias = ctx.text;
     }
+    if (this.context.currentItem === 'where') {
+      this.context.tempData.currConditionOperation.left.fn.alias = ctx.text;
+    }
     if (this.context.currentItem === 'having') {
       this.context.tempData.currConditionOperation.left.fn.alias = ctx.text;
     }
@@ -363,9 +378,11 @@ export class Listener implements SOQLListener {
       const currFn: FunctionExp = this.context.tempData.fn || this.context.tempData;
       currFn.name = ctx.text;
     }
-    if (this.context.currentItem === 'having') {
+    if (this.context.currentItem === 'where' || this.context.currentItem === 'having') {
       this.context.tempData.currConditionOperation.left.fn.name = ctx.text;
-      // this.context.tempData.fn.name = ctx.text;
+      if (this.context.tempData.currConditionOperation.left.field) {
+        delete this.context.tempData.currConditionOperation.left.field;
+      }
     }
     if (this.context.currentItem === 'orderby') {
       this.context.tempData.fn.name = ctx.text;
@@ -572,16 +589,30 @@ export class Listener implements SOQLListener {
     if (this.config.logging) {
       console.log('enterSoql_subquery:', ctx.text);
     }
-    this.context.isSubQuery = true;
-    this.soqlQuery.subqueries.push(new SoqlQuery());
-    this.context.currSubqueryIdx = this.soqlQuery.subqueries.length - 1;
+    if (this.context.currentItem === 'where') {
+      this.context.isWhereSubQuery = true;
+      this.context.whereSubquery = new SoqlQuery();
+      delete this.context.tempData.currConditionOperation.left.value;
+      this.context.tempDataBackup = this.context.tempData;
+      this.context.tempData = null;
+    } else {
+      this.context.isSubQuery = true;
+      this.soqlQuery.subqueries.push(new SoqlQuery());
+      this.context.currSubqueryIdx = this.soqlQuery.subqueries.length - 1;
+    }
   }
   exitSoql_subquery(ctx: Parser.Soql_subqueryContext) {
     if (this.config.logging) {
       console.log('exitSoql_subquery:', ctx.text);
     }
-    this.context.isSubQuery = false;
-    this.context.currWhereConditionGroupIdx = 0; // ensure reset for base query or next subquery
+    if (this.context.isWhereSubQuery) {
+      this.context.isWhereSubQuery = false;
+      this.context.tempData = this.context.tempDataBackup;
+      this.context.tempData.currConditionOperation.left.valueQuery = this.context.whereSubquery;
+    } else {
+      this.context.isSubQuery = false;
+      this.context.currWhereConditionGroupIdx = 0; // ensure reset for base query or next subquery
+    }
   }
   enterSubquery_select_clause(ctx: Parser.Subquery_select_clauseContext) {
     if (this.config.logging) {
@@ -673,7 +704,7 @@ export class Listener implements SOQLListener {
       const currFn: FunctionExp = this.context.tempData.fn || this.context.tempData;
       currFn.text = ctx.text;
     }
-    if (this.context.currentItem === 'having') {
+    if (this.context.currentItem === 'where' || this.context.currentItem === 'having') {
       this.context.tempData.currConditionOperation.left.fn = {
         text: ctx.text,
       };
@@ -705,6 +736,7 @@ export class Listener implements SOQLListener {
     // Get correct fn object based on what is set in tempData (set differently for field vs having)
     if (
       this.context.currentItem === 'field' ||
+      this.context.currentItem === 'where' ||
       this.context.currentItem === 'having' ||
       this.context.currentItem === 'orderby'
     ) {
@@ -914,9 +946,6 @@ export class Listener implements SOQLListener {
     if (this.config.logging) {
       console.log('enterField_based_condition:', ctx.text);
     }
-    if (this.config.logging) {
-      console.log('enterLike_based_condition:', ctx.text);
-    }
     if (this.context.currentItem === 'where') {
       const currItem: any = {};
       if (!this.context.tempData.currConditionOperation.left) {
diff --git a/lib/models/SoqlQuery.model.ts b/lib/models/SoqlQuery.model.ts
index 73ac864..eb8fc2d 100644
--- a/lib/models/SoqlQuery.model.ts
+++ b/lib/models/SoqlQuery.model.ts
@@ -57,9 +57,11 @@ export interface Condition {
   openParen?: number;
   closeParen?: number;
   logicalPrefix?: LogicalPrefix;
-  field: string;
+  field?: string;
+  fn?: FunctionExp;
   operator: Operator;
-  value: string | string[];
+  value?: string | string[];
+  valueQuery?: Query;
 }
 
 export interface OrderByClause {


Test patch:
diff --git a/debug/test.js b/debug/test.js
index 8f2d00e..bd9c49b 100644
--- a/debug/test.js
+++ b/debug/test.js
@@ -1,12 +1,13 @@
 var soqlParserJs = require('../dist');
 
 const query = `
-SELECT Title FROM FAQ__kav WHERE PublishStatus='online' and Language = 'en_US' and KnowledgeArticleVersion = 'ka230000000PCiy' UPDATE VIEWSTAT
+SELECT Company, toLabel(Status) FROM Lead WHERE toLabel(Status) = 'le Draft'
 `;
 
 const parsedQuery = soqlParserJs.parseQuery(query, { logging: true });
 
 console.log(JSON.stringify(parsedQuery, null, 2));
 
-// SELECT amount, FORMAT(amount) Amt, convertCurrency(amount) editDate, FORMAT(convertCurrency(amount)) convertedCurrency FROM Opportunity where id = '12345'
-// SELECT FORMAT(MIN(closedate)) Amt FROM opportunity
+// SELECT Id, Name FROM Account WHERE Id IN (SELECT AccountId FROM Opportunity WHERE StageName = 'Closed Lost')
+// SELECT Id FROM Account WHERE Id NOT IN (SELECT AccountId FROM Opportunity WHERE IsClosed = false)
+// SELECT Id, Name FROM Account WHERE Id IN (SELECT AccountId FROM Contact WHERE LastName LIKE 'apple%') AND Id IN (SELECT AccountId FROM Opportunity WHERE isClosed = false)
diff --git a/test/TestCases.ts b/test/TestCases.ts
index 7b56bef..74b6d79 100644
--- a/test/TestCases.ts
+++ b/test/TestCases.ts
@@ -1275,5 +1275,170 @@ export const testCases: TestCase[] = [
       sObject: 'Opportunity',
     },
   },
+  {
+    testCase: 46,
+    soql: `SELECT Company, toLabel(Status) FROM Lead WHERE toLabel(Status) = 'le Draft'`,
+    output: {
+      fields: [
+        {
+          text: 'Company',
+        },
+        {
+          fn: {
+            text: 'toLabel(Status)',
+            name: 'toLabel',
+            parameter: 'Status',
+          },
+        },
+      ],
+      subqueries: [],
+      sObject: 'Lead',
+      where: {
+        left: {
+          operator: '=',
+          value: "'le Draft'",
+          fn: {
+            text: 'toLabel(Status)',
+            name: 'toLabel',
+            parameter: 'Status',
+          },
+        },
+      },
+    },
+  },
+  {
+    testCase: 47,
+    soql: `SELECT Id, Name FROM Account WHERE Id IN (SELECT AccountId FROM Opportunity WHERE StageName = 'Closed Lost')`,
+    output: {
+      fields: [
+        {
+          text: 'Id',
+        },
+        {
+          text: 'Name',
+        },
+      ],
+      subqueries: [],
+      sObject: 'Account',
+      where: {
+        left: {
+          field: 'Id',
+          operator: 'IN',
+          valueQuery: {
+            fields: [
+              {
+                text: 'AccountId',
+              },
+            ],
+            subqueries: [],
+            sObject: 'Opportunity',
+            where: {
+              left: {
+                field: 'StageName',
+                operator: '=',
+                value: "'Closed Lost'",
+              },
+            },
+          },
+        },
+      },
+    },
+  },
+  {
+    testCase: 48,
+    soql: `SELECT Id FROM Account WHERE Id NOT IN (SELECT AccountId FROM Opportunity WHERE IsClosed = false)`,
+    output: {
+      fields: [
+        {
+          text: 'Id',
+        },
+      ],
+      subqueries: [],
+      sObject: 'Account',
+      where: {
+        left: {
+          field: 'Id',
+          operator: 'NOT IN',
+          valueQuery: {
+            fields: [
+              {
+                text: 'AccountId',
+              },
+            ],
+            subqueries: [],
+            sObject: 'Opportunity',
+            where: {
+              left: {
+                field: 'IsClosed',
+                operator: '=',
+                value: 'false',
+              },
+            },
+          },
+        },
+      },
+    },
+  },
+  {
+    testCase: 49,
+    soql: `SELECT Id, Name FROM Account WHERE Id IN (SELECT AccountId FROM Contact WHERE LastName LIKE 'apple%') AND Id IN (SELECT AccountId FROM Opportunity WHERE isClosed = false)`,
+    output: {
+      fields: [
+        {
+          text: 'Id',
+        },
+        {
+          text: 'Name',
+        },
+      ],
+      subqueries: [],
+      sObject: 'Account',
+      where: {
+        left: {
+          field: 'Id',
+          operator: 'IN',
+          valueQuery: {
+            fields: [
+              {
+                text: 'AccountId',
+              },
+            ],
+            subqueries: [],
+            sObject: 'Contact',
+            where: {
+              left: {
+                field: 'LastName',
+                operator: 'LIKE',
+                value: "'apple%'",
+              },
+            },
+          },
+        },
+        operator: 'AND',
+        right: {
+          left: {
+            field: 'Id',
+            operator: 'IN',
+            valueQuery: {
+              fields: [
+                {
+                  text: 'AccountId',
+                },
+              ],
+              subqueries: [],
+              sObject: 'Opportunity',
+              where: {
+                left: {
+                  field: 'isClosed',
+                  operator: '=',
+                  value: 'false',
+                },
+              },
+            },
+          },
+        },
+      },
+    },
+  },
 ];
 export default testCases;