# 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.

```jadpo
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](constants.md) 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](embeddings-and-search.md#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](geospatial-queries.md)).
- **A standard operation with an exact SQL form**, which stands where the
  field would ([Operations in conditions](filters.md#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](indexes.md)).

## Operations in conditions

A standard operation may stand where a field does when it has an exact
SQL form ([SQL forms](sql-forms.md)). 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.

```jadpo
use library/types { BookTitle, PublicationDay }

/// A book in the catalogue.
entity Book {
    id: Uuid
    title: BookTitle
    published_on: PublicationDay
}
```

```jadpo
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
}
```

```jadpo
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:

```jadpo
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](indexes.md)). 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](reads-and-writes-built.md)).

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

```sql
-- spine_titles
SELECT "id", "title"
FROM "book"
WHERE char_length("title")::bigint <= $1
ORDER BY "title" COLLATE "C" ASC, "id" ASC
LIMIT 50

-- published_in
SELECT "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 = $1
ORDER BY "title" COLLATE "C" ASC, "id" ASC
LIMIT 100

CREATE 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.