# useDocyrusPivotFilter
URL: /docs/web/hooks/use-docyrus-pivot-filter

Wire a Docyrus data source to a `<PivotFilter>` strip — auto-detects the field type, runs a pivot/aggregate query, transforms results into pill items, and emits filter rules ready to merge into a grid query.

## Installation

```bash
pnpm dlx @docyrus/cli add @docyrus/hooks-use-docyrus-pivot-filter
```

**Dependencies:**
- [@docyrus/app-utils](https://www.npmjs.com/package/@docyrus/app-utils)
- [@docyrus/api-client](https://www.npmjs.com/package/@docyrus/api-client)
- [@tanstack/react-query](https://tanstack.com/query/latest)
- [date-fns](https://date-fns.org)

This hook is distributed as source. It expects an authenticated `RestApiClient` from `@docyrus/api-client` and a `QueryClientProvider` from `@tanstack/react-query` somewhere above your component tree.

## Overview

`useDocyrusPivotFilter` is the Docyrus-backed companion to [`PivotFilter`](/docs/web/components/pivot-filter). It does for the pivot filter what [`useDocyrusKanban`](/docs/web/hooks/use-docyrus-kanban) does for kanban boards:

- loads the data source metadata (`expand=enums`)
- auto-detects the field strategy from the field's `type`
- builds a native Docyrus pivot query (`columns: '<field>(id, name, icon, color)'` for relations, `columns: '<bucket>@<field>'` for dates) — no `pivot.matrix`, no alias quirks
- transforms the API response into ready-to-render `PivotFilterItem`s
- generates date buckets for date / dateTime fields
- enriches list pills with `icon` / `color` directly from the join (no FE lookup for enums)
- exposes a `filterRule` matching the active selection — feed it back into your data grid filter
- provides a settings popover content node ready to slot into `<PivotFilter>`

For multi-pivot pages (e.g. "filter by status AND priority"), reach for [`<DocyrusPivotFilterGroup>`](#docyruspivotfiltergroup) below — it stacks N strips and handles bidirectional cross-filtering for free.

**If your page already uses [`useDocyrusDataGrid`](/docs/web/hooks/use-docyrus-data-grid),** prefer its built-in `pivotFilters` option — the grid hook renders the strips above its toolbar automatically, AND-merges selections into the items query, and feeds the grid's own filter chain back into the pivots as `activeFilters`. No glue required:

```tsx
const grid = useDocyrusDataGrid({
  client,
  appSlug: 'base',
  dataSourceSlug: 'task',
  pivotFilters: [
    { fieldSlug: 'status' },
    { fieldSlug: 'priority' }
  ]
});

// grid.toolbar already contains the pivot strips on top.
```

## Supported field types

The hook reads `field.type` and picks a strategy:

| Field type | Strategy | Pill content | Filter rule emitted |
|-----------|----------|--------------|---------------------|
| `field-select`, `field-status`, `field-radioGroup` | `list` | `id` + `name` + `icon` + `color` from joined `tenant_enum` | `{ field, operator: '=', value: id }` |
| `field-relation` | `list` | `id` + `name` from related data source (icon / color silently dropped) | `{ field, operator: '=', value: id }` |
| `field-userSelect` | `user` | avatar (from caller's user list) + name from joined `tenant_user` | `{ field, operator: '=', value: userId }` |
| `field-date` | `date` (date-only) | bucket label + range | `{ operator: 'between', value: [start+00:00, end+00:00] }` — wall-clock dates pinned to UTC |
| `field-dateTime` | `date` (with time) | bucket label + range | `{ operator: 'between', value: [startLocalISO, endLocalISO] }` — preserves the user's local timezone offset |
| `field-multiSelect`, `field-tagSelect`, `field-userMultiSelect` | `unsupported` | — | none (the backend's `applyPivot` currently rejects these as axis fields, so the query is skipped) |

Empty (null) values are surfaced as a synthetic `EMPTY` pill that emits `{ operator: 'empty', value: null }`.

### `field-date` vs `field-dateTime` timezone handling

A `field-date` represents a calendar day with no time component. When the user picks the "13 May" bucket in Istanbul (UTC+3), they mean *the calendar day 2026-05-13*, not "2026-05-13T00:00:00+03:00" (which is 2026-05-12T21:00:00Z in UTC and could silently shift the filter to the previous day on a UTC backend). The hook handles this by **stamping date-only bucket bounds as `+00:00`** — preserving the wall-clock numbers (`2026-05-13T00:00:00+00:00`).

For `field-dateTime`, the local offset is preserved (`2026-05-13T00:00:00+03:00`), matching the way Docyrus stores datetime values.

This matches the Vue `KvPivotFilter` behavior verbatim.

## Usage

### Status filter above a grid

```tsx
'use client';

import { useState } from 'react';

import { useDocyrusAuth } from '@docyrus/signin';

import { PivotFilter } from '@docyrus/ui/components/pivot-filter';
import {
  useDocyrusDataGrid
} from '@docyrus/ui/library/hooks/use-docyrus-data-grid';
import {
  useDocyrusPivotFilter
} from '@docyrus/ui/library/hooks/use-docyrus-pivot-filter';

export function OrderListWithStatusFilter() {
  const { client } = useDocyrusAuth();

  if (!client) return null;

  const grid = useDocyrusDataGrid({
    client,
    appSlug: 'sales',
    dataSourceSlug: 'order'
  });

  const pivot = useDocyrusPivotFilter({
    client,
    appSlug: 'sales',
    dataSourceSlug: 'order',
    fieldSlug: 'status',
    defaultFilters: grid.resolvedListParams.filters
  });

  return (
    <div className="space-y-3">
      
      {grid.toolbar}
      {grid.table}
    </div>
  );
}
```

`pivot.filterRule` carries the rule the grid needs to apply — merge it into your grid's filter to actually narrow the rows.

### Date pivot (days of week)

```tsx
const pivot = useDocyrusPivotFilter({
  client,
  appSlug: 'support',
  dataSourceSlug: 'ticket',
  fieldSlug: 'created_at',
  defaultDateBucket: 'days_of_week'
});

return (
  <>
    
    {/* The settings popover lets the user switch to days_of_month, months_of_year, etc. */}
  </>
);
```

The hook always renders the date strip as **Before · [buckets] · Upcoming · Not Set**, so users can grab the long tail outside the current window without losing the count.

### User filter (avatars)

```tsx
const pivot = useDocyrusPivotFilter({
  client,
  appSlug: 'project',
  dataSourceSlug: 'task',
  fieldSlug: 'assignee'
});
```

For `userMultiSelect` the hook walks each user array and re-sums the aggregate per-user in the FE, so a record assigned to three users contributes the same stat to all three pills.

### Custom calculation (sum / avg)

The default aggregate is `count` of `id`. To pivot by sum of an amount field:

```tsx
const pivot = useDocyrusPivotFilter({
  client,
  appSlug: 'sales',
  dataSourceSlug: 'invoice',
  fieldSlug: 'status',
  calculation: { func: 'sum', field: 'amount' }
});
```

The settings popover surfaces all numeric / money / duration fields and lets the user pick the function (`sum`, `avg`, `min`, `max`). Hide the popover with `disableSettings: true` when you want to lock the calculation.

### Feeding selection back to a grid

Combine `filterRule` with the grid's `defaultFilters` / `filters`:

```tsx
const grid = useDocyrusDataGrid({
  client,
  appSlug: 'sales',
  dataSourceSlug: 'invoice',
  filters: pivot.filterRule
    ? { combinator: 'and', rules: pivot.filterRule }
    : undefined
});
```

When the user resets to the "All" pill, `filterRule` flips to `null` and the grid sees no filter.

## Query payload

The hook uses Docyrus' native pivot syntax — `columns: '<field>(...)'` for relation joins or `columns: '<bucket>@<field>'` for date buckets. Combined with `calculations`, the backend auto-joins the related table and auto-groups by the joined keys. No `pivot.matrix` is involved.

```jsonc
// List strategy (field-select / field-status / field-radioGroup / field-relation)
{
  "columns": "status(id, name, icon, color)",
  "filters": { "combinator": "and", "rules": [/* defaultFilters + activeFilters + parentPivotFilters */] },
  "calculations": [{ "field": "id", "func": "count", "name": "COUNT_OF_id" }]
}
// Response: [{ status: { id, name, icon, color }, COUNT_OF_id }, ...]
```

```jsonc
// User strategy (field-userSelect)
{
  "columns": "assignee(id, name)",
  "filters": { ... },
  "calculations": [...]
}
// Response: [{ assignee: { id, name }, COUNT_OF_id }, ...]
```

```jsonc
// Date strategy (field-date / field-dateTime)
{
  "columns": "days_of_week@start_date",
  "filters": { ... },
  "calculations": [...]
}
// Response: [{ "days_of_week@start_date": "2026-05-13" | "OLDER" | "UPCOMING" | null, COUNT_OF_id }, ...]
```

The backend recognizes 8 date bucket functions natively: `hours_of_today`, `days_of_week`, `days_of_month`, `weeks_of_month`, `weeks_of_quarter`, `months_of_quarter`, `months_of_year`, `quarters_of_year`. Each emits a CASE expression with `OLDER` / `UPCOMING` sentinels for records outside the current window plus the formatted bucket key for in-range records, and the response groups by that result.

## Filter merging

Three filter inputs are merged with `combinator: and`:

1. `defaultFilters` — base filter, e.g. the saved view's filter.
2. `activeFilters` — the user's current filter on the consuming surface (grid, map, etc.). Passing this lets the pill counts react when the user filters the grid.
3. `parentPivotFilters` — for nested pivot filters, the rule contributed by the parent (e.g. when you stack a status pivot above a user pivot).

Pass `null` / `undefined` for any you don't need.

**Selection reset on parent change:** when `parentPivotFilters` changes, the current selection may point to a bucket that no longer exists in the new result set. The hook resets `selectedItemId` to `null` automatically — matching Vue `KvPivotFilter`'s behavior. `defaultFilters` and `activeFilters` changes do **not** reset the selection (they just refetch counts).

## Date buckets

Eight buckets are supported. Each generates a fixed window relative to the `referenceDate` option (defaults to `new Date()`):

| Bucket | Window | Pill labels |
|--------|--------|------------|
| `hours_of_today` | today | 00 → 23 |
| `days_of_week` | current ISO week | MON … SUN |
| `days_of_month` | current month | 1 … 31 |
| `weeks_of_month` | current month | W01 … W05 |
| `weeks_of_quarter` | current quarter | W01 … W13 |
| `months_of_quarter` | current quarter | Jan, Feb, Mar (or matching quarter months) |
| `months_of_year` | current year | Jan … Dec |
| `quarters_of_year` | current year | Q1 … Q4 |

The hook always prepends a `BEFORE` pill (records older than the window) and appends `UPCOMING` (newer) plus `EMPTY` (null date). `BEFORE` and `UPCOMING` emit `<` / `>` filter rules using the bucket boundary so the user can still drill into the long tail.

Change the bucket via the settings popover or programmatically via `setDateBucket(bucket)`. Changing the bucket resets the selection.

## Settings popover

The hook builds a ready-to-render settings popover (passed through `pivotFilterProps.settingsContent`). Contents depend on the field type:

- **For date fields** — a radio group with the eight buckets above.
- **All field types** — a "COUNT of records" preset button plus a function (`sum` / `avg` / `min` / `max`) + field selector populated from the data source's numeric / money / duration fields.

Pass `disableSettings: true` to hide the gear button entirely.

## Loading & error

`isLoading` and `isFetching` mirror the data source + items queries combined. `error` returns the first failed query.

The component renders skeleton pills while the items query is in flight; the underlying data source metadata is reused across pivots that target the same `appSlug` + `dataSourceSlug` via the shared TanStack Query cache key (`['docyrus', 'dataSource', appSlug, dataSourceSlug]`).

## API Reference

### `useDocyrusPivotFilter(options)`

| Option | Type | Default | Description |
|--------|------|---------|-------------|
| `client` | `RestApiClient` | — | Authenticated Docyrus API client. |
| `appSlug` | `string` | — | Target Docyrus app slug. |
| `dataSourceSlug` | `string` | — | Target data source slug. |
| `fieldSlug` | `string` | — | Slug of the field to pivot on. |
| `calculation` | `PivotFilterCalculation \| null` | `null` (count of id) | Aggregate function + field. |
| `defaultDateBucket` | `PivotFilterDateBucket` | `'days_of_week'` | Initial bucket for date / dateTime fields. |
| `defaultFilters` | `unknown` | — | Base filter merged into every query (e.g. saved view). |
| `activeFilters` | `unknown` | — | Current user filter — drives pill counts. |
| `parentPivotFilters` | `unknown` | — | Filter contributed by a parent pivot filter (nested setups). |
| `hideZeroValues` | `boolean` | `false` | Forwarded to `<PivotFilter>`. |
| `defaultSelectedItemId` | `string \| null` | `null` | Initial selection. |
| `referenceDate` | `Date` | `new Date()` | Reference point used to compute date buckets. |
| `disableSettings` | `boolean` | `false` | Hide the gear popover entirely. The hook stays role-agnostic; gate the popover yourself from the caller (e.g. only pass `disableSettings: !isAdmin`). |
| `onConfigurationChange` | `(config: { dateBucket; calculation }) => void` | — | Fires whenever the user changes the bucket or the calculation. Use it to persist the user's preference back to a saved view / preferences API. Matches Vue `KvPivotFilter`'s `configuration` event. |
| `staleTime` | `number` | `30000` | TanStack Query `staleTime` (ms). |
| `enabled` | `boolean` | `true` | Skip fetching when `false`. |
| `dataSourceExpand` | `string \| false` | `'enums'` | `expand` query param for the data-source schema fetch (so select/status fields carry their option metadata). Pass `false`/`''` to omit `expand` for backends that don't support it — e.g. core/tenant system data sources. |

### Return value

| Property | Type | Description |
|----------|------|-------------|
| `pivotFilterProps` | `PivotFilterProps` | Ready-to-spread props for `<PivotFilter>`. Wires `items`, `selectedItemId`, `onSelect`, `total`, `loading`, `hideZeroValues`, `onRefresh`, `settingsContent`. |
| `items` | `Array<PivotFilterItem>` | All items derived from the API response. |
| `total` | `number` | Aggregated stat across all items. |
| `selectedItemId` | `string \| null` | Currently selected item id (`null` = "All"). |
| `setSelectedItemId` | `(id) => void` | Update the selection programmatically. |
| `selectedItem` | `PivotFilterItem \| null` | Currently selected item (`null` for "All"). |
| `filterRule` | `Array<PivotFilterRule> \| null` | Filter rule(s) corresponding to the current selection — merge into your consumer's filter. `null` when "All" is selected. |
| `field` | `DataSourceField \| null` | Resolved field definition. |
| `fieldType` | `'list' \| 'user' \| 'user-multi' \| 'date' \| 'unsupported'` | Strategy derived from `field.type`. |
| `dateBucket` | `PivotFilterDateBucket` | Current bucket. |
| `setDateBucket` | `(bucket) => void` | Change the bucket (resets selection). |
| `calculation` | `PivotFilterCalculation \| null` | Current calculation. |
| `setCalculation` | `(calc) => void` | Change the calculation. |
| `reload` | `() => void` | Refetch metadata + items. |
| `isLoading` | `boolean` | First-fetch loading state. |
| `isFetching` | `boolean` | Background refetch state. |
| `error` | `Error \| null` | First query error, if any. |

## `DocyrusPivotFilterGroup`

Stacks multiple `<PivotFilter>` strips above a consumer surface (data grid, calendar, map…) and wires **bidirectional cross-filtering** between them automatically — when the user picks an item in one strip, every other strip refetches its pill counts with the new selection applied as an `activeFilters` rule.

Because React hooks can't be called in a loop with a dynamic count, the group component delegates each field to a single-instance child that owns its own `useDocyrusPivotFilter`. The group lifts each row's `filterRule` into a shared map and feeds the **other** rows' rules back down as `activeFilters` — so consumer pages don't need to write any state-lift glue.

### Usage

```tsx
'use client';

import { useState } from 'react';

import { useDocyrusAuth } from '@docyrus/signin';

import { useDocyrusDataGrid } from '@docyrus/ui/library/hooks/use-docyrus-data-grid';
import {
  DocyrusPivotFilterGroup,
  type PivotFilterRule
} from '@docyrus/ui/library/hooks/use-docyrus-pivot-filter';

export function TasksPage() {
  const { client } = useDocyrusAuth();
  const [pivotRule, setPivotRule] = useState<Array<PivotFilterRule> | null>(null);

  if (!client) return null;

  const grid = useDocyrusDataGrid({
    client,
    appSlug: 'base',
    dataSourceSlug: 'task',
    listParams: pivotRule
      ? { filters: { combinator: 'and', rules: pivotRule } }
      : undefined
  });

  return (
    <>
      

      {grid.toolbar}
      {grid.table}
    </>
  );
}
```

Clicking **High** in the priority strip narrows the status strip's pill counts to "only high-priority tasks per status", and vice-versa. The `onFilterRuleChange` callback fires whenever any pivot selection changes, with the AND-combined rule of every active selection (or `null` when nothing is selected).

### API Reference

| Prop | Type | Description |
|------|------|-------------|
| `client` | `RestApiClient` | Authenticated Docyrus API client. |
| `appSlug` | `string` | Target app slug. |
| `dataSourceSlug` | `string` | Target data source slug. |
| `fields` | `ReadonlyArray<DocyrusPivotFilterGroupField>` | One entry per stacked strip. Order = stack order top-to-bottom. |
| `defaultFilters` | `unknown` | Base filter applied to every strip's items query (e.g. saved view). |
| `activeFilters` | `unknown` | External filter applied to every strip's items query (e.g. the consumer grid's current toolbar filter). |
| `onFilterRuleChange` | `(rule: Array<PivotFilterRule> \| null) => void` | Fired with the AND-combined rule of every pivot's current selection. `null` when no pivot has a selection. |
| `vertical` | `boolean` | Render strips vertically (column rail). |
| `compact` | `boolean` | Compact pill layout. |
| `className` | `string` | Wrapper class name. |
| `staleTime` | `number` | TanStack Query `staleTime` (ms). Forwarded to every strip. |
| `dataSourceExpand` | `string \| false` | `expand` query param for the shared data-source schema fetch (default `'enums'`). Pass `false`/`''` to omit `expand` for backends that don't support it. Forwarded to every strip. |

### `DocyrusPivotFilterGroupField`

| Field | Type | Description |
|-------|------|-------------|
| `fieldSlug` | `string` | Slug of the field to pivot on. |
| `defaultDateBucket` | `PivotFilterDateBucket` | Initial date bucket — only used for `field-date` / `field-dateTime`. |
| `calculation` | `PivotFilterCalculation \| null` | Initial calculation. `null` (default) is `count` of `id`. |
| `totalLabel` | `string` | Override the "All" pill label for this strip. |
| `hideZeroValues` | `boolean` | Hide items whose stat is zero (except the currently selected one). |
| `disableSettings` | `boolean` | Disable the settings popover for this strip. |
| `defaultSelectedItemId` | `string \| null` | Initial selection. |

## Type Exports

| Type | Description |
|------|-------------|
| `PivotFilterAggregateFunc` | `'count' \| 'sum' \| 'avg' \| 'min' \| 'max'`. |
| `PivotFilterCalculation` | `{ func: PivotFilterAggregateFunc; field: string }`. |
| `PivotFilterDateBucket` | One of the eight bucket ids. |
| `PivotFilterFieldType` | `'list' \| 'user' \| 'user-multi' \| 'date' \| 'unsupported'`. |
| `PivotFilterRule` | `{ field, operator, value }` — emitted via `filterRule`. |
| `UseDocyrusPivotFilterOptions` | Hook option shape. |
| `UseDocyrusPivotFilterResult` | Hook return shape. |
| `DocyrusPivotFilterGroupField` | Per-pivot config shape used in `<DocyrusPivotFilterGroup>`. |
| `DocyrusPivotFilterGroupProps` | Group component prop shape. |