Repository: squashql/squashql
Pull request #284 (2024-03-07 12:59:43)

Expose hiddentotals in pivot query result 


Patch:
diff --git a/core/src/main/java/io/squashql/query/QueryExecutor.java b/core/src/main/java/io/squashql/query/QueryExecutor.java
index a3e92ba..d5c0261 100644
--- a/core/src/main/java/io/squashql/query/QueryExecutor.java
+++ b/core/src/main/java/io/squashql/query/QueryExecutor.java
@@ -76,7 +76,8 @@ public class QueryExecutor {
     return new PivotTable(result,
             pivotTableQueryDto.rows.stream().map(SqlUtils::squashqlExpression).toList(),
             pivotTableQueryDto.columns.stream().map(SqlUtils::squashqlExpression).toList(),
-            values);
+            values,
+            pivotTableQueryDto.hiddenTotals == null ? Collections.emptyList() : pivotTableQueryDto.hiddenTotals.stream().map(SqlUtils::squashqlExpression).toList());
   }
 
   public Table executeRaw(String rawSqlQuery) {
diff --git a/core/src/main/java/io/squashql/query/QueryMergeExecutor.java b/core/src/main/java/io/squashql/query/QueryMergeExecutor.java
index b49c9b2..05b0392 100644
--- a/core/src/main/java/io/squashql/query/QueryMergeExecutor.java
+++ b/core/src/main/java/io/squashql/query/QueryMergeExecutor.java
@@ -55,7 +55,8 @@ public class QueryMergeExecutor {
     return new PivotTable(table,
             rows.stream().map(SqlUtils::squashqlExpression).toList(),
             columns.stream().map(SqlUtils::squashqlExpression).toList(),
-            values);
+            values,
+            Collections.emptyList()); // not supported for now
   }
 
   private static List<Field> getLocalFields(List<Field> elements, QueryDto query, Set<Field> columnsFromColumnSets) {
diff --git a/core/src/main/java/io/squashql/query/dto/PivotTableQueryDto.java b/core/src/main/java/io/squashql/query/dto/PivotTableQueryDto.java
index ae5346e..7bd83b9 100644
--- a/core/src/main/java/io/squashql/query/dto/PivotTableQueryDto.java
+++ b/core/src/main/java/io/squashql/query/dto/PivotTableQueryDto.java
@@ -6,6 +6,7 @@ import lombok.EqualsAndHashCode;
 import lombok.NoArgsConstructor;
 import lombok.ToString;
 
+import java.util.Collections;
 import java.util.List;
 
 @ToString
@@ -17,11 +18,9 @@ public class PivotTableQueryDto {
   public QueryDto query;
   public List<Field> rows;
   public List<Field> columns;
-  public List<Field> hideTotals;
+  public List<Field> hiddenTotals;
 
   public PivotTableQueryDto(QueryDto query, List<Field> rows, List<Field> columns) {
-    this.query = query;
-    this.rows = rows;
-    this.columns = columns;
+    this(query, rows, columns, Collections.emptyList());
   }
 }
diff --git a/core/src/main/java/io/squashql/query/dto/PivotTableQueryResultDto.java b/core/src/main/java/io/squashql/query/dto/PivotTableQueryResultDto.java
index 310ec14..d59b584 100644
--- a/core/src/main/java/io/squashql/query/dto/PivotTableQueryResultDto.java
+++ b/core/src/main/java/io/squashql/query/dto/PivotTableQueryResultDto.java
@@ -17,4 +17,5 @@ public class PivotTableQueryResultDto {
   public List<String> rows;
   public List<String> columns;
   public List<String> values;
+  public List<String> hiddenTotals;
 }
diff --git a/core/src/main/java/io/squashql/table/PivotTable.java b/core/src/main/java/io/squashql/table/PivotTable.java
index 6810d83..c5eab72 100644
--- a/core/src/main/java/io/squashql/table/PivotTable.java
+++ b/core/src/main/java/io/squashql/table/PivotTable.java
@@ -9,12 +9,14 @@ public class PivotTable {
   public final List<String> rows;
   public final List<String> columns;
   public final List<String> values;
+  public final List<String> hiddenTotals;
 
-  public PivotTable(Table table, List<String> rows, List<String> columns, List<String> values) {
+  public PivotTable(Table table, List<String> rows, List<String> columns, List<String> values, List<String> hiddenTotals) {
     this.table = table;
     this.rows = rows;
     this.columns = columns;
     this.values = values;
+    this.hiddenTotals = hiddenTotals;
     this.pivotTableCells = PivotTableUtils.pivot(this);
   }
 
diff --git a/core/src/main/java/io/squashql/table/PivotTableUtils.java b/core/src/main/java/io/squashql/table/PivotTableUtils.java
index 2e65ef5..3d59848 100644
--- a/core/src/main/java/io/squashql/table/PivotTableUtils.java
+++ b/core/src/main/java/io/squashql/table/PivotTableUtils.java
@@ -306,11 +306,11 @@ public final class PivotTableUtils {
   }
 
   private static void addGroupingSetsToExcludeFromHideTotals(PivotTableContext context, List<Field> rows, Set<Set<Field>> toExclude, List<Field> columns) {
-    List<Field> hideTotalsOnRows = context.hideTotals.stream().filter(rows::contains).toList();
-    for (Field hideTotal : hideTotalsOnRows) {
+    List<Field> hiddenTotalsOnRows = context.hiddenTotals.stream().filter(rows::contains).toList();
+    for (Field hiddenTotal : hiddenTotalsOnRows) {
       Set<Field> s = new HashSet<>();
       for (Field row : rows) {
-        if (row.equals(hideTotal)) {
+        if (row.equals(hiddenTotal)) {
           break;
         }
         s.add(row);
@@ -354,10 +354,10 @@ public final class PivotTableUtils {
     private final List<Field> cleansedRows;
     private final List<Field> columns;
     private final List<Field> cleansedColumns;
-    private final List<Field> hideTotals;
+    private final List<Field> hiddenTotals;
 
     public PivotTableContext(PivotTableQueryDto pivotTableQueryDto) {
-      this.hideTotals = pivotTableQueryDto.hideTotals == null ? Collections.emptyList() : pivotTableQueryDto.hideTotals;
+      this.hiddenTotals = pivotTableQueryDto.hiddenTotals == null ? Collections.emptyList() : pivotTableQueryDto.hiddenTotals;
       this.rows = pivotTableQueryDto.rows;
       this.cleansedRows = cleanse(pivotTableQueryDto.query, pivotTableQueryDto.rows);
       this.columns = pivotTableQueryDto.columns;
diff --git a/js/typescript-library/src/generate-json-query-pivot.ts b/js/typescript-library/src/generate-json-query-pivot.ts
index 8405c41..d484e58 100644
--- a/js/typescript-library/src/generate-json-query-pivot.ts
+++ b/js/typescript-library/src/generate-json-query-pivot.ts
@@ -16,7 +16,7 @@ export function generateFromQueryPivot() {
   const pivotQuery = createPivotTableQuery(q, {
     rows: [tableField("a")],
     columns: [tableField("b")],
-    hideTotals: [tableField("b")]
+    hiddenTotals: [tableField("b")]
   })
   const data = JSON.stringify(pivotQuery)
   fs.writeFileSync('json/build-from-query-pivot.json', data)
diff --git a/js/typescript-library/src/generate-json-query-results.ts b/js/typescript-library/src/generate-json-query-results.ts
index e8b3d2c..63c3e1d 100644
--- a/js/typescript-library/src/generate-json-query-results.ts
+++ b/js/typescript-library/src/generate-json-query-results.ts
@@ -30,7 +30,8 @@ function generateFromPivotQueryResult() {
     ],
     rows: ["key1", "key2"],
     columns: ["key3"],
-    values: ["key4"]
+    values: ["key4"],
+    hiddenTotals: ["key2"]
   }
   fs.writeFileSync('json/build-from-pivot-query-result.json', JSON.stringify(r))
 }
diff --git a/js/typescript-library/src/index.ts b/js/typescript-library/src/index.ts
index d4fa7fc..9549ec8 100644
--- a/js/typescript-library/src/index.ts
+++ b/js/typescript-library/src/index.ts
@@ -19,7 +19,7 @@ export {
   comparisonMeasureWithPeriod, comparisonMeasureWithinSameGroup, comparisonMeasureWithParent, comparisonMeasureWithGrandTotalAlongAncestors,
   comparisonMeasureWithGrandTotal,
   totalCount,
-  ComparisonMethod,
+  ComparisonMethod, BinaryOperator, BinaryOperationMeasure
 } from './measure'
 
 export {
diff --git a/js/typescript-library/src/pivotTableQuery.ts b/js/typescript-library/src/pivotTableQuery.ts
index 27aafaf..0f2c624 100644
--- a/js/typescript-library/src/pivotTableQuery.ts
+++ b/js/typescript-library/src/pivotTableQuery.ts
@@ -6,7 +6,7 @@ export interface PivotTableQuery {
   query: Query
   rows: Array<Field>
   columns: Array<Field>
-  hideTotals?: Array<Field>
+  hiddenTotals?: Array<Field>
 }
 
 export interface PivotTableQueryMerge {
@@ -18,5 +18,5 @@ export interface PivotTableQueryMerge {
 export interface PivotConfig {
   rows: Array<Field>
   columns: Array<Field>
-  hideTotals?: Array<Field>
+  hiddenTotals?: Array<Field>
 }
diff --git a/js/typescript-library/src/querier.ts b/js/typescript-library/src/querier.ts
index bcd7c32..fd238c5 100644
--- a/js/typescript-library/src/querier.ts
+++ b/js/typescript-library/src/querier.ts
@@ -69,7 +69,7 @@ export class Querier {
 }
 
 export function createPivotTableQuery(query: Query, pivotConfig: PivotConfig): PivotTableQuery {
-  return {query, rows: pivotConfig.rows, columns: pivotConfig.columns, hideTotals: pivotConfig.hideTotals}
+  return {query, rows: pivotConfig.rows, columns: pivotConfig.columns, hiddenTotals: pivotConfig.hiddenTotals}
 }
 
 export function createPivotTableQueryMerge(query: QueryMerge, pivotConfig: PivotConfig): PivotTableQueryMerge {
@@ -87,6 +87,7 @@ export interface PivotTableQueryResult {
   cells: Array<Record<string, any>>,
   rows: Array<string>
   columns: Array<string>
+  hiddenTotals: Array<string>
   values: Array<string>
 }
 
diff --git a/server/src/main/java/io/squashql/spring/web/rest/QueryController.java b/server/src/main/java/io/squashql/spring/web/rest/QueryController.java
index 536852b..cb520e3 100644
--- a/server/src/main/java/io/squashql/spring/web/rest/QueryController.java
+++ b/server/src/main/java/io/squashql/spring/web/rest/QueryController.java
@@ -64,7 +64,7 @@ public class QueryController {
             true,
             null);
     List<Map<String, Object>> cells = PivotTableUtils.generateCells(pt, pivotTableQueryDto.query.minify);
-    return ResponseEntity.ok(new PivotTableQueryResultDto(cells, pt.rows, pt.columns, pt.values));
+    return ResponseEntity.ok(new PivotTableQueryResultDto(cells, pt.rows, pt.columns, pt.values, pt.hiddenTotals));
   }
 
   @PostMapping(MAPPING_QUERY_MERGE)
@@ -82,7 +82,7 @@ public class QueryController {
             this.squashQLUserSupplier == null ? null : this.squashQLUserSupplier.get()
     );
     List<Map<String, Object>> cells = PivotTableUtils.generateCells(pt, pivotTableQueryMergeDto.query.minify);
-    return ResponseEntity.ok(new PivotTableQueryResultDto(cells, pt.rows, pt.columns, pt.values));
+    return ResponseEntity.ok(new PivotTableQueryResultDto(cells, pt.rows, pt.columns, pt.values, pt.hiddenTotals));
   }
 
   @PostMapping(MAPPING_QUERY_JOIN_EXPERIMENTAL)


Test patch:
diff --git a/core/src/test/java/io/squashql/query/ATestDocDrillacross.java b/core/src/test/java/io/squashql/query/ATestDocDrillacross.java
index 5698f87..84f1e69 100644
--- a/core/src/test/java/io/squashql/query/ATestDocDrillacross.java
+++ b/core/src/test/java/io/squashql/query/ATestDocDrillacross.java
@@ -71,6 +71,6 @@ public abstract class ATestDocDrillacross extends ABaseTestQuery {
             null);
     pt.show();
     List<Map<String, Object>> cells = PivotTableUtils.generateCells(pt, false);
-    System.out.println(JacksonUtil.serialize(new PivotTableQueryResultDto(cells, pt.rows, pt.columns, pt.values)));
+    System.out.println(JacksonUtil.serialize(new PivotTableQueryResultDto(cells, pt.rows, pt.columns, pt.values, pt.hiddenTotals)));
   }
 }
diff --git a/duckdb/src/test/java/io/squashql/TestDuckDBDoc.java b/duckdb/src/test/java/io/squashql/TestDuckDBDoc.java
index 6ab980f..fe54046 100644
--- a/duckdb/src/test/java/io/squashql/TestDuckDBDoc.java
+++ b/duckdb/src/test/java/io/squashql/TestDuckDBDoc.java
@@ -307,6 +307,6 @@ public class TestDuckDBDoc {
    */
   private static void toJson(PivotTable pt) {
     List<Map<String, Object>> cells = PivotTableUtils.generateCells(pt, null);
-    System.out.println(JacksonUtil.serialize(new PivotTableQueryResultDto(cells, pt.rows, pt.columns, pt.values)));
+    System.out.println(JacksonUtil.serialize(new PivotTableQueryResultDto(cells, pt.rows, pt.columns, pt.values, pt.hiddenTotals)));
   }
 }
diff --git a/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java b/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java
index 512a0a8..d843df9 100644
--- a/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java
+++ b/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java
@@ -332,6 +332,7 @@ public class TestJavascriptLibrary {
     expected.rows = List.of("key1", "key2");
     expected.columns = List.of("key3");
     expected.values = List.of("key4");
+    expected.hiddenTotals = List.of("key2");
     PivotTableQueryResultDto r = JacksonUtil.deserialize(TestUtil.readAllLines(name), PivotTableQueryResultDto.class);
     Assertions.assertThat(r).isEqualTo(expected);
   }