You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@superset.apache.org by ru...@apache.org on 2026/06/13 23:34:25 UTC

(superset) branch master updated: fix(pivot_table): next try refreshed pr for fixing sorting in pivot table (#38080)

This is an automated email from the ASF dual-hosted git repository.

rusackas pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/superset.git


The following commit(s) were added to refs/heads/master by this push:
     new 6b682b1aa1e fix(pivot_table):  next try refreshed pr for fixing sorting in pivot table  (#38080)
6b682b1aa1e is described below

commit 6b682b1aa1ed8036f94d3ed8f322e561eb67312d
Author: SBIN2010 <Sb...@mail.ru>
AuthorDate: Sun Jun 14 02:34:18 2026 +0300

    fix(pivot_table):  next try refreshed pr for fixing sorting in pivot table  (#38080)
    
    Co-authored-by: Evan Rusackas <ev...@preset.io>
---
 .../src/react-pivottable/utilities.ts              | 173 +++++++++++++++++-
 .../test/react-pivottable/tableRenders.test.tsx    | 193 +++++++++++++++++++++
 2 files changed, 361 insertions(+), 5 deletions(-)

diff --git a/superset-frontend/plugins/plugin-chart-pivot-table/src/react-pivottable/utilities.ts b/superset-frontend/plugins/plugin-chart-pivot-table/src/react-pivottable/utilities.ts
index 4c76b4217d7..2839b290ddf 100644
--- a/superset-frontend/plugins/plugin-chart-pivot-table/src/react-pivottable/utilities.ts
+++ b/superset-frontend/plugins/plugin-chart-pivot-table/src/react-pivottable/utilities.ts
@@ -92,6 +92,29 @@ const numberFormat = function (optsIn?: NumberFormatOptions): Formatter {
   };
 };
 
+/**
+ * Safely converts any value to a number for aggregation purposes
+ * Handles null/undefined, strings, and non-numeric values
+ */
+function toAggregationNumber(value: unknown): number {
+  if (value == null) return 0;
+  if (typeof value === 'number') {
+    return Number.isNaN(value) ? 0 : value;
+  }
+  if (typeof value === 'string') {
+    const num = parseFloat(value.trim());
+    return Number.isNaN(num) ? 0 : num;
+  }
+  return 0;
+}
+
+type DataFunction = (key: string[], context: never[]) => unknown;
+
+interface GroupNode {
+  auto_agg_sum: number;
+  [childKey: string]: GroupNode | number;
+}
+
 const rx = /(\d+)|(\D+)/g;
 const rd = /\d/;
 const rz = /^0/;
@@ -167,6 +190,125 @@ const naturalSort: SortFunction = (as, bs) => {
   return aArr.length - bArr.length;
 };
 
+/**
+ * Precomputes aggregate sums for all group levels using safe numeric conversion
+ */
+function buildGroupAggregates(
+  keys: string[][],
+  dataFunc: DataFunction,
+): GroupNode {
+  const root: GroupNode = { auto_agg_sum: 0 } as GroupNode;
+
+  const terminalKeys = keys.filter(
+    key =>
+      !keys.some(
+        ancestorKey =>
+          ancestorKey.length > key.length &&
+          key.every((segment, idx) => ancestorKey[idx] === segment),
+      ),
+  );
+  for (const key of terminalKeys) {
+    let current: GroupNode = root;
+
+    for (let i = 0; i < key.length - 1; i += 1) {
+      const segment = key[i];
+
+      if (!current[segment]) {
+        current[segment] = { auto_agg_sum: 0 } as GroupNode;
+      }
+
+      const childNode = current[segment] as GroupNode;
+      childNode.auto_agg_sum += toAggregationNumber(dataFunc(key, []));
+      current = childNode;
+    }
+  }
+
+  return root;
+}
+
+/**
+ * Creates a comparator function for hierarchical keys with subtotal awareness
+ */
+function createHierarchicalComparator(
+  groups: GroupNode,
+  top: boolean | undefined,
+  asc: boolean,
+  dataFunc: DataFunction,
+): (a: string[], b: string[]) => number {
+  const hierarchyMultiplier = top ? 1 : -1;
+  const valueMultiplier = asc ? 1 : -1;
+
+  return (a: string[], b: string[]) => {
+    const minLen = Math.min(a.length, b.length);
+    let currentGroup: GroupNode = groups;
+    let diffIndex = -1;
+
+    for (let i = 0; i < minLen; i += 1) {
+      if (a[i] !== b[i]) {
+        diffIndex = i;
+        break;
+      }
+      currentGroup = currentGroup[a[i]] as GroupNode;
+    }
+
+    if (diffIndex === -1 && a.length !== b.length) {
+      return (a.length < b.length ? -1 : 1) * hierarchyMultiplier;
+    }
+
+    const isLastLevelComparison =
+      diffIndex === -1 ||
+      (diffIndex === a.length - 1 && diffIndex === b.length - 1);
+
+    if (isLastLevelComparison) {
+      const valA = dataFunc(a, []) as string | number | null;
+      const valB = dataFunc(b, []) as string | number | null;
+
+      const result = naturalSort(valA, valB) * valueMultiplier;
+
+      if (result === 0) {
+        const lastA = a[a.length - 1] ?? '';
+        const lastB = b[b.length - 1] ?? '';
+        return lastA.localeCompare(lastB) * valueMultiplier;
+      }
+      return result;
+    }
+
+    const segmentA = a[diffIndex];
+    const segmentB = b[diffIndex];
+
+    const nodeA = currentGroup[segmentA] as GroupNode | undefined;
+    const nodeB = currentGroup[segmentB] as GroupNode | undefined;
+
+    const sumA = nodeA?.auto_agg_sum ?? 0;
+    const sumB = nodeB?.auto_agg_sum ?? 0;
+
+    if (sumA === sumB) {
+      return segmentA.localeCompare(segmentB) * valueMultiplier;
+    }
+    return (sumA > sumB ? 1 : -1) * valueMultiplier;
+  };
+}
+
+/**
+ * @param keys Hierarchical keys to sort ([[row1, row2], [row1, row3], ...])
+ * @param dataFunc Function to retrieve aggregate value (may return string/number/null)
+ * @param top true = subtotals at top/left, false = subtotals at bottom/right
+ * @param asc true = ascending sort, false = descending sort
+ *
+ * @see https://github.com/apache/superset/issues/20564
+ */
+export function groupingValueSort(
+  keys: string[][],
+  dataFunc: DataFunction,
+  top: boolean | undefined,
+  asc: boolean,
+): void {
+  const groups = buildGroupAggregates(keys, dataFunc);
+
+  const comparator = createHierarchicalComparator(groups, top, asc, dataFunc);
+  keys.sort(comparator);
+}
+
 const sortAs = function (order: (string | number)[]): SortFunction {
   const mapping: Record<string | number, number> = {};
 
@@ -897,7 +1039,8 @@ class PivotData {
       this.sorted = true;
       const rows = this.props.rows as string[];
       const cols = this.props.cols as string[];
-      const v = (r: string[], c: string[]) => this.getAggregator(r, c).value();
+      const vr = (r: string[], c: string[]) => this.getAggregator(r, c).value();
+      const vc = (c: string[], r: string[]) => this.getAggregator(r, c).value();
       switch (this.props.rowOrder) {
         case 'key_z_to_a':
           this.rowKeys.sort(
@@ -905,10 +1048,20 @@ class PivotData {
           );
           break;
         case 'value_a_to_z':
-          this.rowKeys.sort((a, b) => naturalSort(v(a, []), v(b, [])));
+          groupingValueSort(
+            this.rowKeys,
+            vr,
+            this.subtotals.rowPartialOnTop,
+            true,
+          );
           break;
         case 'value_z_to_a':
-          this.rowKeys.sort((a, b) => -naturalSort(v(a, []), v(b, [])));
+          groupingValueSort(
+            this.rowKeys,
+            vr,
+            this.subtotals.rowPartialOnTop,
+            false,
+          );
           break;
         default:
           this.rowKeys.sort(this.arrSort(rows, this.subtotals.rowPartialOnTop));
@@ -920,10 +1073,20 @@ class PivotData {
           );
           break;
         case 'value_a_to_z':
-          this.colKeys.sort((a, b) => naturalSort(v([], a), v([], b)));
+          groupingValueSort(
+            this.colKeys,
+            vc,
+            this.subtotals.colPartialOnTop,
+            true,
+          );
           break;
         case 'value_z_to_a':
-          this.colKeys.sort((a, b) => -naturalSort(v([], a), v([], b)));
+          groupingValueSort(
+            this.colKeys,
+            vc,
+            this.subtotals.colPartialOnTop,
+            false,
+          );
           break;
         default:
           this.colKeys.sort(this.arrSort(cols, this.subtotals.colPartialOnTop));
diff --git a/superset-frontend/plugins/plugin-chart-pivot-table/test/react-pivottable/tableRenders.test.tsx b/superset-frontend/plugins/plugin-chart-pivot-table/test/react-pivottable/tableRenders.test.tsx
index b9a18d7ae8f..84e9230980e 100644
--- a/superset-frontend/plugins/plugin-chart-pivot-table/test/react-pivottable/tableRenders.test.tsx
+++ b/superset-frontend/plugins/plugin-chart-pivot-table/test/react-pivottable/tableRenders.test.tsx
@@ -17,6 +17,8 @@
  * under the License.
  */
 
+import { groupingValueSort } from '../../src/react-pivottable/utilities';
+
 import type { ReactElement } from 'react';
 import '@testing-library/jest-dom';
 import { fireEvent, render, screen } from '@testing-library/react';
@@ -353,6 +355,197 @@ test('TableRenderer coerces numeric timestamp strings to numbers for column head
   expect(screen.getByText('col:square')).toBeInTheDocument();
 });
 
+type TestData = {
+  [key: string]: number | string | null;
+};
+
+const createMockAggregator =
+  (data: TestData) =>
+  (key: string[], _context: never[]): unknown => {
+    const keyStr = key.join('|');
+    return data[keyStr] ?? null;
+  };
+
+test('should sort flat keys in ascending order', () => {
+  const keys: string[][] = [['A'], ['C'], ['B']];
+  const data = {
+    A: 30,
+    B: 10,
+    C: 20,
+  };
+
+  groupingValueSort(keys, createMockAggregator(data), false, true);
+
+  expect(keys).toEqual([['B'], ['C'], ['A']]);
+});
+
+test('should sort flat keys in descending order', () => {
+  const keys: string[][] = [['A'], ['C'], ['B']];
+  const data = {
+    A: 30,
+    B: 10,
+    C: 20,
+  };
+
+  groupingValueSort(keys, createMockAggregator(data), false, false);
+
+  expect(keys).toEqual([['A'], ['C'], ['B']]);
+});
+
+test('should place subtotal at top when top=true and ascending', () => {
+  const keys: string[][] = [
+    ['Region', 'City1'],
+    ['Region'],
+    ['Region', 'City2'],
+  ];
+  const data = {
+    Region: 150,
+    'Region|City1': 100,
+    'Region|City2': 50,
+  };
+
+  groupingValueSort(keys, createMockAggregator(data), true, true);
+
+  expect(keys[0]).toEqual(['Region']);
+  expect(keys[1]).toEqual(['Region', 'City2']);
+  expect(keys[2]).toEqual(['Region', 'City1']);
+});
+
+test('should place subtotal at bottom when top=false and descending', () => {
+  const keys: string[][] = [
+    ['Region', 'City1'],
+    ['Region'],
+    ['Region', 'City2'],
+  ];
+  const data = {
+    'Region|City1': 100,
+    'Region|City2': 50,
+    Region: 150,
+  };
+
+  groupingValueSort(keys, createMockAggregator(data), false, false);
+
+  expect(keys[0]).toEqual(['Region', 'City1']);
+  expect(keys[1]).toEqual(['Region', 'City2']);
+  expect(keys[2]).toEqual(['Region']);
+});
+
+test('should use alphabetical order for terminals with equal values', () => {
+  const keys: string[][] = [
+    ['Group', 'Apple'],
+    ['Group', 'Banana'],
+    ['Group', 'Cherry'],
+  ];
+  const data = {
+    'Group|Apple': 50,
+    'Group|Banana': 50,
+    'Group|Cherry': 50,
+  };
+
+  groupingValueSort(keys, createMockAggregator(data), false, true);
+
+  expect(keys).toEqual([
+    ['Group', 'Apple'],
+    ['Group', 'Banana'],
+    ['Group', 'Cherry'],
+  ]);
+});
+
+test('should handle null values gracefully', () => {
+  const keys: string[][] = [['A'], ['B'], ['C']];
+  const data = {
+    A: 100,
+    B: null,
+    C: 50,
+  };
+
+  groupingValueSort(keys, createMockAggregator(data), false, true);
+  expect(keys).toEqual([['B'], ['C'], ['A']]);
+});
+
+test('should handle string numbers', () => {
+  const keys: string[][] = [['A'], ['B'], ['C']];
+  const data = {
+    A: '100',
+    B: '50',
+    C: '200',
+  };
+
+  groupingValueSort(keys, createMockAggregator(data), false, false);
+  expect(keys).toEqual([['C'], ['A'], ['B']]);
+});
+
+test('should handle NaN values', () => {
+  const keys: string[][] = [['A'], ['B'], ['C']];
+  const data = {
+    A: 100,
+    B: NaN,
+    C: 50,
+  };
+
+  groupingValueSort(keys, createMockAggregator(data), false, true);
+  expect(keys).toEqual([['B'], ['C'], ['A']]);
+});
+
+test('should handle single key', () => {
+  const keys: string[][] = [['OnlyKey']];
+  const data = { OnlyKey: 42 };
+
+  groupingValueSort(keys, createMockAggregator(data), false, true);
+  expect(keys).toEqual([['OnlyKey']]);
+});
+
+test('should handle empty keys array', () => {
+  const keys: string[][] = [];
+  const data = {};
+
+  groupingValueSort(keys, createMockAggregator(data), false, true);
+  expect(keys).toEqual([]);
+});
+
+test('should handle product categories with subcategories', () => {
+  const keys: string[][] = [
+    ['Electronics'],
+    ['Electronics', 'Phones'],
+    ['Electronics', 'Phones', 'iPhone'],
+    ['Electronics', 'Phones', 'Samsung'],
+    ['Electronics', 'Laptops'],
+    ['Electronics', 'Laptops', 'MacBook'],
+    ['Clothing'],
+    ['Clothing', 'Shirts'],
+    ['Clothing', 'Shirts', 'T-Shirt'],
+    ['Clothing', 'Pants'],
+    ['Clothing', 'Pants', 'Jeans'],
+  ];
+  const data = {
+    Electronics: 2100,
+    'Electronics|Phones': 900,
+    'Electronics|Phones|iPhone': 500,
+    'Electronics|Phones|Samsung': 400,
+    'Electronics|Laptops': 1200,
+    'Electronics|Laptops|MacBook': 1200,
+    Clothing: 2550,
+    'Clothing|Shirts': 1400,
+    'Clothing|Shirts|T-Shirt': 1400,
+    'Clothing|Pants': 1150,
+    'Clothing|Pants|Jeans': 1150,
+  };
+
+  groupingValueSort(keys, createMockAggregator(data), true, true);
+
+  expect(keys[0]).toEqual(['Electronics']);
+  expect(keys[1]).toEqual(['Electronics', 'Phones']);
+  expect(keys[2]).toEqual(['Electronics', 'Phones', 'Samsung']);
+  expect(keys[3]).toEqual(['Electronics', 'Phones', 'iPhone']);
+  expect(keys[4]).toEqual(['Electronics', 'Laptops']);
+  expect(keys[5]).toEqual(['Electronics', 'Laptops', 'MacBook']);
+  expect(keys[6]).toEqual(['Clothing']);
+  expect(keys[7]).toEqual(['Clothing', 'Pants']);
+  expect(keys[8]).toEqual(['Clothing', 'Pants', 'Jeans']);
+  expect(keys[9]).toEqual(['Clothing', 'Shirts']);
+  expect(keys[10]).toEqual(['Clothing', 'Shirts', 'T-Shirt']);
+});
+
 test('TableRenderer coerces numeric timestamp strings to numbers for row header date formatters', () => {
   const dateFormatter = jest.fn((val: unknown) => `row:${String(val)}`);
   const data = [