28.1 Pivot tables

A pivot summarises the matching nodes as a table, like a spreadsheet pivot table. Rows and columns are groups of nodes by property value, and each cell holds measures over its nodes. A pivot returns the table, not the nodes.

var pivot = db.Query<IEvent>()
              .Where(e => e.Status == EventStatus.Published)
              .Pivot()
              .AddRow(e => e.Venue)                              // a row per venue
              .AddColumn(e => e.StartsUtc, DateInterval.Month)   // a column per calendar month
              .AddCount("events")
              .AddSum(e => e.Price, "revenue")
              .AddAverage(e => e.Price)                          // named "Price.Average"
              .SetRowOptions(e => e.Venue, maxGroups: 20, sortByMeasure: "revenue", otherGroup: true)
              .Execute();

foreach (var row in pivot.EnumerateRows()) {
    Console.Write(row.Group.DisplayName);                        // the venue's name
    foreach (var cell in row.Cells) Console.Write("\t" + (cell?.Get("revenue") ?? 0));
    Console.WriteLine("\t" + row.Total!.Get("revenue"));         // the row's total
}

Grouping by a relation needs [RelationProperty(Facet = true)] on it, here on IEvent.Venue. With no AddColumn, a pivot is a plain group-by. With no rows and no columns, it is one cell over every match.

Groups

Each AddRow or AddColumn adds a level to its axis, in call order. You can group by anything you can facet on.

Method	Groups by
`AddRow(expr)`	Values or ranges, chosen as for `AddFacet`
`AddRow(expr, DateInterval.Month)`	`Year`, `Quarter`, `Month`, `Week` (ISO, Monday first), `Day` or `Hour`
`AddRowValues(expr)`	One group per value
`AddRowRanges(expr, bucketCount)`	Ranges chosen by the engine
`AddRowRange(expr, from, to, "label")`	One range of your own; ranges on one property form one level
`SetRowOptions(expr, …)`	`maxGroups`, `minCount`, `includeMissing`, `sortByMeasure`, `descending`, `otherGroup`

The column twins (AddColumn, AddColumnValues, SetColumnOptions, …) work the same way. sortByMeasure sorts a level's groups by a measure, largest first; "Count" always works. maxGroups and minCount trim the groups, and otherGroup: true gathers what they trim into one (other) group. includeMissing adds a (none) group for nodes without a value.

Measures

AddCount, AddCountDistinct, AddSum, AddAverage, AddMin, AddMax and AddMeasure(PivotFunction, …). Sum, average, min and max need a numeric property. A measure is named Count or <Property>.<Function> unless you give it a name. Values are double?, and null where no node in the cell has a value. An average divides by the nodes that have a value.

The result

PivotResult has Rows and Columns (with their Levels and Groups), Measures and Cells. Cells are sparse: read one with pivot[row, column], which is null when empty, and a value with cell.Get("revenue"). RowTotals, ColumnTotals and GrandTotal are computed over their own nodes, not added up from cells, so averages are right and a node in two groups counts once. EnumerateRows() gives the table row by row, and ToTable() one line per cell.

A group's Values (one per level, the related node for a relation) and Values2 (the top of a range) are what SetFacetValue and SetFacetRangeValue take, so a facet query can list the nodes behind a cell.

Totals, limits and paging

.SetTotals(rows: true, columns: true, subTotals: true)   // sub-totals for every level above the last
.SetLimits(maxCells: 10_000, throwWhenExceeded: false)   // default 250 000; past it rows are cut and Capped is set
.SetRowPaging(pageIndex: 0, pageSize: 50)                // Rows.TotalGroupCount has the full count

Sub-totals come back in RowSubTotals and ColumnSubTotals. The grand total is always computed.

After a facet selection

Open a pivot on a facet query to summarise the nodes the user's selection leaves:

var byMonth = db.Query<IEvent>()
                .Facets()
                .SetFacetValue(e => e.Status, EventStatus.Published)
                .SetFacetRangeValue(e => e.Price, 0m, 250m)
                .Pivot()
                .AddRow(e => e.StartsUtc, DateInterval.Month)
                .AddCount()
                .Execute();

Only the selection applies: no facet buckets are counted, and the facet page is ignored. The admin UI's Query section has a pivot view built on this: click a cell to see the nodes behind the number.

A pivot goes to the store as a query string, which a REST client can send too. Properties are named "Type.Property" or by id, and ToString() gives the string of a typed pivot: IEvent.Pivot().AddRow("IEvent.Venue").AddColumn("IEvent.StartsUtc", "Month").AddSum("IEvent.Price", "revenue").