Server-side grouping

Q: Can rows be grouped on the server?

Yes. With groupBy and a datasource, getRows receives groupBy and groupKeys and answers one row per group with count and totals, so the server runs a GROUP BY query.

groupBy with a datasource asks your server one group level at a time, so a GROUP BY query answers groups with counts and totals.

Paid feature · Server-side groups. Free for 30 days on a new domain; after that it needs a free personal key or a company key. See pricing

Live demoServer-side grouping

Groups with counts and totals from the server, loaded as they open.

Code for this demo

import { createGrid, type Row, type ServerParams } from '@freegridjs/core';
import { makeRows } from '../data';
import { select, separator, text, tr, type DemoContext } from '../ui';

export default function mount(ctx: DemoContext) {
  const { lang } = ctx;
  const h = (en: string, ko: string) => tr(lang, { en, ko });
  const all = makeRows(20000);

  /**
   * A pretend server. With groupBy it answers, for the open groups (groupKeys),
   * one row per group with its count and totals — what a GROUP BY query returns.
   */
  const datasource = {
    getRows(p: ServerParams) {
      const by = p.groupBy ?? [];
      const keys = p.groupKeys ?? [];
      let rows = all.filter(r => keys.every((k, i) => r[by[i]] === k));
      let answer: { rows: Row[]; total?: number };
      if (keys.length < by.length) {
        const col = by[keys.length];
        const groups = new Map<unknown, { count: number; q1: number; total: number }>();
        for (const r of rows) {
          const g = groups.get(r[col]) ?? { count: 0, q1: 0, total: 0 };
          g.count++;
          g.q1 += r.q1;
          g.total += r.total;
          groups.set(r[col], g);
        }
        const desc = p.sort.find(s => s.colId === col)?.dir === 'desc';
        answer = { rows: [...groups].sort((a, b) => String(a[0]).localeCompare(String(b[0])) * (desc ? -1 : 1)).map(([v, g]) => ({ [col]: v, ...g })) };
        ctx.log(`getRows(groupKeys: ${JSON.stringify(keys)}) → ${answer.rows.length} ${h('groups', '그룹')}`);
      } else {
        rows = rows.slice(p.start, p.end);
        answer = { rows, total: rows.length };
        ctx.log(`getRows(groupKeys: ${JSON.stringify(keys)}) → ${rows.length} ${h('rows', '행')}`);
      }
      return new Promise<typeof answer>(resolve => setTimeout(() => resolve(answer), 250));
    },
  };

  const grid = createGrid(ctx.host, {
    columns: [
      { field: 'country', header: h('Country', '국가'), width: 160 },
      { field: 'company', header: h('Company', '회사'), width: 160 },
      { field: 'name', header: h('Name', '이름'), width: 160 },
      { field: 'q1', header: 'Q1', type: 'number', format: '#,##0', width: 130, aggFunc: 'sum' },
      { field: 'total', header: h('Total', '합계'), type: 'number', format: '#,##0', width: 140, aggFunc: 'sum' },
    ],
    datasource, getRowId: r => String(r.id), locale: lang,
    groupBy: ['country', 'company'],
  });

  text(ctx, h('20,000 rows stay on the "server". Opening a group asks only for what is inside it; the log shows each request.',
    '2만 행은 "서버"에만 있습니다. 그룹을 펼칠 때 그 안에 든 것만 요청합니다. 아래 기록에서 요청을 볼 수 있습니다.'));
  separator(ctx);
  select(ctx, h('Group by', '그룹 기준'), [['country,company', h('Country → Company', '국가 → 회사')], ['company', h('Company', '회사')], ['', h('None', '없음')]], 'country,company',
    v => grid.setGroupBy(v ? v.split(',') : []));
  return grid;
}

Server-side grouping is row grouping for data that stays on your server. Give a grid with a datasource a groupBy, and the grid asks for one level at a time: first the groups at the top, then, as each group opens, the groups or rows inside it. A million orders grouped by status cost a GROUP BY query and a few rows, not a million rows in the browser.

Each request carries groupBy and groupKeys, the values of the open groups above it. While there are fewer keys than grouped columns, answer one row per group with the group value, count and the totals of the columns that have an aggFunc, keyed by column id. Inside the deepest group, answer the rows themselves, filtered by every key, in blocks like any server request. Sorting by a grouped column orders the groups.

createGrid(el, {
  columns: [
    { field: 'status' },
    { field: 'customer' },
    { field: 'qty', type: 'number', aggFunc: 'sum' },
  ],
  groupBy: ['status'],
  datasource: {
    async getRows({ start, end, sort, filters, groupBy, groupKeys }) {
      if (groupKeys.length < groupBy.length) {
        // a group level: one row per group, with count and totals by column id
        // SELECT status, COUNT(*) AS count, SUM(qty) AS qty FROM orders GROUP BY status
        return { rows: [{ status: 'new', count: 12, qty: 340 }, { status: 'done', count: 80, qty: 2100 }] };
      }
      // inside an open group: the rows, filtered by groupKeys (e.g. status = 'new')
      return api.rows({ start, end, sort, filters, where: { status: groupKeys[0] } });
    },
  },
});

People can change the grouping with setGroupBy() or the group panel, and each change starts again from the top level. The server helpers for Java, Node.js, Python, PHP, C# and Go answer these requests with safe SQL.

Grouping in getRows

Name	Type	Description
`groupBy`	`string[]`	The grouped column ids, in order.
`groupKeys`	`unknown[]`	Values of the open groups above this request; empty at the top level.
`group rows`	`{ [groupCol], count, [colId]: total }`	While groupKeys.length < groupBy.length, answer one row per value of groupBy[groupKeys.length], with count and the aggregates by column id.
`leaf rows`	`{ rows, total? }`	Inside the deepest open group: the rows [start, end) that match every group key.

Questions about this page

Can rows be grouped on the server?

Yes. With groupBy and a datasource, getRows receives groupBy and groupKeys and answers one row per group with count and totals, so the server runs a GROUP BY query.