JavaScript Data Grid Filtering

Developer guide › Sorting, filtering and find › JavaScript Data Grid Filtering

Filtering

There are two filters and they are separate on purpose. The quick filter is one string matched across every readable column. The filter set is a structured condition tree.

grid.filters.quick('singapore');
grid.filters.quick('');          // clear

grid.filters.set({
  op: 'and',
  conditions: [
    { col: 'region', op: 'in', value: ['EMEA', 'APAC'] },
    { col: 'utilisation', op: 'gt', value: 0.9 },
    { op: 'or', conditions: [
      { col: 'slaBreached', op: 'eq', value: true },
      { col: 'margin', op: 'lt', value: 0 },
    ]},
  ],
});

Operators are per family: text has contains, startsWith, matches; numbers and dates have between, gt, lte; multi-value columns have containsAny, containsAll, containsNone; a geometry column has withinBbox, withinPolygon, intersects and withinDistance (with the geometry module). blank and notBlank work everywhere.

Where a filter's value list comes from

A set filter lists a column's values to tick. A condition filter whose type opts in with valuePicker (the built-in text filter) offers the same kind of list for its Equals, Not equals and Is-one-of operators, ticking several meaning in/ notIn rather than a repeated eq. Where either list comes from depends on the source, and on a large source it matters: the list is built the moment the popup opens.

• Memory source (rows): the column's dictionary - every value the grid holds, sorted alphabetically, with (Blanks) first, as in the engine list. Nothing is fetched.
• Pushdown source (createPushdownSource): the engine. Opening the popup sends one GROUP BY on the column with a count through the adapter's executeGroupedAggregates() - DuckDB, ClickHouse and Elasticsearch all answer it - and the browser reads no row to build the list. The popup draws at once, says Loading values…, and fills when the answer lands; closing it aborts the request. The engine's 200 most frequent values are listed, each with its count, collated A to Z in the grid's locale like the memory list but with a blank first; a two-option toggle (A to Z, Count) in the popup switches to most frequent first and holds while the popup is open. When the column has more than 200, the popup says so (Top 200 of 8,412 values) and typing in its search box asks the engine again with a contains condition on the column, so a value past the first 200 is still one search away. The list covers the rows kept by every other active condition - the grid's filter tree with this column's own conditions removed, plus the quick filter - combined with the search text. Those predicates are pushed exactly as a row query's are, so on a Parquet source the engine reads only the row groups the filters keep, and each count is the count the grid shows after that value is picked. The column's own conditions are left out so a selection can still be widened. This differs on purpose from the memory source, whose dictionary lists every value the grid holds regardless of filters: a dictionary costs nothing to read, while an unscoped engine list would read the whole file to show counts the grid will never display. The tool panel's Filters section uses the same scoped list, and so does a condition filter's own picker - minus the count per value and the A-to-Z/Count toggle, which are the set filter's own chrome.
• A pushdown adapter that cannot answer a distinct-values query (no executeGroupedAggregates(), no count, or a column it cannot group by): the popup offers the text condition editor instead and says why, and the console carries filter:values-unsupported naming the column and the reason.
• A column with a lookup, or a filter.props.values list: that list, on every source. It is the fastest route and never touches the data.

Filters your application owns: where

A condition tree can only test what is in a column. Plenty of real filters cannot be written that way - whether this user may see the row, whether you hold an exchange rate for its currency, whether it came back from your last search call. Those go in as named predicates, and they compose with everything above.

grid.filters.where('visibleToMe', row => row.owner === me, { pinned: true });
grid.filters.where('rateKnown', row => rates.has(row.ccy), { deps: ['ccy'] });

grid.filters.where();              // ['visibleToMe', 'rateKnown']
grid.filters.where('rateKnown', null);   // remove just that one
grid.filters.reapply('rateKnown');       // the rate table arrived late

Three options shape one. deps names the columns the predicate reads, exactly as value.deps does for a computed column: the verdict is then cached per row and re-run when one of those columns changes on that row, not when an unrelated one does. Leave it off and the predicate is assumed to read the whole row, so it runs every pass and can never be stale. pinned makes a predicate survive filters.clear(), which is what you want for permissions and tenant scoping and not much else. condition gives the predicate a declarative twin that is pushed to the source while the function stays as the residual, so a pushdown engine narrows the fetch instead of your code filtering a page.

grid.filters.where('tenant', row => row.tenant_id === 7, {
  pinned: true,
  condition: { col: 'tenant_id', op: 'eq', value: 7 },
});
// over duckdbAdapter: grouping and pivot run in the engine as
// ... WHERE "tenant_id" = ? GROUP BY ..., value bound; a where with no condition
// still groups in the browser, and lastPlan().groupReason says why