# Database Queries The database Service accepts query objects. Collection and field names refer to the declared Data Collections. Examples below use a PurchaseOrder with id, timestamp, deleted, reference, total, status and a supplier reference to Supplier. Supplier has id, name and deleted. Acquire the declared database Service inside the function. ## Query Properties | Property | Meaning | | --- | --- | | `from` | Collection name, optionally followed by fields in parentheses. | | `where` | Conditions on the source records. | | `groupBy` | Array of grouping field expressions. | | `orderBy` | Object mapping field expressions to `asc` or `desc`, or a priority definition described below. | | `distinct` | Return distinct combinations of the selected values. | | `offset` | Non-negative number of result rows to skip. | | `count` | Positive maximum number of result rows; omitted means 25. | | `locking` | Read lock, for example `{ type: "forUpdate" }`. | | `set` | Fields and values for `db.update`, a bulk update. | | `options` | Optional read tuning: `{ fetchSize: 100 }` requests that fetch batch size. This does not change the result limit. | Filtering precedes grouping; ordering and paging apply to the resulting rows. There is no supported `having` interface. To apply a condition to a grouped result, inspect the grouped rows in code; do not confuse filtering one page with filtering the entire grouped result. ## Projections And References ```text query = { from: "PurchaseOrder (id, reference, supplier (id, name), total)", where: { "supplier.id": input.supplierId, deleted: false }, orderBy: { timestamp: "desc", id: "asc" }, offset: input.offset, count: input.pageSize } ``` The projection selects loaded fields, not filter fields. A filter or ordering field need not appear in the projection. The example can order by timestamp without returning it. `supplier (id, name)` loads fields from the referenced Supplier; deeper references use nested parentheses. `"supplier.name"` in a condition follows that reference. It is not a field called supplierId, nor a second query written by the author. An explicit projection is recommended. With no projection, `db.query` selects the Collection's non-reference fields; `db.first` defaults to id only. `db.first` returns at most one record regardless of the query's count, or null when none matches. Supply ordering if the choice among matching records matters. A bare reference such as `supplier` is not a loaded projection: enumerate `supplier (id)` or the required related fields. Load all fields needed by the code. A projection does not fetch other fields on demand. Reading a reference does not automatically exclude a deleted target: add `"supplier.deleted": false` when the operation requires an active Supplier as well as an active order. References are single linked records; this syntax does not hydrate arrays of references or named inverse relationships. ## Conditions | Form | Meaning | | --- | --- | | `{ status: "draft", deleted: false }` | Both equalities must match. | | `{ total: { op: "gte", value: 100 } }` | Greater than or equal. Other comparisons: eq, neq, gt, lt, lte. | | `{ notes: { op: "nil" } }` | Value is null. `nnil` means not null. Do not use scalar null as equality. | | `{ status: { op: "in", value: ["draft", "approved"] } }` | Match a value in the array. `nin` excludes those values. Handle empty arrays in code before querying. | | `{ total: { op: "btw", value: [100, 500] } }` | Inclusive bounds; `nbtw` excludes the interval. | | `{ reference: { op: "like", value: "PO-%" } }` | Case-sensitive pattern; `ilike` is case-insensitive. `%` matches a sequence, `_` one character. | | `{ reference: { op: "ilike", value: "TMP-%", negate: true } }` | Negate the condition. | | `{ tags: { op: "overlap", value: ["priority", "review"] } }` | A declared array field shares at least one value with the supplied array. | | `{ tags: { op: "contains", value: ["priority", "review"] } }` | The array field contains all supplied values. | | `{ tags: { op: "containedBy", value: ["priority", "review"] } }` | Every value in the array field is in the supplied array. | Array conditions require a declared array field and values matching its element type. They do not mean that reference arrays are automatically loaded as linked records. Membership (`in`) compares a scalar field against candidate values; it is different from testing an array field with `overlap` or `contains`. Object equality uses `{ op: "eq", value: expectedObject }`; a bare object is interpreted as a condition definition, not automatically as an object value. Keep interval conditions inside an `and` group, as in the example below. For exclusive date/time bounds use separate `gte` and `lt` conditions instead of `btw`. Pattern wildcards are pattern syntax, not a literal substring-escaping facility. Do not invent an escape option. Use `and` and `or` arrays for nested conditions or multiple comparisons on the same field: ```text filter = { and: [ { deleted: false, "supplier.deleted": false }, { timestamp: { op: "gte", value: start } }, { timestamp: { op: "lt", value: until } }, { or: [ { status: "draft" }, { total: { op: "gte", value: 1000 } } ] } ] } ``` Object-property paths use `/`, for example `"details/country"`. Reference paths use `.`, for example `"supplier.name"`. A condition on an object property can use `{ "details/country": "FR" }`. Declared field types still apply. Names and expressions come from the design, not unchecked caller input. ## Ordering And Paging Ordering keys are evaluated in their declared order. Add an id tie-breaker for stable record pages. Offset starts at zero. A cursor visits the returned page; it does not automatically fetch every matching row. To process all results, explicitly request successive bounded pages with stable ordering. Changes to the underlying records can move offset-based pages; paging does not itself provide a frozen snapshot. Priority ordering places one value first, then uses the next ordering fields: ```text orderBy = { status: { priority: "draft" }, timestamp: "desc", id: "asc" } ``` This places drafts before other statuses. It does not sort the other statuses alphabetically. `priority` is a string value, not a ranking list. When priority is present, direction does not reverse that priority. There is no documented null-placement option. `distinct: true` compares the complete selected row. To list distinct statuses, select status alone, order by status, and page those rows. Selecting id as well makes different record identities distinct even when their statuses match. ## Field Functions And Aggregates A field function uses `field:function`, for example `total:sum`. Use that exact expression in the projection, grouping or ordering as appropriate. These are database functions, not methods on a Record. | Function | Meaning | | --- | --- | | `count` | Count non-null field values; `id:count` counts records with an id. | | `ucount` | Count distinct non-null values. | | `sum`, `avg` | Sum or average numeric values. | | `usum`, `uavg` | Sum or average distinct numeric values. Equal amounts are counted only once. | | `min`, `max` | Minimum or maximum numeric value. | | `std_dev`, `std_dev_pop` | Sample or population standard deviation. | | `var_samp`, `var_pop` | Sample or population variance. | | `minute`, `hour`, `day`, `week`, `month`, `quarter`, `year` | Truncate a date/time to the start of that period. | Date truncation and calendar snippets use the database's time context. For a caller-selected timezone, calculate datetime bounds with Dates and filter explicitly. They do not inherit an Input named timezone automatically. Non-aggregate selected fields belong in `groupBy`. For example, group orders by supplier and status, then order by their total: ```text execute(input) db = service "database_default" cursor = db.query({ from: "PurchaseOrder (supplier (id, name), status, id:count, total:sum)", where: { deleted: false, "supplier.deleted": false }, groupBy: ["supplier.id", "supplier.name", "status"], orderBy: { "total:sum": "desc", "supplier.id": "asc", status: "asc" }, offset: input.offset, count: input.pageSize }) groups = [] for each row in cursor data = row.data() groups.add({ supplier: data.supplier, status: data.status, orderCount: data["id:count"], total: data["total:sum"] }) end return { groups: groups } end ``` This Process requires non-negative integer offset and positive bounded integer pageSize Inputs, the declared database Service and both Collections. Its groups Output is an array of objects containing supplier (id/name), status, orderCount (integer) and total (decimal). Function results keep their expression as the loaded key, such as `"total:sum"`; there is no `AS` alias syntax. Map them to business Output names in code. Counts can be zero; other aggregates can be null when no values contribute. Handle null according to the Output contract. For daily totals use `from: "PurchaseOrder (timestamp:day, id:count, total:sum)"`, `groupBy: ["timestamp:day"]` and `orderBy: { "timestamp:day": "asc" }`, with the appropriate active-record and time filters. `db.count({ from: "PurchaseOrder", where: filter })` counts matching source records, not grouped result rows. Do not use it as the total number of groups. `distinct: true` removes duplicate selected combinations; it is not a substitute for grouping or `ucount`. With paging, counts and sums calculated over returned rows describe only that page. ## Named Condition Snippets A snippet is a named database condition. Use `fn` inside a field condition, not a new method and not a projection function: ```text query = { from: "PurchaseOrder (id, reference, total)", where: { deleted: false, timestamp: { fn: "LastNDays", value: 7 } }, orderBy: { timestamp: "desc", id: "asc" }, offset: 0, count: 100 } ``` `LastNDays` selects complete calendar days preceding today; it excludes today. `Today` selects the current calendar day. `negate: true` negates a snippet condition too. | Snippets | Meaning / value | | --- | --- | | `BeforeNow`, `AfterNow` | Strictly before/after the current database time; no value. | | `ThisMinute`, `ThisHour`, `Today`, `ThisWeek`, `ThisMonth`, `ThisYear` | Current calendar period; no value. | | `LastNMinutes`, `LastNHours`, `LastNDays`, `LastNWeeks`, `LastNMonths`, `LastNYears` | Previous N complete periods, excluding the current period; positive integer value. | | `NamedHour` | Hour 0-23 of today. | | `NamedDayOfWeek` | Day 1-7 of the current week, starting Monday. | | `NamedDay` | Day of the current month; valid integer day for that month. | | `NamedMonth` | Month 1-12 of the current year. | | `NamedYear` | Calendar year as integer. | | `StartOfDay` | At or after today's start; no upper bound. | | `EndOfDay` | Before tomorrow's start; no lower bound. | | `LastMonth` | Previous complete calendar month. | | `WeekToDate`, `MonthToDate`, `QuarterToDate`, `YearToDate` | From period start through now. | Snippet values are template substitutions, not ordinary parameter bindings. Only use validated integer values in these snippets, never caller-supplied text or expressions. For arbitrary dates, use ordinary comparison conditions with bound datetime values. Authors select the documented snippets; they do not supply database expressions as snippet values. ## Reading And Updating Use the same filter for a record page and its matching-record count. For a single-record change, `db.first` loads the required fields, a base-row lock protects a concurrent decision, and field assignment or `remove` followed by `save` persists the change. Keep optional joined projections out of that locking read. Use `db.update` for one bulk change with `from`, `where` and a non-empty `set`. Updating `"supplier.id"` changes the order's reference, not the Supplier record. No query or save creates a separate transaction. Bulk updates support local fields and local reference identities such as `supplier.id`; their conditions cannot traverse joined fields such as `supplier.name`. If a decision requires related data, query and validate that data first, then change the intended records. Do not use a projection, ordering or page count to imply a bounded bulk update: bound the actual filter. An absent filter would target every record; state the intended business filter explicitly. Read lock types are `forUpdate`, `forNoKeyUpdate`, `forShare` and `forKeyShare`. An optional qualifier is `noWait` or `skipLocked`, for example `locking: { type: "forUpdate", qualifier: "skipLocked" }`. `noWait` fails instead of waiting for a conflicting lock; `skipLocked` omits locked rows, so an empty result need not mean no matching business record exists. Locks last for the execution transaction, not just the helper function. ## Complete Reporting Examples The companion [query-projections.ais](examples/query-projections.ais) returns a page and matching-record count using the same reference filter. [query-summary.ais](examples/query-summary.ais) computes totals over all matching records, rather than adding only one page. [query-daily-totals.ais](examples/query-daily-totals.ais) groups a named calendar interval into daily totals. Their Input, Output and resource requirements are listed in the [example index](examples/README.md).