Ordering and paging#

Grouping calculates values over related rows. orderBy sets the order of those rows. distinct removes repeated rows. offset and limit cut a window from the ordered rows. page reads a long result in stable parts. Each example reads the same five entities as Queries.

Order#

Each orderBy entry is a variable, or a variable with a direction. A variable without a direction uses ascending order. An entry can also be an expression. Stardust calculates it for each row and sorts the rows by the result.

{
  "find": [
    "?name",
    "?sal"
  ],
  "where": [
    [
      "?e",
      "salary",
      "?sal"
    ],
    [
      "?e",
      "name",
      "?name"
    ]
  ],
  "orderBy": [
    [
      "?sal",
      "desc"
    ]
  ]
}

[
  {
    "name": "Ada",
    "sal": 120
  },
  {
    "name": "Bob",
    "sal": 100
  },
  {
    "name": "Cy",
    "sal": 90
  },
  {
    "name": "Eve",
    "sal": 80
  },
  {
    "name": "Dan",
    "sal": 70
  }
]

asc and desc are the two directions. Several entries make several sort keys, and the first entry is the strongest.

{
  "find": [
    "?dept",
    "?name"
  ],
  "where": [
    [
      "?e",
      "dept",
      "?dept"
    ],
    [
      "?e",
      "salary",
      "?sal"
    ],
    [
      "?e",
      "name",
      "?name"
    ]
  ],
  "orderBy": [
    "?dept",
    [
      "?sal",
      "desc"
    ]
  ]
}

[
  {
    "dept": "eng",
    "name": "Ada"
  },
  {
    "dept": "eng",
    "name": "Bob"
  },
  {
    "dept": "ops",
    "name": "Eve"
  },
  {
    "dept": "sales",
    "name": "Cy"
  },
  {
    "dept": "sales",
    "name": "Dan"
  }
]

Distinct#

distinct true keeps the first row of each set of equal rows. It compares the find columns, and it respects the type of each value. The integer 1 and the number 1.0 are two different values.

Stardust can drop a repeated partial result during a where join. No later clause, find column or ordering key can read the distinguishing variables. Aggregates, grouping, windows and projection prevent this optimization. The result rows remain the same. A join that reaches one answer along many paths does the work once.

{
  "find": [
    "?dept"
  ],
  "where": [
    [
      "?e",
      "dept",
      "?dept"
    ]
  ],
  "distinct": true,
  "orderBy": [
    "?dept"
  ]
}

[
  {
    "dept": "eng"
  },
  {
    "dept": "ops"
  },
  {
    "dept": "sales"
  }
]

Offset and limit#

offset and limit are counts of rows, and each one accepts 0 or more. Stardust applies them after orderBy and after distinct.

{
  "find": [
    "?name"
  ],
  "where": [
    [
      "?e",
      "salary",
      "?sal"
    ],
    [
      "?e",
      "name",
      "?name"
    ]
  ],
  "orderBy": [
    "?name"
  ],
  "offset": 2,
  "limit": 2
}

[
  {
    "name": "Cy"
  },
  {
    "name": "Dan"
  }
]

Pages#

page reads a long result in parts. It has size, the number of rows in one page, and an optional after, the cursor of the page before it.

{
  "find": [
    "?name"
  ],
  "where": [
    [
      "?e",
      "salary",
      "?sal"
    ],
    [
      "?e",
      "name",
      "?name"
    ]
  ],
  "orderBy": [
    "?name"
  ],
  "page": {
    "size": 2
  }
}

[
  {
    "name": "Ada"
  },
  {
    "name": "Bob"
  }
]

The reply carries hasMore. While more rows exist, it also carries nextCursor. The next page gives that cursor as after.

Copy the first page's cursor into the next query's page.after string. Stardust generates its value at runtime. It is not an entity name. Do not reuse a literal from this article.

A cursor holds the last row of the page and a fingerprint of the query. The fingerprint covers every part of the query except page. A cursor from a different query is Cursor_Mismatch, and a cursor that Stardust cannot open is Invalid_Cursor.

{
  "find": [
    "?name"
  ],
  "where": [
    [
      "?e",
      "salary",
      "?sal"
    ],
    [
      "?e",
      "name",
      "?name"
    ]
  ],
  "orderBy": [
    "?name"
  ],
  "page": {
    "size": 2,
    "after": "not-a-cursor"
  }
}

When a page is rejected#

A page boundary needs a total order, and it cannot share a query with the other window clauses.

Each of these conditions gives one error. This query has page and limit:

{
  "find": [
    "?name"
  ],
  "where": [
    [
      "?e",
      "salary",
      "?sal"
    ],
    [
      "?e",
      "name",
      "?name"
    ]
  ],
  "orderBy": [
    "?name"
  ],
  "page": {
    "size": 2
  },
  "limit": 2
}

Bounds and history covers the limits on the work of a query.

Run it#

database query · query run.