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

  • alias

    A short name for the FROM table, used in GROUP and RETURN. A joined table is written by its own name

    Required: Yes
  • FROM table

    The table to read

    Required: Yes
  • JOIN(…)

    Joins another table, by the pair of columns in ON. Write as many JOINs as you need, one after another

    Required: No
  • FILTER(…) or WHERE(…)

    Keeps the rows to count, before they are grouped. See FILTER

    Required: No
  • GROUP([…])

    The columns to group by. See GROUP

    Required: When RETURN has a plain column
  • HAVING(…)

    Keeps the groups after they are counted. See HAVING

    Required: No
  • SORT(…)

    Orders the groups. Name a RETURN name, such as "revenue DESC", to order by a computed value, and add more names to break ties

    Required: No
  • TAKE(limit, offset)

    Keeps the first groups, after skipping offset of them

    Required: No
  • DISTINCT

    Removes repeated result rows

    Required: No
  • RETURN {…}

    The answer's fields: group columns and aggregates

    Required: Yes
AggregateWhat 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
ErrorWhat happenedHow to recover
HTQL-2001A 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 aggregateAdd the column to GROUP([…]), wrap it in an aggregate, or check the names
HTQL-3003The table is not registered as readableCheck the table's name
HTQL-1003The statement is not written in HTQL's grammarCheck 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 OR in FILTER. Write FILTER(OR(a = 1, b = 2)), with AND(…) and NOT(…) 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.