Repository: squashql/squashql
Pull request #336 (2024-05-13 05:53:29)

add not condition


Patch:
diff --git a/core/src/main/java/io/squashql/query/Functions.java b/core/src/main/java/io/squashql/query/Functions.java
index b9fec7b..03cd366 100644
--- a/core/src/main/java/io/squashql/query/Functions.java
+++ b/core/src/main/java/io/squashql/query/Functions.java
@@ -91,11 +91,15 @@ public class Functions {
   }
 
   public static ConditionDto in(Object... values) {
-    return new InConditionDto(values, false);
+    return new InConditionDto(values);
   }
 
   public static ConditionDto notIn(Object... values) {
-    return new InConditionDto(values, true);
+    return new NotConditionDto(new InConditionDto(values));
+  }
+
+  public static ConditionDto not(ConditionDto c) {
+    return new NotConditionDto(c);
   }
 
   public static ConditionDto lt(Object value) {
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 7f0b018..3bd22b7 100644
--- a/core/src/main/java/io/squashql/query/compiled/CompiledCriteria.java
+++ b/core/src/main/java/io/squashql/query/compiled/CompiledCriteria.java
@@ -65,8 +65,7 @@ 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);
-      String sep = ic.invert ? " not " : " ";
-      return expression + sep + dto.type().sqlInfix + " (" +
+      return expression + " " + dto.type().sqlInfix + " (" +
               ic.values
                       .stream()
                       .map(sqlMapper)
@@ -84,6 +83,8 @@ public record CompiledCriteria(ConditionDto condition, ConditionType conditionTy
         case NULL, NOT_NULL -> expression + " " + cc.type.sqlInfix;
         default -> throw new IllegalStateException("Unexpected value: " + dto.type());
       };
+    } else if (dto instanceof NotConditionDto nc) {
+      return nc.type().sqlInfix + " " + toSql(field, nc.c, queryRewriter);
     } else {
       throw new RuntimeException("Not supported condition " + dto);
     }
diff --git a/core/src/main/java/io/squashql/query/dto/ConditionDto.java b/core/src/main/java/io/squashql/query/dto/ConditionDto.java
index bb45cca..3092613 100644
--- a/core/src/main/java/io/squashql/query/dto/ConditionDto.java
+++ b/core/src/main/java/io/squashql/query/dto/ConditionDto.java
@@ -8,7 +8,7 @@ import static io.squashql.query.dto.ConditionType.*;
  * Marker interface to represent a (logical, value) condition.
  */
 @JsonTypeInfo(use = JsonTypeInfo.Id.CLASS)
