Filters

Each where: line is one condition, and a record must meet all of them. A condition can use comparisons, and, or, not, == none for an empty field, and in with a list. Fields are named with their entity, as Todo.status, so where: Todo.id == todo_id can't be misread as comparing a value with itself.

where_present: applies its condition only when the value on the right is present. That is how a request's optional filters are written: below, filter.due_before narrows the list only when the request gave it.

query due_todos(owner_id: User.id, filter: ListTodos) -> List<TodoView> {    from: Todo    where: Todo.owner_id == owner_id    where: Todo.due_at == none or Todo.due_at > clock.now    where: Todo.status in [TodoStatus.open, TodoStatus.waiting]    where_present: Todo.due_at <= filter.due_before    order: [Todo.created_at desc]    limit: 50}

A condition must be one the database can use an index for: a field, named first, compared with a value. So a condition can't compare two fields, can't compare with a value worked out in the query, and can't apply a function to a field, except for the operations below.

The values a condition compares with are parameters and their fields, literals, enum values, lists of those, and clock.now. A constant works wherever a literal does, since its value is fixed when the project is compiled. < and the other orderings take numbers, times, dates and durations, as they do in code.

A field may be passed to three kinds of operation:

• ai.distance, compared with a bound (The nearest-neighbour query).
• A geo. test, geo.within, geo.contains or geo.intersects. It takes a geometry field first and a parameter after, on a where: line of its own or joined by and. A GiST index, PostgreSQL's index for shapes, finds the records, and Jadpo's own code decides each one, so the query gives the same answer as code (Queries).
• A standard operation with an exact SQL form, which stands where the field would (Operations in conditions, below).

A field that is none meets no comparison except !=, and not keeps that meaning, so SQL's NULL never shows through. A where: line can't compare with a value that may be absent: that is what where_present: is for.

The compiler creates the indexes the conditions and order need (Indexes).

Operations in conditions

A standard operation may stand where a field does when it has an exact SQL form (SQL forms). The field is its first argument, and every other argument is written out: a literal, a constant, or an enum value such as Rounding.half_even. The database works the operation out with that form, which gives exactly what the operation gives in code. So the query keeps the same records list.filter would keep with the same operation.

The operation's result is compared with a value as a field is: by ==, !=, in and, for numbers, times, dates and durations, the orderings. An operation that gives a Bool is the condition on its own.

use library/types { BookTitle, PublicationDay }/// A book in the catalogue.entity Book {    id: Uuid    title: BookTitle    published_on: PublicationDay}

use library/entities { Book }use library/types { BookCard }/// The books whose title fits on a spine label.query spine_titles() -> List<BookCard> {    from: Book    where: text.length(Book.title) <= 24    order: [Book.title asc]    limit: 50}

use library/entities { Book }use library/types { BookCard, PublicationYear }/// The books first published in a year.query published_in(year_published: PublicationYear) -> List<BookCard> {    from: Book    where: temporal.year(Book.published_on) == year_published    order: [Book.title asc]    limit: 100}

This test runs on PostgreSQL and shows the database agreeing with code: a title is counted in code points, as text.length counts it, however many bytes it takes, and a year ends where temporal.year says it does:

use library/entities { Book }use library/types { BookCard, BookTitle }use library/actions { add_book }use library/queries { published_in }test "a query keeps the books its operation keeps in code" {    var dune: Book = add_book(BookTitle("Dune"), "1965-08-01")    var cafe: Book = add_book(BookTitle("Café Society à la Plage!"), "1965-12-31")    var left_hand: Book = add_book(BookTitle("The Left Hand of Darkness"), "1969-03-01")    var spine: List<BookCard> = spine_titles()    assert text.join(list.map(spine, card -> card.title), ", ") == text.join(        [cafe.title, dune.title],        ", "    )    assert text.length(cafe.title) == 24    assert text.length(left_hand.title) == 25    var sixty_five: List<BookCard> = published_in(1965)    assert list.count(sixty_five) == 2    assert list.count(published_in(1969)) == 1}

The rules for an operation in a condition:

• Every other argument is written out: a literal, a constant, an enum value, a rounding mode, decimal.round's scale: or text.pad_start's fill:. The compiler holds each to the limits code holds it to (a zero divisor, a negative count, empty text to find), and writes it into the statement's text. That way the index and the statement hold the same expression, and PostgreSQL matches them. A value given when the query runs would change what the index holds, so a parameter as another argument is query.condition_unsupported.
• Only an exact form, and one an index can hold. An operation without a SQL form is query.condition_unsupported: text.lower, for example, whose result PostgreSQL's collation would decide. So is an operation whose form an index can't hold. map.count and temporal.period_between read a subquery, and hash.sha256 and encoding.to_utf8 of text call convert_to, which PostgreSQL marks as stable rather than fixed, since it reads the database's encoding. A range's operations take a range written out, which a query can't write.
• The field first, once. The field is the operation's first argument, given as it is. An operation of another operation's result, or with the field anywhere else, is query.condition_unsupported.
• Compared as a field is. A literal the result is compared with is read as a value of the result's type. The not of an operation that gives a Bool is written IS FALSE, since PostgreSQL turns NOT (a = b) into a <> b before it looks for an index. == none is type.incomparable, since an operation always gives a value.
• none. A record whose field is none meets no condition on an operation of that field, not included (the SQL is "field" IS NOT NULL AND ...), since code can't call the operation on none. ai.distance keeps its plain statement, since pgvector's operators give NULL for NULL.
• Anywhere a comparison stands: under not and or, on a where_present: line, in an include's where: and in an aggregate query, since the database works the operation out exactly. order: still takes fields only. A geo. test, which an index finds and code decides, still stands on its own line or under and, and not in includes or aggregates.
• The index. The compiler derives one B-tree index on each expression, however many queries and includes compare it, as it derives one for a field's test (Indexes). It is named <table>_<column>_<operation>_idx, with its digest added when another expression of the same operation and field needs a different name. Like every derived index, it is partial, holding only the records visible: keeps. The schema keeps it as an index's expression, beside the column it reads, and a migration creates it with CREATE INDEX ... ((expression)).

Comparing a field of money or a range, or one kept whole as JSON, isn't built yet (What is built).

The statements (jadpo sql) and the indexes derived for them:

-- spine_titlesSELECT "id", "title"FROM "book"WHERE char_length("title")::bigint <= $1ORDER BY "title" COLLATE "C" ASC, "id" ASCLIMIT 50-- published_inSELECT "id", "title"FROM "book"WHERE (CASE WHEN extract(year FROM "published_on") < 0 THEN extract(year FROM "published_on") + 1 ELSE extract(year FROM "published_on") END)::bigint = $1ORDER BY "title" COLLATE "C" ASC, "id" ASCLIMIT 100CREATE INDEX "book_published_on_year_idx" ON "book" (((CASE WHEN extract(year FROM "published_on") < 0 THEN extract(year FROM "published_on") + 1 ELSE extract(year FROM "published_on") END)::bigint));CREATE INDEX "book_title_length_idx" ON "book" ((char_length("title")::bigint));

A live test (jadpo-cli's operation_conditions) runs such queries on PostgreSQL, edge cases among them, and checks each gives the code's answer through its index.