Full-text search¶

Full-text search is one API on every dialect with it; each dialect writes the SQL its own way. ClickHouse has none through hare: __search and every search expression raise UnSupportedError there before any SQL is sent.

	PostgreSQL	SQLite
Engine	tsvector and tsquery	FTS5
What is searched	any text column or expression	the fields of a `FullTextIndex` the model declares
`__search`	`TO_TSVECTOR(column) @@ PLAINTO_TSQUERY(text)`, or the column as is for a `TSVectorField`	`rowid IN (SELECT rowid FROM index WHERE index MATCH query)`
`SearchRank`	`TS_RANK()` / `TS_RANK_CD()`	`bm25()` with the sign flipped, `NULL` for a row the query doesn't match
`SearchHeadline`	`TS_HEADLINE()`	`highlight()`, or `snippet()` with `max_words` — `NULL` for a row the query doesn't match
Configurations, lexemes, label weights	yes	no
Weights by field	through `SearchVector(weight=...)`	`SearchRank(weights={"title": 10})`

What only one dialect runs is declared by two Features flags, and a call that needs a flag the connection hasn't raises UnSupportedError before any SQL is sent:

supports_text_search_configurations (PostgreSQL) — a text search configuration (config=), a SearchVector as a value of its own (an annotation, +, a weight), lexemes, SearchRank's label weights, normalization and cover_density, and SearchHeadline's config, min_words, short_word, highlight_all and max_fragments;
supports_full_text_index (SQLite) — a FullTextIndex and SearchRank's weights by field.

from hare.search import SearchHeadline, SearchQuery, SearchRank, SearchType, SearchVector

await Book.objects.filter(description__search="white whale")
await Book.objects.filter(description__search=SearchQuery("white whale", search_type=SearchType.PHRASE))
await (
    Book.objects.filter(description__search="white whale")
    .annotate(rank=SearchRank("description", "white whale"))
    .order_by("-rank")
)
await Book.objects.annotate(excerpt=SearchHeadline("description", "white whale", max_words=20))

On SQLite the model declares the index the search reads:

from hare.dialects.sqlite.indexes import FullTextIndex


class Book(Model):
    title = fields.CharField(max_length=200)
    description = fields.TextField()

    class Meta:
        indexes = (FullTextIndex(fields=("title", "description"), tokenizer="porter unicode61"),)

`__search`¶

field__search=text matches the rows whose text holds every word of text (SearchType.PLAIN); text can be a SearchQuery too.

PostgreSQL vectorizes the column with TO_TSVECTOR(); a TSVectorField is used as it is, with its own config. On a plain text column, field__search=SearchQuery(..., config="russian") vectorizes the column with the query's config.
SQLite matches through the model's FullTextIndex covering the field, restricted to the field's column. A field no FullTextIndex covers, or an annotation other than a plain field, raises UnSupportedError before any SQL.

`SearchQuery`¶

SearchQuery(value, config=None, search_type=SearchType.PLAIN, invert=False)

Argument	Meaning
`value`	The search text, an expression giving it, or PostgreSQL's lexemes (read as `RAW`)
`config`	The text search configuration (`"english"`) — PostgreSQL
`search_type`	How the text is read — a `SearchType`; anything else raises `ConfigurationError`
`invert`	Negate the query

SearchType:

Value	Reads	PostgreSQL	SQLite
`PLAIN`	every word, anywhere	`PLAINTO_TSQUERY`	each word as a quoted FTS5 term
`PHRASE`	the words next to each other, in order	`PHRASETO_TSQUERY`	one FTS5 phrase
`WEBSEARCH`	words, `"quoted phrases"`, `or` between alternatives, `-word` for a word that must be missing	`WEBSEARCH_TO_TSQUERY`	the same, as an FTS5 query; an alternative of only `-word`s (`-python`, `hare or -python`) raises `UnSupportedError` — FTS5 excludes words only after a word the rows hold
`RAW`	the database's own syntax, as it is	`TO_TSQUERY`	FTS5's query syntax

On SQLite, except for RAW, the text is bound as a parameter and turned into an FTS5 query by a function hare registers on its connections — none of it is read as FTS5 syntax. Text without a word matches no row.

Queries combine with & (both match), | (either matches) and ~ (negation):

SearchQuery("hare") | SearchQuery("rabbit")
SearchQuery("hare") & ~SearchQuery("python")

PostgreSQL negates any query (!!). FTS5 negates a query only as the right side of & — SQLite writes query & ~query as query NOT query; ~query on its own, inside | or on the left of & raises UnSupportedError before any SQL.

a & b and a | b give a CombinedSearchQuery joining the two with a SearchOperator (AND, OR); SearchQueryCombinable is the base giving both query classes the operators. A query in PostgreSQL's own lexeme syntax is a RawSearchQueryText, which a SearchQuery reads as RAW. All of them come from hare.search.

`SearchVector`¶

SearchVector(*expressions, config=None, weight=None)

The searchable text of one or more fields or expressions, concatenated with a space between them.