-public sealed interface ConditionDto permits ConstantConditionDto, InConditionDto, LogicalConditionDto, SingleValueConditionDto {
+public sealed interface ConditionDto permits ConstantConditionDto, InConditionDto, LogicalConditionDto, SingleValueConditionDto, NotConditionDto {
 
   ConditionDto NULL_CONDITION = new ConstantConditionDto(NULL);
   ConditionDto NOT_NULL_CONDITION = new ConstantConditionDto(NOT_NULL);
diff --git a/core/src/main/java/io/squashql/query/dto/ConditionType.java b/core/src/main/java/io/squashql/query/dto/ConditionType.java
index 62d9f05..15a8bec 100644
--- a/core/src/main/java/io/squashql/query/dto/ConditionType.java
+++ b/core/src/main/java/io/squashql/query/dto/ConditionType.java
@@ -54,6 +54,10 @@ public enum ConditionType {
    * Static condition, false only.
    */
   FALSE("false"),
+  /**
+   * NOT
+   */
+  NOT("not"),
   /**
    * Is Null
    */
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 533e284..fe8de1a 100644
--- a/core/src/main/java/io/squashql/query/dto/InConditionDto.java
+++ b/core/src/main/java/io/squashql/query/dto/InConditionDto.java
@@ -16,9 +16,8 @@ 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, boolean invert) {
+  public InConditionDto(Object value) {
     Set<Object> set = new HashSet<>();
     if (value.getClass().isArray()) {
       Object[] array = (Object[]) value;
@@ -31,7 +30,6 @@ 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/core/src/main/java/io/squashql/query/dto/NotConditionDto.java b/core/src/main/java/io/squashql/query/dto/NotConditionDto.java
new file mode 100644
index 0000000..babaf97
--- /dev/null
+++ b/core/src/main/java/io/squashql/query/dto/NotConditionDto.java
@@ -0,0 +1,25 @@
+package io.squashql.query.dto;
+
+import lombok.EqualsAndHashCode;
+import lombok.NoArgsConstructor;
+import lombok.ToString;
+
+@ToString
+@EqualsAndHashCode
+@NoArgsConstructor // For Jackson
+public final class NotConditionDto implements ConditionDto {
+
+  /**
+   * The condition to reverse
+   */
+  public ConditionDto c;
+
+  public NotConditionDto(ConditionDto c) {
+    this.c = c;
+  }
+
+  @Override
+  public ConditionType type() {
+    return ConditionType.NOT;
+  }
+}
diff --git a/documentation/QUERY.md b/documentation/QUERY.md
index 306f754..11a806b 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, notIn, isNull, isNotNull, like, and, or`.
+Condition operators available: `eq, neq, lt, le, gt, ge, _in, notIn, isNull, isNotNull, like, and, or, not`.
 
 If the field is an array, the condition `contains` can be used:
 
diff --git a/js/typescript-library/src/condition.ts b/js/typescript-library/src/condition.ts
index 6ed66dd..b62e8e8 100644
--- a/js/typescript-library/src/condition.ts
+++ b/js/typescript-library/src/condition.ts
@@ -24,6 +24,7 @@ export enum ConditionType {
   NULL = "NULL",
   NOT_NULL = "NOT_NULL",
   ARRAY_CONTAINS = "ARRAY_CONTAINS",
+  NOT = "NOT",
 }
 
 function toJSON(c: Condition) {
@@ -70,14 +71,31 @@ export class InCondition implements Condition {
   readonly class: string = PACKAGE + "dto.InConditionDto"
   readonly type: ConditionType = ConditionType.IN
 
-  constructor(private values: Array<Primitive>, private invert: boolean) {
+  constructor(private values: Array<Primitive>) {
   }
 
   toJSON() {
     return {
       "@class": this.class,
       "values": this.values,
-      "invert": this.invert,
+    }
+  }
+}
+
+/**
+ * Not condition
+ */
+export class NotCondition implements Condition {
+  readonly class: string = PACKAGE + "dto.NotConditionDto"
+  readonly type: ConditionType = ConditionType.NOT
+
+  constructor(readonly c: Condition) {
+  }
+
+  toJSON() {
+    return {
+      "@class": this.class,
+      "c": this.c,
     }
   }
 }
@@ -143,14 +161,21 @@ 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, false)
+  return new InCondition(value)
 }
 
 /**
  * NOT In condition on a list of string, number or boolean constants.
  */
 export function notIn(value: Array<Primitive>): Condition {
-  return new InCondition(value, true)
+  return new NotCondition(new InCondition(value))
+}
+
+/**
+ * NOT condition.
+ */
+export function not(c: Condition): Condition {
+  return new NotCondition(c)
 }
 
 /**
diff --git a/js/typescript-library/src/index.ts b/js/typescript-library/src/index.ts
index 8101936..b959516 100644
--- a/js/typescript-library/src/index.ts
+++ b/js/typescript-library/src/index.ts
@@ -57,7 +57,7 @@ export {
 export {
   Condition, ConditionType,
   eq, neq, lt, le, gt, ge, _in, like, isNull, isNotNull,
-  and, or,
+  and, or, not,
   all, any, criterion, criterion_, havingCriterion,
   SingleValueCondition
 } from './condition'
diff --git a/js/typescript-library/src/util.ts b/js/typescript-library/src/util.ts
index e78e1a4..9178d8d 100644
--- a/js/typescript-library/src/util.ts
+++ b/js/typescript-library/src/util.ts
@@ -13,7 +13,7 @@ import {
   ParametrizedMeasure,
   PartialHierarchicalComparisonMeasure
 } from "./measure"
-import {ConstantCondition, InCondition, LogicalCondition, SingleValueCondition} from "./condition"
+import {ConstantCondition, InCondition, LogicalCondition, NotCondition, SingleValueCondition} from "./condition"
 import Criteria from "./criteria"
 import {Month, Quarter, Semester, Year} from "./period"
 import {ExplicitOrder, Order, SimpleOrder} from "./order"
@@ -104,11 +104,13 @@ 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"], value["invert"])
+    return new InCondition(value["values"])
   } else if (clazz === PACKAGE + "dto.ConstantConditionDto") {
     return new ConstantCondition(value["type"])
   } else if (clazz === PACKAGE + "dto.SingleValueConditionDto") {
     return new SingleValueCondition(value["type"], value["value"])
+  } else if (clazz === PACKAGE + "dto.NotConditionDto") {
+    return new NotCondition(value["c"])
   } else if (clazz === PACKAGE + "BinaryOperationField") {
     return new BinaryOperationField(value["operator"], transformToObject("leftOperand", value["leftOperand"]), transformToObject("rightOperand", value["rightOperand"]), value["alias"])
   } else if (clazz === PACKAGE + "ConstantField") {


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 10f3bde..be14534 100644
--- a/core/src/test/java/io/squashql/query/ATestQueryExecutor.java
+++ b/core/src/test/java/io/squashql/query/ATestQueryExecutor.java
@@ -376,11 +376,19 @@ public abstract class ATestQueryExecutor extends ABaseTestQuery {
   void testLikeCondition() {
     QueryDto query = Query
             .from(this.storeName)
-            .where(tableField(SCENARIO_FIELD_NAME), Functions.like("s%")) // start with s
+            .where(tableField(SCENARIO_FIELD_NAME), like("s%")) // start with s
             .select(tableFields(List.of(SCENARIO_FIELD_NAME)), List.of())
             .build();
     Table table = this.executor.executeQuery(query);
     Assertions.assertThat(table).containsExactly(List.of("s1"), List.of("s2"));
+
+    query = Query
+            .from(this.storeName)
+            .where(tableField(SCENARIO_FIELD_NAME), not(like("s%"))) // NOT start with s
+            .select(tableFields(List.of(SCENARIO_FIELD_NAME)), List.of())
+            .build();
+    table = this.executor.executeQuery(query);
+    Assertions.assertThat(table).containsExactly(List.of(MAIN_SCENARIO_NAME));
   }
 
   @Test
diff --git a/js/typescript-library/src/__tests__/generate-json-queryDto.ts b/js/typescript-library/src/__tests__/generate-json-queryDto.ts
index 59db547..9c12f85 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, notIn,
+  lt, not, notIn,
   or
 } from "../condition"
 import * as fs from "fs"
@@ -126,6 +126,7 @@ export function buildQuery(): Query {
     criterion(upper(new TableField("f8")), eq("A")),
     criterion_(new TableField("f9"), currentDate(), ConditionType.GT),
     criterion(new TableField("f10"), notIn([1, 2])),
+    criterion(new TableField("f11"), not(like("s%"))),
   ]))
 
   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 f8ffa7a..e5821e0 100644
--- a/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java
+++ b/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java
@@ -123,7 +123,8 @@ public class TestJavascriptLibrary {
             criterion(lower(tableField("f7")), eq("a")),
             criterion(upper(tableField("f8")), eq("A")),
             criterion(new TableField("f9"), currentDate(), ConditionType.GT),
-            criterion(new TableField("f10"), notIn(1, 2))
+            criterion(new TableField("f10"), notIn(1, 2)),
+            criterion(new TableField("f11"), not(like("s%")))
     ));
 
     q.withHavingCriteria(all(