Column Filters

[amiregelz]

trafficstars

Is there any way to filter column values? a multi selection would be helpful.

[jassmith]

Are you trying to cause certain columns not to be shown, or are you trying to have a per-column "search" lets call it?

[amiregelz]

@jassmith I am trying to have a per-column search - to be able to filter the column values (one is text value, the other is tags array) based on a specific text search string.

[jassmith]

This is not a built in feature for the data grid at the moment. It is something I intend to the source package at some point but not right now due to time. It's pretty straightforward to implement yourself if you have already built your menus. You just need to have a pre-filter before you pass data to the data grid.

[amiregelz]

@jassmith Got it. Is there an example of using Menus for this use case?

[jassmith]

I dont have an example of that right now sorry.

[tb-viktor]

Our users also quite like this feature and our migration to glide data grid is a bit more bumpy due to it not being available.

I've tried to implement it via menus and a pre-filter like suggested here, but it was not as straight forward as expected. The problem is that due to filters hiding rows, row 3 could become row 1, and when editing values in it the wrong row will be changed in the source data.

This is how it looks in jspreadsheet, the library that we currently use:

@jassmith is this something that you intend to pick up in the near future?

[jassmith]

Can I see how your data source is integrated into GDG? This isn't fundamentally that hard of a hook to write.

[jassmith]

Also how many rows do you really support? The biggest problem with this hook isn't making it work, it's making it scale to millions of rows. If you only care about 10's of thousands or less, really easy.

[tb-viktor]

We do not have to handle a crazy amount of rows luckily - a few thousand in the most extreme cases.

Here is the essence of the code with some comments. We had to customize a lot of things, so I don't want to put up a wall of code here. Let me know if you are interested in anything specific:

// Table data is stored in a ref. Using state causes the table to lose focus when navigating and updating cells via keyboard
// Initialized from the externalValue and can be force-updated using an effect (after a calculation result in our product)
const data = useRef<Record<string, unknown>[]>(
	externalValue.length ? externalValue : [createEmptyRow()],
);

useEffect(() => {
	if (forceUpdateValue) {
		data.current = externalValue.length ? externalValue : [createEmptyRow()];
	}
}, [externalValue, forceUpdateValue, createEmptyRow]);

// Filters structure idea is with the column id as key and array of values that are selected by the user to filter by 
const [selectedFilters, setSelectedFilters] = useState<Record<string, any[]>>({});

// externalValue is used for the unfiltered list of values, assuming `data` would be filtered in this implementation
const onHeaderMenuClick = useCallback(
	(col: number, screenPosition: Rectangle) => {
		const columnId = columns[col].id;
		
		if (columnId) {
			const columnValues = externalValue.map((row) => row[columnId]);
			const uniqueValues = [...new Set(columnValues)];

			setFiltersMenu({
			  visible: true,
			  ...screenPosition,
			  columnId,
			  options: uniqueValues,
			}); // tells the MUI Menu where and what to show
		}
	},
	[columns, externalValue],
);

<DataEditor
	// ...
	columns={columns}
	rows={data.current.length}
	getCellContent={getCellContent} // reads values from data, corrects invalid ones based on column type and returns them
	onCellEdited={onCellEdited}
	onPaste={onPaste}
	onHeaderMenuClick={onHeaderMenuClick}
	// ...
/>

// MUI Menu for selecting filters, selecting/unselecting options. Modifies the `selectedFilters`.

The part connecting the selected filters and data is missing, that is where the problems start. I've had two ideas how to tackle it:

1. Add a dummy value to each row to act as a unique row id, and update row values based on that key. Downside is constantly having to filter this value out of the data and not accidentally send it to the backend in jobs. Easy to overlook.
2. Create a "mapping" between real data and filtered data that stores which real data row id is connected with which filtered row id. This was pretty messy and error-prone.

It felt like this was better handled internally than implementing an overcomplicated and likely flawed solution on our end.