How to sort column with English, Japanese entries and null values using custom sorting algorithm in MUI Data Grid

Question

I have an MUI data grid. I want to create a custom sorting algorithm for columns which can override the default options offered by MUI. My data fields have data in English, Japanese as well as empty/null values. My expected output is to display columns with following sort order for ascending -

1. A-Z English
2. Remaining Null and Japanese entries in any order

Descending

1. Z-A
2. Remaining Null and Japanese entries in any order

My code is as follows

import * as React from "react";
import { DataGrid } from "@material-ui/data-grid";

const handleCellClick = (param, event) => {
  console.log(param);
  console.log(event);
  if (param.colIndex === 2) {
    event.stopPropagation();
  }
};

const handleRowClick = (param, event) => {
  console.log("Row:");
  console.log(param);
  console.log(event);
};

const columns = [
  {
    field: "id",
    headerName: "ID",
    width: 70
  },
  {
    field: "firstName",
    headerName: "First Name",
    width: 130,
    renderCell: (cellValues) => {
      return (
        <div
          style={{
            color: "blue",
            fontSize: 18,
            width: "100%",
            textAlign: "right"
          }}
        >
          {cellValues.value}
        </div>
      );
    }
  },
  { field: "lastName", headerName: "Last Name", width: 130 },
  {
    field: "age",
    headerName: "Age",
    type: "number",
    //width: 90,
    minWidth: 90,
    flex: 1
    //align: "left"
  },
  {
    field: "fullName",
    headerName: "Full name",
    description: "This column has a value getter and is not sortable.",
    sortable: false,
    minWidth: 160,
    flex: 2,
    //width: 160,
    valueGetter: (params) => {
      return `${params.getValue(params.id, "firstName") || ""} ${
        params.getValue(params.id, "lastName") || ""
      }`;
    }
  }
];

const rows = [
  { id: 1, lastName: "Snow", firstName: "Jon", age: 35 },
  { id: 2, lastName: "Lannister", firstName: "Amy", age: 42 },
  {
    id: 3,
    lastName: "IGOTAREALL",
    firstName: "Jaime",
    age: 45
  },
  { id: 4, lastName: "Stark", firstName: "Arya", age: 16 },
  { id: 5, lastName: "Targaryen", firstName: "Daenerys", age: 12 },
  { id: 6, lastName: "Melisandre", firstName: "Jane", age: 15 },
  { id: 7, lastName: "榎原", firstName: "しよう", age: 44 },
  { id: 8, lastName: "田中", firstName: "田畑", age: 36 },
  { id: 9, lastName: "瀬奈", firstName: "健治", age: 65 }
];

export default function DataGridDemo() {
  

  return (
    <div style={{ height: 500, width: "100%" }}>
      <DataGrid
        rowHeight={50}
        className={"hello"}
        rows={rows}
        columns={columns}
        pageSize={10}
        checkboxSelection
        onCellClick={handleCellClick}
        onRowClick={handleRowClick}
      />
    </div>
  );
}

Answer 1

It looks like it would be something like the following (sandbox). You can customize the isEnglish() and isNull() utility functions to suit your needs, and in the case of the Full name column you could build the full ${firstName} ${lastName} string for comparison if you wanted to.

const columns = [
  {
    field: "lastName",
    headerName: "Last Name",
    width: 130,
    sortComparator: (v1, v2) => {
      const isEnglish = (s) => /^[A-Za-z0-9]*$/.test(s);
      const isNull = (s) => (s == undefined || s.trim() === '');

      return (isNull(v1) || !isEnglish(v1)) - (isNull(v2) || !isEnglish(v2)) || v1.localeCompare(v2)
    }
  },
  {
    field: "fullName",
    headerName: "Full name",
    minWidth: 160,
    flex: 2,
    //width: 160,
    valueGetter: (params) => {
      return `${params.getValue(params.id, "firstName") || ""} ${params.getValue(params.id, "lastName") || ""
        }`;
    },
    sortComparator: (v1, v2, param1, param2) => {
      const isEnglish = (s) => /^[A-Za-z0-9]*$/.test(s);
      const isNull = (s) => s == undefined || s.trim() === '';

      const a = param1.api.getCellValue(param1.id, 'firstName');
      const b = param2.api.getCellValue(param2.id, 'firstName');

      return (isNull(a) || !isEnglish(a)) - (isNull(b) || !isEnglish(b)) || a.localeCompare(b)
    }
  }
]

Edit

The data-grid assumes a symmetric sort and so just reverses the logic for 'desc'. To account for this you need to intervene by detecting the sort direction and manually reversing the initial value in the OR statement to continue to sort to the bottom. You can access the current state through either of the param arguments passed to the comparator:

const isDesc = param1.api.state.sorting.sortModel[0].sort === "desc";

You can then use this boolean to adjust the first OR value:

((isNull(v1) || !isEnglish(v1)) - (isNull(v2) || !isEnglish(v2))) * (isDesc ? -1 : 1) 
|| v1.localeCompare(v2)

The complete solution then might be something like the following:

  {
    field: "lastName",
    headerName: "Last Name",
    width: 130,
    sortComparator: (v1, v2, param1) => {
      const isEnglish = (s) => /^[A-Za-z0-9]*$/.test(s);
      const isNull = (s) => s == undefined || s.trim() === "";
      const shouldSortToEnd = (s) => isNull(s) || !isEnglish(s);

      const isDesc = param1.api.state.sorting.sortModel[0].sort === "desc";

      return (
        (shouldSortToEnd(v1) - shouldSortToEnd(v2)) * (isDesc ? -1 : 1) ||
        v1.localeCompare(v2)
      );
    }
  },

---

This is based on a sort() using OR short-circuit:

const rows = [
  { id: 1, lastName: "Snow", firstName: "Jon", age: 35 },
  { id: 2, lastName: "Lannister", firstName: "Amy", age: 42 },
  { id: 3, lastName: "IGOTAREALL", firstName: "Jaime", age: 45 },
  { id: 4, lastName: "Stark", firstName: "Arya", age: 16 },
  { id: 5, lastName: "Targaryen", firstName: "Daenerys", age: 12 },
  { id: 6, lastName: null, firstName: "Jane", age: 15 },
  { id: 7, lastName: "榎原", firstName: "しよう", age: 44 },
  { id: 8, lastName: "田中", firstName: "田畑", age: 36 },
  { id: 9, lastName: "瀬奈", firstName: "健治", age: 65 }
];

rows.sort((a, b) => {
  const isEnglish = (s) => /^[A-Za-z0-9]*$/.test(s);
  const isNull = (s) => s == undefined || s === '';

  a = a.lastName;
  b = b.lastName;

  return (isNull(a) || !isEnglish(a)) - (isNull(b) || !isEnglish(b)) || a.localeCompare(b);
});

console.log(rows.map(({ lastName }) => lastName).join(', '));

see:

• How to sort an array of objects by multiple fields?
• Javascript find if english alphabets only