DHLQ query reference

A DHLQ query is a source followed by stages, one per line, each starting with |.

query Late(owner: Identity, today: Text(len=10))
    returns List<PayableRow>
    api authenticated
    dhlq
    std.business.payable
    | where owner == $owner and status != "paid" and due_on < $today
    | sort due_on, -amount_minor
    | take 50
    | select subject as id, supplier, amount_minor, currency, due_on

Arguments are written $name.

Sources

Source	Reads
`self`	This contract's records
`std.business.payable`	Another contract's records, by namespace
`search $q in ns.field, ns.other fuzz=1`	Full-text search over fields

Every record row has subject (its id) and seq (its revision) besides its public fields.

Stages

Stage	Example	Does
`where`	`where owner == $owner and active == true`	Keeps matching rows
`validate`	`validate $limit > 0 and $limit <= 100`	Checks arguments once; refuses the query otherwise
`let`	`let remaining = received - reserved`	Adds a computed field
`sort`	`sort due_on, -amount_minor`	Sorts; `-` for descending
`drop`	`drop 20`	Skips rows
`take`	`take 100`	Keeps at most this many
`select` (or `sel`)	`select subject as id, ref, title`	Chooses and renames columns
`join` (or `lj`)	`join std.crm.contact as c on supplier_id = c.subject`	Joins another contract's rows
`explode`	`explode tags as tag`	One row per list item
`group by` (or `by`)	`group by currency`	Groups rows
`agg`	`agg count() as n, sum(amount_minor) as total`	Aggregates: `count`, `sum`, `avg`, `min`, `max`
`bucket`	`bucket created_at 1d as day`	Groups timestamps into intervals

Totals

    dhlq
    self
    | where cart_subject.id == $cart_id and quantity > 0
    | agg count() as line_count, sum(total_minor) as total_minor

Per group

    dhlq
    std.personal.finance_transaction
    | where owner == $owner
    | group by category
    | agg sum(amount_minor) as spent, count() as n

Paging

Page by a stable key rather than by offset: pass the last key you saw and filter on it.

    | where owner == $owner and ref > $after
    | sort ref
    | take 100

What the engine maintains

Queries over records are answered from projections the engine keeps up to date. Projections support where, sort, drop, take, select, let, validate, group by, agg and bucket. A query using search, join or explode is part of the language, but the engine refuses to activate a plan it cannot maintain natively: try it in the The Workbench, which shows whether it runs.

Indexed equality filters (owner == $owner) are fast: about 8 ms at 10,000 records.