Force null or empty values to bottom when sorting?

[sa7mon]

Hi all,

I've got a column in my table that has some empty or null values. Is there an easy way to force these to always be at the bottom no matter which direction the column is sorted?

This is my custom sort method so far:

let customSort = (rowA, rowB, columnId) => {
  let a = rowA.values[columnId]
  let b = rowB.values[columnId]

  if (a === '' || a === null) {
    if (b === '' || b === null) {
        return 0; // Both empty/null
    }
    return -1; // Sort a to an index lower than b
  }

  if (b === '' || b === null) {
      if (a === '' || a === null) {
          return 0; // Both empty/null
      }
      return 1; // Sort b to an index lower than a
  }

  if (a > b) return 1;
  if (b > a) return -1;
  return 0;
}

which is referenced in my column config:

...
{
    id: 'applications',
    Header: 'Application ID',
    accessor: a => a.id,
    filterable: true,
    minWidth: 20,
    sortType: customSort,
},
...

This seems to work fine when sorting in Descending order, but when I toggle sorting to Ascending, all the blank values are at the top. Is there an easy way to fix this?

Thanks!

[Zertz]

Here's what I came up with to keep falsy values at the end:

const sortTypes = useMemo(
    () => ({
        alphanumericFalsyLast(rowA, rowB, columnId, desc) {
            if (!rowA.values[columnId] && !rowB.values[columnId]) {
                return 0;
            }

            if (!rowA.values[columnId]) {
                return desc ? -1 : 1;
            }

            if (!rowB.values[columnId]) {
                return desc ? 1 : -1;
            }

            return rowA.values[columnId].localeCompare(rowB.values[columnId]);
        },
    }),
    []
);

const columns = [{
  sortType: "alphanumericFalsyLast",
}];

useTable({ columns, sortTypes }, useSortBy);

[aruprakshit]

@Zertz As MDN doc said return value may not be always -1 or +1, it can be any positive or negative number, but this lib doc said it must be +1 or -1. So will the user of localeCompare as you suggested still work or need a bit more improvement?

[Zertz]

Fortunately, that is not the case and there's an open PR to clarify the docs.

[andreyk-code]

I had the same problem. Got it to work by adding useMemo and using same format as @Zertz. Thanks for sharing!

It's called out in the docs that any custom sort functions have to be memoized. I think this is key to making it work.

[prasanthbattula]

const sortTypes = React.useMemo(
    () => ({
      alphanumericFalsyLast(rowA, rowB, columnId) {
        
        const a = Number(rowA.values[columnId]);
        const b = Number(rowB.values[columnId]);

        return Number(a) - Number(b);
      },
    }),
    []
  );

It worked for me.

[VincentHokie]

I had a similar problem today. My solution would translate to something like below for you

const customSort = () =>
  React.useCallback((rowA, rowB, columnId, desc) => {

   let a = rowA.values[columnId]
   let b = rowB.values[columnId] 

    /**
     * These first 3 conditions keep our null values at the bottom.
     */
    if (a === null && b !== null) {
      return desc ? -1 : 1;
    }

    if (a !== null && b === null) {
      return desc ? 1 : -1;
    }

    if (a === null && === null) {
      return 0;
    }

    // at this point, you have non-null values and you should do whatever is required to sort those values correctly
    return a -b

  }, []);

A more generic answer would be

const sortFunctionName = () =>
  React.useCallback((rowA, rowB, columnId, desc) => {

    /**
     * These first 3 conditions keep our null values at the bottom.
     */
    if (conditionRowAToGoBottom && conditionRowBToSortAtTheTop) {
      return desc ? -1 : 1;
    }

    if (conditionRowAToSortAtTheTop && conditionRowBToGoBottom) {
      return desc ? 1 : -1;
    }

    if (conditionRowAToGoBottom && conditionRowBToGoBottom) {
      return 0;
    }

    // at this point, you have values that require sorting at the top of the list, so..do that? :)
    return rowValueA - rowValueB

  }, []);

Hope this helps someone

[lisahillebrand]

I would like to use a custom sort function and always display rows with undefined value in the respective column at the bottom of the table. They should be at the bottom whether I use desc or asc sorting. The sortingFn does not receive the sort order as param.

How can this be achieved with v8 without maintaining the sort state myself?

Thanks for your help.

[lindesvard]

Looking for similar options. Since we dont get desc or asc in sortingFn its hard to do.

[ipsips]

my solution is to translate "empty" values as last unicode character:

/**
 * Overriding default sorting function so that empty values are sorted to the end.
 * @see: {@link https://tanstack.com/table/v8/docs/api/features/sorting#sorting-functions}
 */
const LAST_UNICODE_CHARACTER = '\u10FFFF';
sortingFns.basic = (rowA, rowB, columnId) => {
  const valueA = sanitizeValue(rowA.getValue(columnId));
  const valueB = sanitizeValue(rowB.getValue(columnId));
  return valueA > valueB ? 1 : valueA < valueB ? -1 : 0;
};
const sanitizeValue = (v: any) =>
  v === undefined || v === null || v === '' ? LAST_UNICODE_CHARACTER : v;

[KevinVandy]

invertSorting as a column option already exists. https://tanstack.com/table/latest/docs/guide/sorting#sorting-direction

sortUndefined as a column option already exists. https://tanstack.com/table/latest/docs/guide/sorting#sort-undefined-or-nullish-values

[KevinVandy]

This was in response to someone asking about v8 and opening up a similar PR. lol sorry for any confusion

[ghost]

@KevinVandy sortUndefined does sort undefined values, yes, but how about null values? Currently sortUndefined does not sort null values, requiring users to monkey patch the table.

Here is the code that handles sortUndefined:

table/packages/table-core/src/utils/getSortedRowModel.ts

Lines 60 to 77 in a4bd09a

	if (sortUndefined) {
	const aValue = rowA.getValue(sortEntry.id)
	const bValue = rowB.getValue(sortEntry.id)
	const aUndefined = aValue === undefined
	const bUndefined = bValue === undefined
	if (aUndefined || bUndefined) {
	if (sortUndefined === 'first') return aUndefined ? -1 : 1
	if (sortUndefined === 'last') return aUndefined ? 1 : -1
	sortInt =
	aUndefined && bUndefined
	? 0
	: aUndefined
	? sortUndefined
	: -sortUndefined
	}
	}

[KevinVandy]

Just have an accessor fallback to undefined instead of null

[ghost]

As I said, requiring users to monkey patch the table. Why did the creators decided to just adopt undefined and not null as well? And are there plans to also include null? Default for missing values when returned from DBs is null.

[KevinVandy]

Accessors allow you to control the fallback values for purposes such as this. We can change this for v9 to be sortNullish. That makes sense to me, but would technically be a breaking change. The v9 branch has been created recently.