Filtering, views and SQL
Narrow a list with a saved view, a search, a filter tree or a where clause, and read or change many rows with gtable's SQL dialect. All of it through your permissions.
Every way of narrowing a list is AND-ed with your permissions: it can only narrow what you would see anyway.
Saved views
Ask for a saved view instead of rebuilding its filters. It is one parameter, it stays correct when someone edits the view, and it is usually a much smaller answer.
GET /v1/tables/tbl_8fj3kq2wmv5d/records?viewId=viw_e4ks9mz2bh7wA view belongs to whoever made it; you are handed your own views and nobody else’s.
Search, filters, sorts and where
| Parameter | Takes |
|---|---|
search | a substring, matched over the text fields you can read |
filter | a filter tree as JSON, the same shape a saved view stores |
sorts | a JSON array of { "fieldId", "direction" } levels |
where | a SQL-style condition, in the dialect below |
GET /v1/tables/tbl_8fj3kq2wmv5d/records?filter={"id":"root","kind":"group","mode":"and","children":[{"id":"c1","kind":"condition","fieldId":"fld_7qk2mv9xp4ht","op":"gt","value":50000}]}
GET /v1/tables/tbl_8fj3kq2wmv5d/records?where=Stage = 'won' AND Amount > 50000URL-encode the values in real requests. A field in a filter, a sort or a grouping may be named
by id or by exact name. One you cannot read is refused with a 422 that lists the fields you
can, and never names the hidden one.
A field you can read on only some of your rows cannot be filtered or sorted on (403), because
counting matches would reveal rows outside your scope. GET /v1/app lists those fields per
table under constrained.
To turn a sentence into a filter tree, filters.suggest takes the sentence and returns a tree
you can inspect before you use it.
SQL
POST /v1/sql/query reads; POST /v1/sql/execute changes many rows. Nothing you send is run as
written: the statement is parsed, every column is checked against what you may read, every value
becomes a bound parameter, and your permissions are added to the WHERE.
SELECT stage, COUNT(*) AS n, SUM(amount) AS total FROM Deals GROUP BY stage ORDER BY n DESC
SELECT name, amount FROM Deals WHERE "Expected close" > CURRENT_DATE LIMIT 20
UPDATE Deals SET probability = 100 WHERE stage = 'won'
DELETE FROM Deals WHERE stage = 'lost' AND amount < 1000What the dialect covers:
- one statement over one table, named by name, plural or id; fields by name or id, with double quotes around a name that has spaces;
AND OR NOT, comparisons,[NOT] LIKE,[NOT] IN (…),IS [NOT] NULL,[NOT] BETWEEN, both forms ofCASE;LENGTH LOWER UPPER TRIM ABS ROUND COALESCE SUBSTR DATE STRFTIME IFNULL INSTR REPLACE, andCOUNT SUM AVG MIN MAXwithGROUP BY;- on a link field,
HAS ANY ('rec_…', …),HAS ALL (…)andIS EMPTY.
No JOIN, no subquery, no INSERT (use records.create). A SELECT returns at most 1,000 rows.
A column you cannot read is “Unknown column”, the same as one that does not exist. A field you
can read on only some rows reads as NULL on the others, inside sums and expressions too.
An UPDATE or DELETE touches up to 500 rows, each through the ordinary permission checks, and
is one change in the history, undone in one step. Without a WHERE it is refused unless you
send confirm: true. dryRun: true returns the count and the first ids and changes nothing.
sql.query refuses a write and sql.execute refuses a SELECT, so a key holding only
records:read gets exactly what its scope says.
Builders have the same two operations on any app, under /v1/apps/{appId}/sql/… on the Studio.