Facets & Data Aggregation
Relevant source files
The following files were used as context for generating this wiki page:
Facets and data aggregation in Better Tables provide a sophisticated mechanism for summarizing dataset characteristics and generating server-derived columns. This system allows for real-time calculation of value distributions (facets) that respond to active filters, as well as the definition of aggregate columns like counts or sums that can be filtered and sorted just like standard database fields.
The architecture ensures that these computations are performed efficiently at the database level—primarily through correlated subqueries—while maintaining full type safety and declarative configuration through the flagship defineTable API.
Architecture & Data Flow
The system operates through a specialized interaction between the UI hooks, the core table definition, and the database adapter. Facets utilize a "self-exclusion" logic where a faceted column's own active filters are ignored to show available sibling options, while other active filters narrow the counts.
This diagram illustrates the flow from UI interactions to the generation of server-side SQL for facets and aggregates.
Sources: plans/021-filter-aware-facets.md:28-44, plans/060-derived-aggregate-columns.md:73-88
Server-Derived Aggregates
Derived columns allow developers to define fields that do not exist as static table columns but are instead computed by the database during retrieval. Common use cases include counting related records (e.g., postsCount for a user) or calculating sums across relationships.
Derived Column Specification
Aggregate columns are defined using a serializable DerivedColumnSpec. This ensures that definitions can cross the HTTP wire safely while being validated against the server-side schema.
| Property | Type | Description |
|---|---|---|
kind | 'aggregate' | Discriminator for the derived column type. |
relation | string | The relation name on the primary table to aggregate. |
fn | enum | The aggregate function: count, sum, avg, min, max. |
field | string | Required for sum/avg/min/max; identifies the target numeric field. |
Sources: plans/design/derived-columns.md:14-25, plans/060-derived-aggregate-columns.md:140-155
SQL Lowering Strategy
The Drizzle adapter lowers these specifications into correlated subqueries. This approach is preferred over GROUP BY to maintain correct pagination totals and avoid row inflation.
-- Example: t.count('posts')
(SELECT count(*) FROM posts WHERE posts.user_id = users.id) AS "postsCount"
Sources: plans/design/derived-columns.md:83-95, packages/adapters/drizzle/src/derived-aggregates.ts:1-20
Facets & Filter Awareness
Facets provide counts of distinct values within a column, adjusted for current search criteria. The system supports "Filter-Aware Facets," meaning counts update as the user interacts with the table's filters.
Self-Exclusion Logic
When calculating facets for a specific column (Column X), the system applies all active filters except those targeting Column X. This prevents a selection from immediately hiding other available options within the same category.
Sequence diagram showing how self-exclusion pruning is handled during a facet request.
Sources: plans/021-filter-aware-facets.md:46-55, packages/ui/src/hooks/use-facets.ts:10-35
Components and Hooks
useFacets: A React hook that manages the fetching and caching of faceted values. It accepts an optionalenabledflag to prevent unnecessary background requests.FacetedFilterSidebar: A UI component that renders a vertical list of filters with associated counts, typically used for multi-select categories.
| Parameter | Type | Description |
|---|---|---|
columnId | string | The ID of the column to generate facets for. |
filters | FilterState[] | The current global filter state, used for narrowed counts. |
enabled | boolean | Controls whether the facet query is actively fetching. |
Sources: packages/ui/src/hooks/use-facets.ts:5-25, packages/ui/src/components/filters/faceted-filter-sidebar.tsx:1-40
Adapter Capabilities
Not all database adapters support the full range of aggregation and faceting operations. The AdapterMeta interface allows adapters to declare their specific capabilities, which the core instance uses to provide early errors for unsupported operations.
| Capability | Scope | Description |
|---|---|---|
render | Aggregates | Ability to include the computed value in the SELECT clause. |
filter | Aggregates | Ability to use the computed value in a WHERE clause (Prisma limitation). |
sort | Aggregates | Ability to use the computed value in ORDER BY. |
countDistinct | Facets | Prevents count inflation when faceting over one-to-many joins. |
Sources: plans/design/derived-columns.md:65-75, plans/021-filter-aware-facets.md:57-65
Conclusion
Facets and Aggregations represent the bridge between raw data and actionable insights in Better Tables. By offloading complex calculations like correlated subqueries and distinct counts to the database adapter while providing a unified UI and hook layer, the system enables high-performance data exploration without the boilerplate typically associated with relational data management.