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 = [