Allow users to sort based on multiple columns

[RFSH]

Users can sort the displayed results by clicking on the column header. When they click on a different column, we're just replacing the sort criteria to be based on the second column. We want to explore allowing users to sort based on multiple columns in this issue. Both ermrestjs and ermrest support sorting based on multiple columns, and we just need to think about the UI/UX.

The following are different phases involved in this task:

Phase 1

We can implement this like how Google Sheets is doing it. We could add a separate button to the right of the "Displaying ..." text called "Sort by" (or some other labels). Clicking on this button would open a popup.

Similar to Google Sheets, in the popup we should allow users to select however many sort columns that they want. We also should allow them to reorder these columns. That's why we're going to use the same react-beautiful-dnd plugin that we're using for array inputs (with the same "+" and "-" icon to add or remove items).

Each item in this draggable list should have the following elements:

• A dropdown to select the column. For now, use the reference.columns to populate this dropdown. Make sure to only allow the ones that are .sortable. So (reference.columns.filter((col) => col.sortable).
• A radio button to toggle between ascending and descending.

After changing the sort columns, users would have to click on the "submit" button similar to how our other popups work.

The popup should have three buttons:

• Reset: Sort based on the default sort (#2153). It should call the same function to sort, but with an empty array.
• Cancel: to cancel changing the sort modifier. It should just close the modal.
• Apply: to apply the selected sorts. It should call the sort function and close the modal.

Technical details

• Use "react-beautiful-dnd": "^13.1.1" for the draggable list.
• The button should be added to table-header.tsx component.

• You can find an example of how we're calling sort here. So let's say users pick col1 visible column object with "ascending" and col2 visible column with "descending". Calling sort would be something like this

  const ref = reference.sort([{'column': col1.name, 'descending': false}, {'column': col2.name, 'descending': true}]);
  
  update({ updateResult: true }, { reference: ref }, { cause: LogReloadCauses.SORT });

  (In our meeting I talked about using col.sortColumns that's not the case. You should just pass the objects like the example above)

• For reset sort, like I mentioned above, pass empty array, so const ref = reference.sort([]);

Phase 2 (customization)

When we're done with the changes described above, we want to make sure this feature is customizable. We need to come up with annotations to allow this, but the following are the customization that we could think of:

• Given the performance implications, this feature might not be desirable in some deployments or tables. So we should allow data-modelers to turn this feature off for a specific catalog, schema, or table.
• Data-modelers should be able to define the list of choices. The choices might not necessarily be just one column, and might be a combination. So we most probably want to allow them to define a similar object as column_order for each item in the list. This would allow us to support sorting based on hidden columns (#1488).
• We should limit the number of different sort columns that users can apply.

Phase 3 (Sort indicator)

Our current UI only supports showing sort indicator for a single column. Even if the sort modifier has multiple columns, we're just showing indicator for the first one. We should improve this. This requires more thought

[PoornimaVKrishnan]

@RFSH I have explored few options more to compare sort range feature similar to google sheets. 1) Airtable This is a better option where we can show the sort criteria as a dropdown instead of a Modal. We have a Sort by label on the top and dropdowns for columns and sort order. We also have a search capability in the column dropdown.

https://github.com/informatics-isi-edu/chaise/assets/133336338/bf9eb5ee-3007-41c7-84ce-88fc8bdc2396

2) Zoho Sheets This option is very similar to what we have currently. Attaching screenshot for reference.

3) Microsoft Excel This opens up a modal for customized sort with dropdowns for columns and sort order.

https://github.com/informatics-isi-edu/chaise/assets/133336338/573a24f6-9205-46b4-a656-351fd0d8f03b

Overall, Airtable option is more feasible as we have pretty much everything what we need in a neat and clean way. Open to suggestions.

[RFSH]

We discussed the previous comment. In summary, we think Airtable implementation is the best, and we will borrow that mostly. A more detailed summary of what we talked about is as follows:

• Modal vs. dropdown doesn't make any difference on the recordset page, but on the record page, dropdown is easier to understand. A modal for the whole page would be confusing on the record page when it's just changing the sort of one of the related tables.
• We should have the same "Sort by" header with an information icon. This is similar to the icon in range pickers, where hovering over it will explain the feature to users.
• We don't need the "sort by" or "then by" labels.
• The ascending and descending options should be a dropdown. But we didn't like the labels that Airtable is using. Our labels should be based on the column type (In the following, instead of to we should use the fa-solid fa-arrow-right icon).
  • For date and timestamp: Earliest to latest and Latest to earliest.
  • For numbers: Min to max and Max to min
  • For boolean: false to true and true to false (we should use the actual presentation value instead of true/false. You can use the formatBoolean function in input-utils.ts for this).
  • Other types: A to Z and Z to A.
• Unlike Airtable, we want the drag icon to be on the left. We should continue using the trash icon as well.
• Use our secondary style of buttons, "Add another sort" (so with a border).
• Use a separator instead of a different background to distinguish the last row in the dropdown. Similar to how we're doing this for the modals.
• For the button on the bottom right, let's use our own style and order of buttons.

[PoornimaVKrishnan]

@RFSH Things pending for sort task:

• dropdown menu for column is hiding below the overlay body
• deeplink sort functionality - take the sort criteria from url and apply to the tooltip
• Ellipsis tooltip for column dropdown
• code cleanup
• check for Boolean type column
• tooltip going beyond screen in the first render