LEARN
Group, count, total and rank your data in the database and receive only the answer.
Syntax
Name an alias and the table, then the clauses you need, then RETURN. The clauses go in this order, and each is optional.
LEARN alias FROM table
JOIN(
["other_table"],
ON(["other_table.column", "alias.column"])
)
FILTER(condition, …)
GROUP(["alias.column", "other_table.column"])
HAVING(aggregate condition, …)
SORT("name DESC", "other")
TAKE(limit [, offset])
DISTINCT
RETURN {
"name": alias.column,
"total": COUNT(alias.column)
}Parameters and arguments
aliasA short name for the FROM table, used in GROUP and RETURN. A joined table is written by its own name
Required: YesFROM tableThe table to read
Required: YesJOIN(…)Joins another table, by the pair of columns in
Required: NoON. Write as many JOINs as you need, one after anotherFILTER(…) or WHERE(…)Keeps the rows to count, before they are grouped. See FILTER
Required: NoGROUP([…])The columns to group by. See GROUP
Required: When RETURN has a plain columnHAVING(…)Keeps the groups after they are counted. See HAVING
Required: NoSORT(…)Orders the groups. Name a RETURN name, such as
Required: No"revenue DESC", to order by a computed value, and add more names to break tiesTAKE(limit, offset)Keeps the first groups, after skipping
Required: Nooffsetof themDISTINCTRemoves repeated result rows
Required: NoRETURN {…}The answer's fields: group columns and aggregates
Required: Yes
| Aggregate | What it computes |
|---|---|
COUNT(alias.column) | How many rows are in the group |
SUM(alias.column) | The total of a numeric column |
AVERAGE(alias.column) | The mean of a numeric column |
MAX(alias.column) | The largest value |
MIN(alias.column) | The smallest value |
Examples
The three content types with the most entries.
Return type and behaviour
LEARN returns a list of rows, one for each group, or one row when there is no GROUP. The database does the joining, grouping and ordering in one query, so only the answer travels.
Groups come back in no fixed order unless you SORT them. A HAVING that no group meets returns an empty list.
Errors and exceptions
| Error | What happened | How to recover |
|---|---|---|
HTQL-2001 | A column in RETURN is not in GROUP and is not inside an aggregate, a joined table or column is unknown, or the RETURN holds something other than a group column or an aggregate | Add the column to GROUP([…]), wrap it in an aggregate, or check the names |
HTQL-3003 | The table is not registered as readable | Check the table's name |
HTQL-1003 | The statement is not written in HTQL's grammar | Check the clauses against the syntax and their order |
Placement and context
LEARN is a statement of its own. Its clauses follow the FROM table, in the order shown.
Limitations
- Every plain column in RETURN must be in GROUP or inside an aggregate. A RETURN with only group columns lists the groups.
- With a JOIN, write a FILTER column with its table's own name, such as
FILTER(entries.published = true). An unqualified column that both tables have is ambiguous. - Not supported: arithmetic on an aggregate in RETURN, such as
COUNT(b.id) / 2. Return the aggregate, or test it in HAVING. - Not supported: the infix
ORin FILTER. WriteFILTER(OR(a = 1, b = 2)), withAND(…)andNOT(…)where you need them. - Not supported:
EXPR("…")in RETURN or GROUP. It is reserved for the platform's own callers.
Permissions and security
LEARN needs read access to every table it names.
Best practices
- Filter first, so the database counts fewer rows.
- Name the aggregate in RETURN, and SORT by that name.
- Add TAKE when you want only the top groups, and a second TAKE number to page.
- Join in LEARN instead of fetching two tables and joining in your code.