As the vector of SearchRank, a SearchVector of plain field names names the ranked fields on every dialect.
As a value of its own it is PostgreSQL's tsvector (TO_TSVECTOR(...), SETWEIGHT(...) with weight="A"..."D" — another letter or value raises QueryError — or an expression giving the letter), concatenated with +. A source that isn't text (a number, JSON) is cast to text, and COALESCE keeps a NULL source from making the vector NULL. An encrypted field raises.
a + b of two vectors is a CombinedSearchVector; SearchVectorCombinable is the base giving both vector classes the +.

`SearchRank`¶

SearchRank(vector, query, weights=None, normalization=None, cover_density=False)

How well each row matches, higher for a better match — for an annotation to filter and order by.

Argument	Meaning
`vector`	The ranked field, a tuple of fields, a `SearchVector`, or (PostgreSQL) a tsvector expression — a `TSVectorField` is ranked as it is
`query`	The search text (`PLAIN`) or a `SearchQuery`
`weights`	A dict of weights by field — a match in a heavier field ranks higher, 1 for a field left out, each a finite number from 0 to 1000000 (SQLite); or PostgreSQL's weights of the labels D, C, B, A as a sequence of four numbers or an expression — another sequence raises `ConfigurationError`
`normalization`	PostgreSQL's length normalization bitmask — an integer of 32 bits or an expression; another value raises `ConfigurationError`
`cover_density`	`TS_RANK_CD()` instead of `TS_RANK()` — PostgreSQL

On SQLite the fields have to be covered by one FullTextIndex of the model, a weight has to name one of its fields (ConfigurationError otherwise), and a row the query doesn't match ranks NULL — FTS5 ranks only the rows it matches. On PostgreSQL TS_RANK() gives such a row 0 for a query of one word, but a rank a little above 0 (1e-20) for one of several, so rank__gt=0 keeps it — filter by __search to keep only the matching rows.

`SearchHeadline`¶

SearchHeadline(
    expression, query, config=None, start_sel=None, stop_sel=None, max_words=None, min_words=None,
    short_word=None, highlight_all=None, max_fragments=None, fragment_delimiter=None,
)

The text with the matched words marked, for an annotation.

Argument	Meaning
`expression`	The field whose text is marked; on PostgreSQL also an expression
`query`	The search text (`PLAIN`) or a `SearchQuery`
`start_sel`, `stop_sel`	The text before and after a matched word — PostgreSQL's `<b>` and `</b>` by default; on SQLite required, FTS5 having no marker of its own; at most 1000 characters
`max_words`	The most words of a fragment — from 1; on SQLite at most 64, and it switches to FTS5's `snippet()`; on PostgreSQL above `min_words`, 15 unless given — `max_words=3` needs a lower `min_words`, else PostgreSQL raises `OperationalError`
`fragment_delimiter`	The text between fragments — PostgreSQL's `" ... "` by default; on SQLite required with `max_words`
`config`, `min_words`, `short_word`, `highlight_all`, `max_fragments`	`TS_HEADLINE()`'s own options — PostgreSQL

On SQLite a row the query doesn't match gets NULL, and a SearchHeadline without start_sel and stop_sel, or with max_words but no fragment_delimiter, raises QueryError before any SQL is sent. A count out of range or a marker that isn't text raises ConfigurationError.

Lexemes (PostgreSQL)¶

from hare.dialects.postgresql.search import Lexeme

SearchQuery(Lexeme("cat", prefix=True, weight="A") & ~Lexeme("dog"))

Lexeme(value, invert=False, prefix=False, weight=None) builds a raw tsquery from lexemes combined with &, | and ~; the value is bound as a parameter. A SearchQuery of lexemes is read as RAW.

`FullTextIndex` (SQLite)¶

class FullTextIndex(SeparateTableIndex):
    def __init__(
        self,
        *,
        fields: Sequence[str],
        name: str | None = None,
        tokenizer: str | None = None,
    ) -> None: ...

An FTS5 table of its own (CREATE VIRTUAL TABLE ... USING fts5(...)) reading its text from the model's table (content=), keyed by the model's integer primary key. Triggers on insert, update and delete keep it in step with the table — a row written by any client, not only hare, is indexed — and it is filled from the rows already there when it is created. Migrations create and drop it (AddIndex, RemoveIndex) with its triggers; changing the fields or the tokenizer replaces it.

Argument	Meaning
`fields`	The indexed text fields — a non-empty list
`name`	The FTS5 table's name — generated from the table and fields by default
`tokenizer`	FTS5's `tokenize` option: `"porter unicode61"` (English stems), `"trigram"` (substrings), ...; FTS5's own `unicode61` when None

The model needs an integer primary key (FTS5's rowid); a model without one, no fields, a descending key or an empty tokenizer raise ConfigurationError. On a connection without Features.supports_full_text_index a model declaring the index raises ConfigurationError in Hare.init(), and a migration creating or dropping it raises UnSupportedError before any SQL.

Query plans¶

Every search expression describes its plan: a later query of the same structure runs on the plan with its own search text bound. Field names, the search type, field weights and the headline's options are part of the structure.

Full-text search¶

__search¶

SearchQuery¶

SearchVector¶

SearchRank¶

SearchHeadline¶