Reading ClickHouse live, filter to SQL: JavaScript Data Grid Demo

<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@toclocoinc/lattice-grid@1.76.0/lattice-grid.min.css">
<script src="https://cdn.jsdelivr.net/npm/@toclocoinc/lattice-grid@1.76.0/lattice-grid.min.js"></script>

<pre id="sql" style="margin-bottom:12px"></pre>
<div id="tiles" style="margin-bottom:12px"></div>
<div id="grid" style="height:520px"></div>

<script type="module">
  import { createKPI } from '@toclocoinc/lattice-grid/modules/kpi';

  const sqlEl = document.getElementById('sql');
  const adapter = LatticeGrid.clickhouseAdapter({
    url: 'https://play.clickhouse.com',
    table: 'uk_price_paid',
    headers: { 'X-ClickHouse-User': 'play' },   // ClickHouse's own read-only playground user
  });
  const source = LatticeGrid.createPushdownSource({ adapter, compute: LatticeGrid, pageSize: 100 });

  const kpi = createKPI(document.getElementById('tiles'), {
    rowKey: 'town',
    columns: 2,
    tiles: [
      { id: 'avgPrice', label: 'Average price · busiest town', aggregation: 'avg', field: 'avg_price', format: { style: 'currency', currency: 'GBP', notation: 'compact' } },
      { id: 'sales', label: 'Sales counted there', aggregation: 'sum', field: 'n' },
    ],
  });

  let currentFilters = null;
  const refreshKpi = async () => {
    const result = await source.aggregate(
      { filters: currentFilters, sort: [], range: null, groupBy: ['town'] },
      [{ id: 'avg_price', col: 'price', fn: 'avg' }, { id: 'n', col: 'price', fn: 'count' }],
    );
    const top = (result.groups ?? [])
      .filter((g) => g.keys?.[0])
      .sort((a, b) => (b.values.n ?? 0) - (a.values.n ?? 0))[0];
    if (top) kpi.rows.apply({ add: [{ town: String(top.keys[0]), avg_price: top.values.avg_price ?? 0, n: top.values.n ?? 0 }] });
  };

  // Every filter, sort and page becomes one statement; sqlFor(query) shows it
  // without running it twice, and the grouped average is a second statement
  // ClickHouse itself answers over the whole matching set, not just the page.
  const inner = adapter.execute.bind(adapter);
  adapter.execute = async (query, request) => {
    const result = await inner(query, request);
    currentFilters = query.filters ?? null;
    // uk_price_paid carries no column that uniquely identifies a row; stamp
    // one from each page's own offset, enough for the grid's own diffing
    // since nothing here is written back.
    const offset = query.range?.offset ?? 0;
    result.rows = (result.rows ?? []).map((r, i) => ({ ...r, __k: offset + '-' + i }));
    const f = adapter.sqlFor(query);
    sqlEl.textContent = f.sql + (f.params?.length ? '\n-- bound: ' + JSON.stringify(f.params) : '');
    void refreshKpi();
    return result;
  };

  const grid = LatticeGrid.createGrid(document.getElementById('grid'), {
    rowKey: '__k',
    toolPanel: { side: 'left', panels: ['filters', 'columns'] },
    columns: [
      { field: 'date', title: 'Date', type: 'dateString' },
      { field: 'price', title: 'Price', type: 'number', format: { style: 'currency', currency: 'GBP', notation: 'compact' }, filter: { type: 'number' } },
      { field: 'type', title: 'Type', filter: { type: 'set' } },
      { field: 'town', title: 'Town', filter: { type: 'set' } },
      { field: 'county', title: 'County', filter: { type: 'set' } },
    ],
    source,
  });
</script>