react-datatable

Use this guide when buildBackendQueryPlan() is already returning the right plan and the remaining job is to execute it.

The right mental model is:

one shared execution setup
one split into two execution strategies
- flat_window
- grouped_window
one final OnlineQueryResponse<TData> contract

OnlineQueryResponse<TData> is the server payload the table reads after each request. It tells the table which rows to render, how many matching data rows exist, whether more rows can load, and, in grouped mode, what grouped structure those rows belong to.

Plan the query

Your backend should have one entrypoint that does three things before execution branches:

normalize request aliases
build the backend query plan
choose the execution strategy from plan.kind

function buildTableQueryPlan(input: OnlineQueryInput) {
  const query = buildBackendQueryState({
    filters: input.filters,
    sorting: input.sorting,
    globalFilter: input.globalFilter,
    filterMode: "AND",
    grouping: input.grouping,
  })

  return buildBackendQueryPlan({
    navigationMode: input.mode,
    limit: input.limit,
    offset: input.offset,
    query,
    columns: orderServerColumns,
    tieBreakers: [
      { columnId: "createdAt", expression: { kind: "column", columnId: "created_at" } },
      { columnId: "id", expression: { kind: "column", columnId: "id" } },
    ],
  })
}

export async function executeTableQuery(input: OnlineQueryInput) {
  const plan = buildTableQueryPlan(input)

  if (plan.kind === "grouped_window") {
    return executeGroupedQuery(plan)
  }

  return executeFlatQuery(plan)
}

In the showcase app, this same handoff lives in site/src/db/showcase-online-query.ts with showcase-specific helper names. The important part is the shape of the pipeline, not the demo app naming.

Implement the flat query

Use the flat path when the planner returns kind: "flat_window".

1. Compile planner output into real SQL conditions

The flat executor needs two compiled pieces:

where
orderBy

That compilation step maps planner expressions onto your actual schema.

2. Fetch the requested window

const compiled = compileFlatQueryPlan(plan)

return db
  .select()
  .from(orders)
  .where(compiled.where)
  .orderBy(...compiled.orderBy)
  .limit(plan.limit + 1)
  .offset(plan.offset)

The limit + 1 read is there only to determine hasMore safely.

3. Count the full filtered result

The same executor runs a separate count query against the same filtered scope:

const [result] = await db
  .select({ value: count() })
  .from(orders)
  .where(compiled.where)

That produces totalDataRows.

4. Shape the final response

The flat executor trims the overflow row, maps database rows into domain rows, and returns only data rows:

return {
  rows: visibleRows.map((item) => ({
    type: "data",
    rowId: item.id,
    item,
    groupPath: [],
  })),
  totalDataRows,
  totalRenderedRows: totalDataRows,
  hasMore,
}

That is the complete flat path:

compile filters, search, and sorting
fetch one ordered window
count the filtered dataset
return one flat OnlineQueryResponse<TData>

Implement the grouped query

Use the grouped path when the planner returns kind: "grouped_window".

Grouped execution is still one server path, but it has extra responsibilities. The response must describe both the matching data rows and the grouped structure around them.

1. Reuse the filtered scope

The grouped path starts from the same filtered dataset as the flat path.

function buildFilteredBaseCte(plan: GroupedWindowQueryPlan): SQL {
  const where = compileFilteredBaseWhere(plan)

  return sql`with filtered_base as (
    select *
    from ${orders}
    ${where ? sql`where ${where}` : sql``}
  )`
}

That keeps grouped counts and grouped rows tied to the same filtered dataset.

2. Build grouped summary queries

The grouped executor needs summary queries before it can shape the response.

const countQuery = ...
const topGroupsQuery = ...
const subgroupsQuery = ...

Those queries produce:

totalDataRows
group counts
subgroup counts
the data needed for OnlineGroupingSummary
the data needed for totalRenderedRows

3. Handle `showEmptyGroups`

showEmptyGroups belongs in the grouped summary layer.

If it is false, return only groups that actually appear in the filtered dataset.

If it is true, the executor also needs a full domain for that grouped column so it can emit zero-count groups.

For example, a status column might use a known domain like this:

const statusDomain = ["active", "paused", "inactive"]

If your product needs empty groups for arbitrary SQL-backed dimensions, you need your own domain source for those values.

4. Fetch the grouped data window

The grouped path still needs a real row query.

return db
  .select()
  .from(orders)
  .where(compiled.where)
  .orderBy(...buildGroupedQueryOrderBy(plan))
  .limit(plan.limit)
  .offset(plan.offset)

That ordering is what keeps grouped infinite windows stable when a request starts in the middle of a group.

5. Shape group headers and data rows together

Once the executor has both the page rows and the grouped summary, it builds the final render rows.

That output may include:

top-level group-header rows
subgroup group-header rows
data rows with the right groupPath

So grouped execution is not just “fetch rows and let the client add headers later.” The server already has to return grouped render structure.

6. Return the grouped response

return {
  rows: buildGroupedRows(plan, items, summary),
  totalDataRows: summary.totalDataRows,
  totalRenderedRows: summary.totalRenderedRows,
  hasMore: plan.offset + plan.limit < summary.totalDataRows,
  grouping: summary.grouping,
}

That is the complete grouped path:

reuse the filtered scope
compute group summaries
apply showEmptyGroups in the summary layer
fetch the ordered data window
build group headers plus data rows
return grouped totals and grouping metadata

facets are not a separate execution mode. They are extra response data.

If your table shows backend-aware filter counts, compute them from the same filtered scope and attach them to the final response.

For example, if your filter UI needs status counts, run a grouped aggregate against the same filtered base query:

async function buildFacets(plan: FlatWindowQueryPlan | GroupedWindowQueryPlan) {
  const where = compileFilteredBaseWhere(plan)

  const statusFacetRows = await db
    .select({
      value: orders.status,
      count: count(),
    })
    .from(orders)
    .where(where)
    .groupBy(orders.status)
    .orderBy(orders.status)

  return {
    status: statusFacetRows.map((row) => ({
      value: row.value,
      count: row.count,
    })),
  }
}

Then attach those facet counts to the same response that returns rows:

const facets = await buildFacets(plan)

return {
  rows,
  totalDataRows,
  totalRenderedRows,
  hasMore,
  grouping,
  facets,
}

The important rule is simple: facets must be computed from the same tenant scope and filtered dataset as the rows you returned.

Why grouped queries need a separate executor

The codebase has one canonical pipeline, but two executor implementations because the contracts differ:

flat mode returns only ordered data rows plus a count
grouped mode returns ordered data rows, structural headers, grouped totals, and rendered-row counts

So the split is not architectural drift. It exists because flat_window and grouped_window need different query and response-shaping logic.

In the showcase app, those responsibilities currently live in files such as:

site/src/db/showcase-flat-executor.ts
site/src/db/showcase-grouped-summary.ts
site/src/db/showcase-grouped-executor.ts
site/src/db/showcase-online-query.ts

Verify the production path

Before you call execution done, confirm that your backend can:

compile planner expressions into real schema expressions
keep ordering deterministic with tie-breakers
count the same filtered scope it renders
return totalRenderedRows correctly in grouped mode
implement showEmptyGroups from a real domain source when needed
compute facets from the same scoped dataset when the UI needs them

Server query execution