Repository: squashql/squashql
Pull request #335 (2024-05-10 13:08:59)

Add notin operator


Patch:
diff --git a/core/src/main/java/io/squashql/query/Functions.java b/core/src/main/java/io/squashql/query/Functions.java
index a722c50..b9fec7b 100644
--- a/core/src/main/java/io/squashql/query/Functions.java
+++ b/core/src/main/java/io/squashql/query/Functions.java
@@ -91,7 +91,11 @@ public class Functions {
   }
 
   public static ConditionDto in(Object... values) {
-    return new InConditionDto(values);
+    return new InConditionDto(values, false);
+  }
+
+  public static ConditionDto notIn(Object... values) {
+    return new InConditionDto(values, true);
   }
 
   public static ConditionDto lt(Object value) {
@@ -272,16 +276,16 @@ public class Functions {
   // COMPARISONS
 
   public static PartialHierarchicalComparisonMeasure comparisonMeasureWithParentOfAxis(String alias,
-                                                          ComparisonMethod comparisonMethod,
-                                                          Measure underlyingMeasure,
-                                                          Axis axis) {
+                                                                                       ComparisonMethod comparisonMethod,
+                                                                                       Measure underlyingMeasure,
+                                                                                       Axis axis) {
     return new PartialHierarchicalComparisonMeasure(alias, comparisonMethod, true, underlyingMeasure, axis, false);
   }
 
   public static PartialHierarchicalComparisonMeasure comparisonMeasureWithTotalOfAxis(String alias,
-                                                         ComparisonMethod comparisonMethod,
-                                                         Measure underlyingMeasure,
-                                                         Axis axis) {
+                                                                                      ComparisonMethod comparisonMethod,
+                                                                                      Measure underlyingMeasure,
+                                                                                      Axis axis) {
     return new PartialHierarchicalComparisonMeasure(alias, comparisonMethod, true, underlyingMeasure, axis, true);
   }
 }
diff --git a/core/src/main/java/io/squashql/query/compiled/CompiledCriteria.java b/core/src/main/java/io/squashql/query/compiled/CompiledCriteria.java
index b7122d8..7f0b018 100644
--- a/core/src/main/java/io/squashql/query/compiled/CompiledCriteria.java
+++ b/core/src/main/java/io/squashql/query/compiled/CompiledCriteria.java
@@ -65,7 +65,8 @@ public record CompiledCriteria(ConditionDto condition, ConditionType conditionTy
       };
     } else if (dto instanceof InConditionDto ic) {
       Function<Object, String> sqlMapper = SqlTranslator.getQuoteFn(field.type(), ic.values.iterator().next().getClass(), queryRewriter);
-      return expression + " " + dto.type().sqlInfix + " (" +
+      String sep = ic.invert ? " not " : " ";
+      return expression + sep + dto.type().sqlInfix + " (" +
               ic.values
                       .stream()
                       .map(sqlMapper)
diff --git a/core/src/main/java/io/squashql/query/dto/InConditionDto.java b/core/src/main/java/io/squashql/query/dto/InConditionDto.java
index fe8de1a..533e284 100644
--- a/core/src/main/java/io/squashql/query/dto/InConditionDto.java
+++ b/core/src/main/java/io/squashql/query/dto/InConditionDto.java
@@ -16,8 +16,9 @@ import static io.squashql.query.dto.ConditionType.IN;
 public final class InConditionDto implements ConditionDto {
 
   public Set<Object> values;
+  public boolean invert;
 
-  public InConditionDto(Object value) {
+  public InConditionDto(Object value, boolean invert) {
     Set<Object> set = new HashSet<>();
     if (value.getClass().isArray()) {
       Object[] array = (Object[]) value;
@@ -30,6 +31,7 @@ public final class InConditionDto implements ConditionDto {
       throw new IllegalArgumentException("Unexpected value for in condition " + value);
     }
     this.values = set;
+    this.invert = invert;
   }
 
   @Override
diff --git a/documentation/QUERY.md b/documentation/QUERY.md
index 5616a7b..306f754 100644
--- a/documentation/QUERY.md
+++ b/documentation/QUERY.md
@@ -160,7 +160,7 @@ WHERE (col1 IN ('a', 'b') AND col2 = 'c')
 GROUP BY col1, col2
 ```
 
-Condition operators available: `eq, neq, lt, le, gt, ge, _in, isNull, isNotNull, like, and, or`.
+Condition operators available: `eq, neq, lt, le, gt, ge, _in, notIn, isNull, isNotNull, like, and, or`.
 
 If the field is an array, the condition `contains` can be used:
 
diff --git a/js/typescript-library/package-lock.json b/js/typescript-library/package-lock.json
index e6226c4..ae2b9e2 100644
--- a/js/typescript-library/package-lock.json
+++ b/js/typescript-library/package-lock.json
@@ -1,12 +1,12 @@
 {
   "name": "@squashql/squashql-js",
-  "version": "1.18.2",
+  "version": "1.19.0",
   "lockfileVersion": 2,
   "requires": true,
   "packages": {
     "": {
       "name": "@squashql/squashql-js",
-      "version": "1.18.2",
+      "version": "1.19.0",
       "license": "Apache-2.0",
       "dependencies": {
         "axios": "^1.6.8"
diff --git a/js/typescript-library/src/condition.ts b/js/typescript-library/src/condition.ts
index 7b31b53..6ed66dd 100644
--- a/js/typescript-library/src/condition.ts
+++ b/js/typescript-library/src/condition.ts
@@ -70,13 +70,14 @@ export class InCondition implements Condition {
   readonly class: string = PACKAGE + "dto.InConditionDto"
   readonly type: ConditionType = ConditionType.IN
 
-  constructor(private values: Array<Primitive>) {
+  constructor(private values: Array<Primitive>, private invert: boolean) {
   }
 
   toJSON() {
     return {
       "@class": this.class,
       "values": this.values,
+      "invert": this.invert,
     }
   }
 }
@@ -142,7 +143,14 @@ export function isNotNull(): Condition {
  * In condition on a list of string, number or boolean constants.
  */
 export function _in(value: Array<Primitive>): Condition {
-  return new InCondition(value)
+  return new InCondition(value, false)
+}
+
+/**
+ * NOT In condition on a list of string, number or boolean constants.
+ */
+export function notIn(value: Array<Primitive>): Condition {
+  return new InCondition(value, true)
 }
 
 /**
diff --git a/js/typescript-library/src/util.ts b/js/typescript-library/src/util.ts
index fa54459..e78e1a4 100644
--- a/js/typescript-library/src/util.ts
+++ b/js/typescript-library/src/util.ts
@@ -104,7 +104,7 @@ function transformToObject(key: string, value: any, reviverFallback?: (key: stri
   } else if (clazz === PACKAGE + "dto.LogicalConditionDto") {
     return new LogicalCondition(value["type"], transformToObject("one", value["one"]), transformToObject("two", value["two"]))
   } else if (clazz === PACKAGE + "dto.InConditionDto") {
-    return new InCondition(value["values"])
+    return new InCondition(value["values"], value["invert"])
   } else if (clazz === PACKAGE + "dto.ConstantConditionDto") {
     return new ConstantCondition(value["type"])
   } else if (clazz === PACKAGE + "dto.SingleValueConditionDto") {


Test patch:
diff --git a/core/src/test/java/io/squashql/query/ATestQueryExecutor.java b/core/src/test/java/io/squashql/query/ATestQueryExecutor.java
index a889217..10f3bde 100644
--- a/core/src/test/java/io/squashql/query/ATestQueryExecutor.java
+++ b/core/src/test/java/io/squashql/query/ATestQueryExecutor.java
@@ -292,7 +292,7 @@ public abstract class ATestQueryExecutor extends ABaseTestQuery {
   }
 
   @Test
-  void testQuerySeveralCoordinates() {
+  void testInCondition() {
     QueryDto query = Query
             .from(this.storeName)
             .where(tableField(SCENARIO_FIELD_NAME), in("s1", "s2"))
@@ -304,6 +304,17 @@ public abstract class ATestQueryExecutor extends ABaseTestQuery {
             List.of("s2", 14.5d, 33L));
   }
 
+  @Test
+  void testNotInCondition() {
+    QueryDto query = Query
+            .from(this.storeName)
+            .where(tableField(SCENARIO_FIELD_NAME), notIn("s1", "s2"))
+            .select(tableFields(List.of(SCENARIO_FIELD_NAME)), List.of(sum("p", "price"), sum("q", "quantity")))
+            .build();
+    Table table = this.executor.executeQuery(query);
+    Assertions.assertThat(table).containsExactly(List.of(MAIN_SCENARIO_NAME, 15.0d, 33L));
+  }
+
   @Test
   void testQuerySingleCoordinate() {
     QueryDto query = Query
diff --git a/js/typescript-library/src/__tests__/dependencies/computeConditionDependencies.test.ts b/js/typescript-library/src/__tests__/dependencies/computeConditionDependencies.test.ts
index 694a803..0d95135 100644
--- a/js/typescript-library/src/__tests__/dependencies/computeConditionDependencies.test.ts
+++ b/js/typescript-library/src/__tests__/dependencies/computeConditionDependencies.test.ts
@@ -1,9 +1,9 @@
 import * as dependencies from "../../dependencies"
 import {
+  _in,
   Condition,
   ConditionType,
   ConstantCondition,
-  InCondition,
   LogicalCondition,
   SingleValueCondition
 } from "../../condition"
@@ -22,7 +22,7 @@ describe('computeConditionDependencies', () => {
   })
 
   it('should compute no dependencies for InCondition', () => {
-    const condition = new InCondition([88])
+    const condition = _in([88])
     const result = dependencies.computeConditionDependencies(condition)
 
     expect(result.length).toEqual(0)
diff --git a/js/typescript-library/src/__tests__/dependencies/computeCriteriaDependencies.test.ts b/js/typescript-library/src/__tests__/dependencies/computeCriteriaDependencies.test.ts
index 53d17e2..1f062e2 100644
--- a/js/typescript-library/src/__tests__/dependencies/computeCriteriaDependencies.test.ts
+++ b/js/typescript-library/src/__tests__/dependencies/computeCriteriaDependencies.test.ts
@@ -2,7 +2,7 @@ import {TableField} from "../../field"
 import Criteria from "../../criteria"
 import * as dependencies from "../../dependencies"
 import {AggregatedMeasure} from "../../measure"
-import {ConditionType, InCondition} from "../../condition"
+import {_in, ConditionType} from "../../condition"
 
 afterEach(() => {
   jest.restoreAllMocks()
@@ -15,8 +15,8 @@ describe('computeCriteriaDependencies', () => {
   const mockField3 = new TableField('mockField3')
   const mockField4 = new TableField('mockField4')
   const mockField5 = new TableField('mockField5')
-  const mockCondition2 = new InCondition([33, 44])
-  const mockCondition4 = new InCondition([22])
+  const mockCondition2 = _in([33, 44])
+  const mockCondition4 = _in([22])
 
   it('should compute dependencies for Criteria with fields', () => {
     const criteria = new Criteria(mockField1, mockField2, undefined, undefined, undefined, [])
diff --git a/js/typescript-library/src/__tests__/generate-json-queryDto.ts b/js/typescript-library/src/__tests__/generate-json-queryDto.ts
index cc84cd9..59db547 100644
--- a/js/typescript-library/src/__tests__/generate-json-queryDto.ts
+++ b/js/typescript-library/src/__tests__/generate-json-queryDto.ts
@@ -35,7 +35,7 @@ import {
   isNotNull,
   isNull,
   like,
-  lt,
+  lt, notIn,
   or
 } from "../condition"
 import * as fs from "fs"
@@ -125,6 +125,7 @@ export function buildQuery(): Query {
     criterion(lower(new TableField("f7")), eq("a")),
     criterion(upper(new TableField("f8")), eq("A")),
     criterion_(new TableField("f9"), currentDate(), ConditionType.GT),
+    criterion(new TableField("f10"), notIn([1, 2])),
   ]))
 
   q.withHavingCriteria(all([
diff --git a/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java b/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java
index 519a449..f8ffa7a 100644
--- a/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java
+++ b/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java
@@ -122,7 +122,8 @@ public class TestJavascriptLibrary {
             criterion(tableField("f6"), contains(2)),
             criterion(lower(tableField("f7")), eq("a")),
             criterion(upper(tableField("f8")), eq("A")),
-            criterion(new TableField("f9"), currentDate(), ConditionType.GT)
+            criterion(new TableField("f9"), currentDate(), ConditionType.GT),
+            criterion(new TableField("f10"), notIn(1, 2))
     ));
 
     q.withHavingCriteria(all(