Filter a database grid by picking values, 1.113

Filtering a column by a category you cannot see is guesswork. You know the rows include some kind of bearing, so you open the filter, choose Equals and get a bare text box. Is it “Bearing 6204” or “bearing-6204”? Is there a “Bearing 6205” beside it? The only way to find out is to type, miss, and type again.

The example above is a grid over 100,000 rows in a Parquet file, read by DuckDB in the tab. Open the Category filter, choose Equals, and a list appears: the eight categories the database holds. Type a few letters to narrow it, tick one, and the rows and the count tile follow. Nobody had to know a spelling.

Why it was a text box

A grid over an ordinary array can scan its rows and list a column’s distinct values. A grid over a database cannot: the rows are not in the page, and loading them all to find out what they contain would defeat the point of the database. So the Equals picker had nothing to offer and fell back to free text.

The set filter already solved this by asking the database. Now the condition filter does the same.

Equals, Not equals and Is one of

The three pickers that take a value, Equals, Not equals and Is one of, now ask the database for the column’s values and show them as a list. Four things about that list matter:

• It comes from the database. The grid sends a distinct query to the source instead of scanning rows it never held.
• It is capped and searchable. A column with millions of distinct values does not flood the popup. Type, and the search narrows the list.
• It is scoped. The list offers only the values in the rows the grid’s other filters keep. Filter the plant first, and the categories you are offered are the ones that plant has.
• Picking applies SQL. Ticking a value applies the condition as a WHERE clause the database answers, and only the visible page comes back.

const grid = createGrid(el, {
  source: createPushdownSource({ adapter: duckdbAdapter({ connection, from }), compute: LG }),
  columns: [{ field: 'product', title: 'Category', filter: { type: 'text' } }],
});

There is nothing to switch on. A column with filter type text over a database source gets the list.

Memory-backed grids stay as they were

A grid over rows it already holds is unchanged. It already knew the values, and it keeps its behaviour exactly.

Where this came from

The public data lake demo reads 81 million places by byte range. At that size, nobody remembers the spelling of a category, and a text box is no help. Listing the values from the database is what made that grid pleasant to filter.

Open the example, choose Equals on Category and tick a value. The DuckDB guide has the adapter, and the changelog has the rest of 1.113.