DataTable sorting doesnt work with currencies

[thejoshualewis]

Describe the bug

If a number is 123456 and another is 654321, 789, the sort works. If it contains any sort of comma/period, the sort does not work.

IE
$322
$1234.56
$6543.21

Sort works both ways.

However, with the same numbers with a ",", both backend api and frontend, does not sort., instead will sort to
$1,234.56
$3.22
$6,543.21

Reproducer

No response

PrimeReact version

9.6.2

React version

17.x

Language

TypeScript

Build / Runtime

Create React App (CRA)

Browser(s)

No response

Steps to reproduce the behavior

No response

Expected behavior

No response

[melloware]

Because of the $ it is just treating those as Strings and not numbers and sorting them in String sort order.

It uses new Intl.Collator(locale, { numeric: true }) to sort the strings so I wonder if it needs more settings by default. https://reference.codeproject.com/javascript/Reference/Global_Objects/Collator

Maybe ignore­Punctua­tion ?

[thejoshualewis]

@melloware Thanks, is ignorePunctuation a flag in the datatable sort? I didnt see that in the documentation. And its not the $ thats causing the issue, its the "," . If I have numbers without a comma but with a $, its fine.

[melloware]

@thejoshualewis no all of the sorting in all of PrimeReact happens in this code...

primereact/components/lib/utils/ObjectUtils.js

Lines 373 to 402 in 189409c

	static sort(value1, value2, order = 1, comparator, nullSortOrder = 1) {
	const result = this.compare(value1, value2, comparator, order);
	let finalSortOrder = order;
	// nullSortOrder == 1 means Excel like sort nulls at bottom
	if (this.isEmpty(value1) || this.isEmpty(value2)) {
	finalSortOrder = nullSortOrder === 1 ? order : nullSortOrder;
	}
	return finalSortOrder * result;
	}
	static compare(value1, value2, comparator, order = 1) {
	let result = -1;
	const emptyValue1 = this.isEmpty(value1);
	const emptyValue2 = this.isEmpty(value2);
	if (emptyValue1 && emptyValue2) result = 0;
	else if (emptyValue1) result = order;
	else if (emptyValue2) result = -order;
	else if (typeof value1 === 'string' && typeof value2 === 'string') result = comparator(value1, value2);
	else result = value1 < value2 ? -1 : value1 > value2 ? 1 : 0;
	return result;
	}
	static localeComparator(locale) {
	//performance gain using Int.Collator. It is not recommended to use localeCompare against large arrays.
	return new Intl.Collator(locale, { numeric: true }).compare;
	}

In there is uses an Intl.Collator based on your locale so it knows how to treat comparing strings and numbers.

[melloware]

Can you update this example: https://stackblitz.com/edit/react-g4dnnj and change price column to demonstrate your issue so I can see it?

[thejoshualewis]

Hmm..would be nice to add a flag in the sort like "numeric", "exclude ('characters'), etc.
I tried that site, but it was clunky and didnt save the data.

But in my example, I would have sort asc, and would hace
$3,000
$3.12
$4,000

etc.
If i remove the comma, its fine

[melloware]

ok updated: https://stackblitz.com/edit/react-g4dnnj

[thejoshualewis]

Thanks, here's the 2 images. The $3.12 should be at the bottom or top, depending on the sort, but stays in the middle.

[melloware]

Unfortunately this is just how JavaScript works. See my console.

To workaround this you should use sortField="currencyAsNumber" https://primereact.org/datatable/#api.Column.props.sortField

So in your JSON have the value as a Number and as a String currency and use the one for display and one for sorting.

field="currency" sortField="currencyNumeric" or something like that

[thejoshualewis]

Nice, I'll give that a try. I was only only looking at this page for sort options
https://primereact.org/datatable/

Thanks for the help