Advanced and extreme queries

The Pikbase query model is more than a list-filter API. A column descriptor can resolve a field, literal, script, or subquery; filters can compare either side; and nested queries can aggregate related data. Together these primitives cover most operational reporting and selection rules without a custom endpoint.

Use these techniques deliberately. Bound returned rows, select only required columns, and inspect the generated query before putting an analytical query on a hot path.

Correlated aggregate subqueries with __PARENT

Inside a tenant serverless function, __PARENT.<field> references the row currently being evaluated by the immediately enclosing query. This makes a nested query correlated rather than global.

The following query selects a cluster only when its capacity is greater than the number of tenants assigned to that same cluster:

const query = new EntityQueryParams(_defs.GsbCluster);
query.query = [];
query.count = 1;
query.select((cluster) => cluster.id);
query.sortBy((cluster) => cluster.priority, _enums.QuerySortType.Desc);

const hasCapacity: SingleQuery = {
  col: { name: "capacity" },
  val: {
    name: "tenants",
    valQuery: {
      selectCols: [{ name: "id", aggregateFunction: "Count" }],
      queries: [
        {
          col: { name: "cluster_id" },
          val: { name: "__PARENT.id" },
          function: "Equals",
        },
      ],
    },
  },
  function: "Greater",
};

query.query.push(hasCapacity);
const result = await entityService.query(query);
const availableCluster = result.entities[0];

Here val.name: "tenants" establishes the related collection used by the nested query, Count(id) produces one scalar value, and __PARENT.id binds each tenant count to the cluster currently under evaluation. Without that parent reference, the count would describe the complete inner result set rather than the current cluster.

Use __PARENT only in trusted tenant-runtime queries. It is a query expression, not a value supplied by an end user, and it is not the syntax for ordinary browser-side filtering.

Set membership from another query

Use val.valQuery with MatchArrays when the right-hand side can return many values. This example finds users whose role is selected by another query:

{
  "entDefName": "GsbUser",
  "filters": [{
    "col": { "name": "roles.id" },
    "val": {
      "valQuery": {
        "entDefName": "GsbRole",
        "selectCols": [{ "name": "id" }],
        "filters": [{
          "col": { "name": "title" },
          "val": { "value": "Administrator" },
          "function": 0
        }]
      }
    },
    "function": 27
  }],
  "distinct": true,
  "count": 100
}

MatchArrays treats the subquery result as a set. Use Equals only when the subquery is guaranteed to produce one scalar row. Multi-reference joins can repeat the outer row, so select distinct results when appropriate.

Compare a row with a global aggregate

An aggregate selectCols entry turns a subquery into a scalar. This filter selects files larger than the average file size:

{
  "col": { "name": "size" },
  "val": {
    "valQuery": {
      "entDefName": "GsbFile",
      "selectCols": [{ "name": "size", "aggregateFunction": 2 }]
    }
  },
  "function": 2
}

The same shape supports maximum, minimum, sum, count, and variance. Add filters to the inner query to compute the threshold from a selected population.

Compare two fields on the same row

val is also a column descriptor. Give it a name instead of a literal value to compare fields:

{
  "col": { "name": "createDate" },
  "val": { "name": "lastUpdateDate" },
  "function": 4
}

This is useful for stale-state checks, date ordering, reconciliations, and invariant audits.

Group and aggregate by a date part

Combine groupBy, dateModifier, and aggregateFunction for analytical projections:

{
  "entDefName": "Order",
  "selectCols": [
    { "name": "createDate", "dateModifier": 2, "groupBy": true },
    { "name": "id", "aggregateFunction": 3, "selectAsTitle": "orderCount" },
    { "name": "total", "aggregateFunction": 1, "selectAsTitle": "revenue" }
  ],
  "count": 24
}

This groups orders by calendar month, counts them, and sums revenue. Add ordinary filters to constrain tenant-visible records, status, region, or date range.

Build nested boolean logic

Use children as parentheses. A filter's relation joins it to the preceding sibling, while a child group's relation joins the complete group to its preceding sibling:

{
  "filters": [
    { "col": { "name": "active" }, "val": { "value": true }, "function": 0 },
    {
      "relation": "and",
      "children": [
        { "col": { "name": "priority" }, "val": { "value": "high" }, "function": 0 },
        { "relation": "or", "col": { "name": "overdue" }, "val": { "value": true }, "function": 0 }
      ]
    }
  ],
  "count": 50
}

The result is active AND (priority = high OR overdue). Use negate for a single predicate; do not use it to invert a child group.

Know the boundaries

• Authorization and row policies still apply to outer and nested queries. A query shape never grants access.
• Equals with a multi-row scalar subquery fails; use MatchArrays for a value set.
• Distinct outer rows may not make calcTotalCount a safe pagination count for a multi-reference join.
• Includes retrieve bounded related data; they are not a substitute for an unbounded graph traversal.
• Correlated aggregates can be expensive because the inner expression is evaluated in the context of outer rows. Filter early and return few rows.
• External values still belong in val.value; never construct scripts or field names from untrusted input.

See Build queries with QueryParams for the basic builder and transport forms, and the query operation reference for the complete wire model and enum values.