Filtering, Sorting & Pagination#

The list endpoint of a view, GET /{prefix}, reads filters, a sort order and a page from the URL:

GET /users?name=John
GET /users?status=active,pending
GET /users?age__gte=18&age__lt=65
GET /users?deleted_at__isnull=true
GET /users?email__icontains=example
GET /users?sort=-created_at,name
GET /users?page=2&page_size=50

Restly builds these list params when the view registers. It reads the view’s response class, and gives each field that maps to a column one filter, plus filters with an operator suffix such as __gte. So a client can filter and sort only on the fields that go out. By default the response class is the view’s schema without its WriteOnly fields; see Set your own response class.

By default a list has the keys of the resource’s own fields. A view chooses other keys with four settings; see Choose the keys of a list. For a filter that the generated keys do not give, see Add a filter of your own.

The filters narrow the rows inside the view’s scope. They can never show a row that the scope hides. To limit the rows of every list, declare the view’s scope, as Give one view other rows shows. For list params of another form, such as those of another client library, see Write the list params yourself.

Restly rejects an unknown key with 422. A filter only narrows the result, so a mistyped key that Restly ignored would return more rows than the client asked for. A view can ignore unknown keys instead; see Accept extra query parameters.

Lists are paginated by default: 50 rows per page, and a client can ask for up to 1000. The rows come in a data envelope with the total count. The view’s pagination setting changes the limits and the parameter names, or turns pagination off.

Filter a list#

A filter compares a field with a value from the URL. Plain equality uses the name of the field:

GET /users?name=John

A suffix on the name chooses another comparison:

Suffix

SQL

Example

(none)

field = value

?name=John

__gte

field >= value

?age__gte=18

__lte

field <= value

?age__lte=64

__gt

field > value

?age__gt=17

__lt

field < value

?age__lt=65

__in

field IN (...)

?status__in=active,pending

__ne

field != value

?status__ne=archived

__contains

field LIKE '%value%'

?email__contains=Example

__icontains

field ILIKE '%value%'

?email__icontains=example

__isnull

field IS NULL or IS NOT NULL

?deleted_at__isnull=true

__isnull takes true or false, not the text null.

Details

Not every field gets every suffix:

  • The range suffixes (__gte, __lte, __gt, __lt) exist only for types that have an order, such as numbers, text and dates. Booleans, UUIDs and labels do not get them: an enum, or a Literal of strings. An int enum, such as an IntEnum, has the order of its numbers and gets them. A flag, such as an IntFlag, gets the other filters, but no range.

  • A column of SQLAlchemy’s Enum type gets no range suffixes, whatever the type of the field, because it holds labels. Mapped[Status], with an enum Status, is such a column by default. So an int enum needs a column that stores the number, such as Integer. On PostgreSQL, a native ENUM orders its labels as they were declared. To filter by that order, write the range yourself; see Add a filter of your own.

  • __contains and __icontains exist only for text fields, and not on a column of SQLAlchemy’s Enum type.

  • A field that holds a dict or a list, such as a JSON or ARRAY column, gets only __isnull. A value from the URL cannot become a collection, so the other filters would fail on every request. Such a field is no sort key either: a document or a list has no order that a client means, and PostgreSQL’s json type has none at all.

  • A field on a JSON column gets only __isnull too, also when it holds one value, such as a str in fr.models.PydanticJSON(str). The column holds JSON text, such as "x" with its quotes, and SQL compares that text, not the value. So the other filters would find no row on SQLite and fail on PostgreSQL. Such a field is no sort key either. In the filter of a react-admin view, and in FilterKey.parse(), such a field takes only null. To filter on a key in a document, write the filter yourself; see Add a filter of your own.

  • A WriteOnly field, a field with Field(exclude=True), and a field of Pydantic’s secret types, such as SecretStr, get no filter and are not a sort key. A filter on such a field would let a client find out its value. Restly cannot see a value that a serializer hides, such as a number shown as ***-1234. Leave such a field out with exclude_filters and exclude_sort_keys.

A filter compares a time in UTC: a time without an offset is UTC, and a time with an offset is compared as the same time in UTC. See Keep times in UTC.

A column_property counts as a column. So a value that SQL computes for each row, such as the number of tasks of a project, gets filters and a sort key too, such as ?task_count__gte=1 and ?sort=-task_count. Add a value to each row shows such a value.

Filter values go through the field’s validation#

Equality, __ne, __in and the range filters validate each value with the field’s Pydantic validation, so the field validators of the schema run on filter values too. A field of type Any, such as the id of fr.IDSchema, gives the value the Python type of its column, such as a UUID from its text. On a column of SQLAlchemy’s Enum type, the value must also be one of its labels. A number must fit its integer column, such as the range of Integer, and text must hold no NUL character; see Column validation. A filter does not check the length of text. A value that fails returns 400 (see Why a bad list request returns 422 or 400).

Details

Restly validates one value at a time, without the other fields. So:

__contains and __icontains use the text as it is. They only refuse a NUL character. __isnull reads a boolean.

Match one of several values#

Values separated by commas in a plain equality filter are combined with OR:

GET /users?status=active,pending

This gives WHERE status = 'active' OR status = 'pending', which is the same as SQL IN. To make IN visible in the URL, use __in:

GET /users?status__in=active,pending

Exclude several values#

Values separated by commas in __ne are combined with AND. A row must differ from every value:

GET /users?status__ne=archived,deleted

This gives WHERE status != 'archived' AND status != 'deleted', which is the same as SQL NOT IN.

Search for text#

To require several pieces of text at once, repeat the parameter. Each value becomes its own LIKE or ILIKE condition, and the conditions are combined with AND:

GET /users?name__contains=john&name__contains=doe
GET /users?name__icontains=john&name__icontains=doe

__contains gives WHERE name LIKE '%john%' AND name LIKE '%doe%'. __icontains uses ILIKE.

Details

Restly also splits one __contains or __icontains value at each space, so ?name__contains=john%20doe gives the same result. Prefer the repeated parameter when you build the URL yourself: it has only one meaning.

Restly escapes %, _ and \ before it builds the LIKE condition. A search finds the text as written; these characters are not wildcards. In a filter of your own, pass SQLAlchemy’s autoescape=True for the same result, such as Item.name.icontains(q, autoescape=True). Without it, ?q=% matches every row.

Combine filters on one field#

Repeat the parameter to add conditions with AND:

GET /users?created_at__gte=2024-01-01&created_at__lt=2025-01-01

This gives WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'.

Sort a list#

The sort parameter takes sort keys separated by commas. A sort key is the name of a field. A - before a key sorts in descending order:

GET /users?sort=-created_at,name

By default every own field of the resource is a sort key. An unknown sort key gets 422, and so does a key that appears twice, such as ?sort=name,-name. The description of sort in OpenAPI lists the sort keys. To choose them, see Choose the keys of a list.

A dotted key sorts by a related field, when include_sort_keys has it: ?sort=-city.country.code orders by that column, and joins each step of the path. A row without the related row stays in the list. Dotted keys follow the rules of Filter on a related row.

Without sort, the list is in primary key order. For another order when the client sends no sort, set default_sort. It is written as the client writes sort:

class UserView(fr.AsyncRestView):
    ...
    default_sort = "-created_at"

GET /users is then newest first, and GET /users?sort=name is sorted by name only. OpenAPI shows -created_at as the default of sort.

Restly always adds the primary key as the last sort term, every column of a composite key included. So rows with equal values are never skipped or repeated from one page to the next.

Details

A key of default_sort must be a field of the response class that can be a sort key. include_sort_keys does not have to keep it, so a view can sort by a field that the client cannot sort by. A key that the response class does not have fails when the view registers, also when the view gets default_sort from a base view. A key that appears twice, such as in "name,-name", fails when the class is defined, or when the view registers for a mixin. A view without those fields sets default_sort = None for primary key order. An empty ?sort= gets the default too. An override of apply_list_params finds the default in list_params.sort. With include_sort_keys = (), the list params have no sort parameter, but the default still applies.

For a default order on a column that the response class does not have, such as a position that an admin sets, order the statement when the client sends no sort:

class ItemView(fr.AsyncRestView):
    ...

    async def apply_list_params(self, stmt, list_params):
        if list_params.sort is None:
            stmt = stmt.order_by(Item.position)
        return await super().apply_list_params(stmt, list_params)

A column of SQLAlchemy’s Enum type sorts by its labels, in the order of the database. On SQLite and on a column that is not a native enum, such as the one that fr.ModelDefaults gives Mapped[Status], that is the order of the text. A native ENUM on PostgreSQL sorts its labels as they were declared. An int enum sorts by its numbers only on a column that stores them, such as Integer.

Where NULLs go#

A sort puts NULLs last, in both directions and on every database. So the users without a last_login come after the other users, in ?sort=last_login and in ?sort=-last_login.

sort_nulls sets where NULLs go. "first" puts them first in both directions. None leaves them where the database puts them: PostgreSQL puts them last in an ascending sort and first in a descending sort, SQLite and MySQL the other way round. A project base view can set sort_nulls once for every view.

Details

On PostgreSQL, Oracle and SQLite 3.30 or later, Restly writes NULLS LAST in the SQL. MySQL and SQL Server do not have it, so Restly sorts on CASE WHEN ... IS NULL first. A column of the model that cannot be NULL gets neither. A column through a relationship always gets it, because the row without the related row has NULL there.

The rule for NULLs can make a sort on a large table slower, because the database cannot always read an index in that order:

  • PostgreSQL reads a plain index in the order of x NULLS LAST, but not in the order of x DESC NULLS LAST. For a fast ?sort=-last_login, also give the column an index in that order. Keep the plain index for ?sort=last_login:

    from sqlalchemy import Index

Index("ix_user_last_login_desc", User.last_login.desc().nulls_last())

  • MySQL and SQL Server cannot read a plain index in the order of the CASE, in either direction, so they sort the rows themselves.

If a sort must use a plain index, set sort_nulls = None on that view.

Rename the sort parameter#

To keep the URLs of another library, rename the parameter with sort_query_param, such as "ordering" for Django REST framework or "order_by" for fastapi-filter:

class UserView(fr.AsyncRestView):
    sort_query_param = "order_by"

Clients then send ?order_by=-created_at, and sort becomes an unknown key. A field called sort gets its filter key back.

Page through a list#

The client asks for a page with page and page_size:

GET /users?page=2&page_size=50

page starts at 1. page_size must be at least 1 and at most max_page_size, 1000 by default. Without page_size, the endpoint uses default_page_size, 50 by default. Restly also rejects a page whose SQL offset, (page - 1) * page_size, is larger than 2**63 - 1. A value out of range returns FastAPI’s standard 422.

The response holds the page in data, with total_count, page, page_size and total_pages. To change this envelope, see Change the list envelope.

Set the limits with fr.NumberedPagination on the view class. On a public endpoint, use a smaller page size and maximum. max_page also limits the page number:

class UserView(fr.AsyncRestView):
    pagination = fr.NumberedPagination(
        default_page_size=25, max_page_size=200, max_page=100
    )

Rename the page parameters#

Rename the two parameters with page_query_param and page_size_query_param:

class UserView(fr.AsyncRestView):
    pagination = fr.NumberedPagination(
        page_query_param="p", page_size_query_param="size"
    )

Clients then send ?p=2&size=50, page and page_size become unknown keys, and OpenAPI shows the new names. A schema field with the same public name as a pagination parameter fails when the view registers: rename the parameter, give the field an alias, or leave the field’s plain key out with exclude_filters = ("size",); see Choose the keys of a list. A renamed parameter frees its old name, so a field called page gets its filter back.

Set the pagination once for every view#

Views inherit pagination, so a project base view sets it once. A view that differs changes one setting with replace(), which returns a checked copy:

APP_PAGINATION = fr.NumberedPagination(
    page_size_query_param="size", max_page_size=100
)


class AppView(fr.AsyncRestView):
    pagination = APP_PAGINATION


class LogView(AppView):
    pagination = APP_PAGINATION.replace(default_page_size=10, max_page_size=10)

Return every row without pagination#

Set pagination = None to return every matching row:

@fr.include_view(app)
class TagView(fr.AsyncRestView):
    prefix = "/tags"
    model = Tag
    schema = TagSchema
    pagination = None
    # Response: {"data": [ /* every tag */ ]}

The list has no page and page_size parameters, and Restly runs no count query. Filters and sort still work. The response keeps the data envelope, without the count and page fields.

Use this only for a table that stays small, such as tags or countries. A list without pagination returns the whole table in one response.

Details

None is short for fr.NoPagination(). Use fr.NoPagination(envelope=...) to choose the envelope of a list without pagination; see Change the list envelope.

Coming from fastapi-pagination#

The default Page of fastapi-pagination takes ?page= and ?size=, and returns items, total, page, size and pages. To keep that contract for existing clients, rename the page size parameter and the fields of the envelope:

from typing import Generic, TypeVar

import pydantic

T = TypeVar("T")


class Page(pydantic.BaseModel, Generic[T]):
    data: list[T] = pydantic.Field(alias="items")
    total_count: int = pydantic.Field(alias="total")
    page: int
    page_size: int = pydantic.Field(alias="size")
    total_pages: int = pydantic.Field(alias="pages")


class AppView(fr.AsyncRestView):
    pagination = fr.NumberedPagination(
        page_size_query_param="size", max_page_size=100, envelope=Page
    )

The default page size of 50 and the maximum of 100 match those of fastapi-pagination. fastapi-pagination has no filters and no sorting. Restly adds both, as this page describes.

Choose the keys of a list#

A view chooses the keys of its list with include_filters and include_sort_keys:

class ItemView(fr.AsyncRestView):
    ...
    include_filters = ("status", "category_id", "name__icontains", "created_at__*")
    include_sort_keys = ("name", "created_at")

An entry is a key as the client sends it, such as name__icontains, or a pattern. In a pattern, * matches any characters except .. So "created_at__*" is every filter on created_at with a suffix, and "vendor.*" is every key on the fields of vendor. OpenAPI shows only the keys that are on, and another key gets 422.

To remove a few keys, use exclude_filters and exclude_sort_keys. Restly applies them after the include settings:

class ItemView(fr.AsyncRestView):
    ...
    exclude_filters = ("internal_note", "internal_note__*", "*__icontains")

Details

A * never matches a ., so each * covers one level only. For an item with a vendor, and a country on the vendor, "vendor.*" has the keys on the fields of vendor, but not those on vendor.country. For both levels, write ("vendor.*", "vendor.*.*"). A pattern has no other wildcard: **, ? and [...] are errors.

The pattern "name*" also matches a field such as names. Write ("name", "name__*") for the keys of name only.

include_filters = () leaves no generated filters, and include_sort_keys = () removes the sort parameter. page and page_size are not filters, and they always stay.

Dotted keys are off by default. Each one is a join that a client can add, and a new field on a nested schema would add keys to every list that nests it.

An entry that a view sets and that matches no key fails when the view registers. The message lists the keys of the field:

ItemView.include_filters: 'price__icontains' matches no filter key of
ItemSchema. The filter keys of 'price' are: price, price__in, price__ne,
price__isnull, price__gte, price__lte, price__gt, price__lt. Only text fields
have __contains and __icontains.

A view inherits each setting on its own, as it inherits pagination. So a project base view can set include_filters = (), and a view turns its filters on again with ("*",). An entry that a view inherits may match no key, because a base view serves many schemas. For mypy, give the setting a type on the base view: include_filters: ClassVar[tuple[str, ...]] = (). Without it, mypy takes the type of () and rejects ("*",) in the view. The same holds for every setting; see What a type checker cannot see.

The sort keys are separate from the filter keys, so a view can sort on created_at without a ?created_at= filter. A sort key has no suffix. It never goes through a list of related rows: an item with several tags has no single tags.name to sort on.

To see the keys that a view could have, set include_filters = ("*", "*.*") and include_sort_keys = ("*", "*.*") for a moment, and look at /docs. Every entry must match a key, so leave out "*.*" when the schema has no nested object. Add "*.*.*" for keys two levels down.

derive_schema_list_params() takes the four settings and sort_query_param as keywords, so a route outside a view can build the same list params.

A key that clients use is part of your API. Removing it later breaks those clients. A key also goes away when you remove its field from the response class.

Filter by a foreign key#

A foreign key declared with fr.MustExist[int, Post] filters by its own public name, the same name it has in JSON:

GET /comments?post_id=1
GET /comments?post_id=1,2          # OR, as SQL IN
GET /comments?post_id__in=1,2
GET /comments?post_id__ne=1
GET /comments?post_id__gte=10      # range suffixes work on int ids
GET /comments?post_id__isnull=true

Details

A MustExist[pk, ...] field gets the filters of its pk type. An int id gets equality, __in, __ne, __isnull and the range suffixes. A UUID id gets no range suffixes, as the list of suffixes says. Only a str id gets __contains and __icontains.

A relationship reference filters differently (see Choose a reference style):

  • An IDSchema[Model] field filters by its id, as in ?author.id=1, with the filters of the id’s type. That key has a dot, so the view turns it on first, for example with include_filters = ("*", "author.*").

  • A flat IDRef[Model] field on a relationship gets no filter. To filter by it, also put the foreign key column in the response class, for example as author_id: fr.ReadOnly[int].

Use aliases in the URL#

The keys of the list params use the public name of each field: its alias when it has one, and its Python name otherwise. The URL accepts only the public name. populate_by_name changes how Pydantic reads a request body. It does not add the Python names to the URL.

Take a field with an alias:

from typing import Annotated

from pydantic import Field


class UserSchema(fr.IDSchema):
    user_name: Annotated[str, Field(alias="userName")]

The URL accepts only the alias:

GET /users?userName=Alice        # accepted
GET /users?user_name=Alice       # rejected: the Python name is not public

To use another key in the URL, change the alias.

Add a filter of your own#

For a filter that the generated keys do not give, decorate a view method with fr.list_filter:

from sqlalchemy import ColumnElement


class ItemView(fr.AsyncRestView):
    ...

    @fr.list_filter("popular")
    def filter_popular(self, value: bool) -> ColumnElement[bool]:
        """Items with more than 1000 views, or with false the other items."""
        popular = Item.view_count > 1000
        return popular if value else ~popular

The client sends ?popular=true. The type of value becomes the type of the key: OpenAPI shows a boolean, with the docstring as its description, and ?popular=maybe gets 422. The method gets the value after validation, never the text from the URL.

The method returns a SQLAlchemy condition, a clause, or None for no condition. Restly adds the condition with AND, inside the view’s scope, after the generated filters.

Restly calls the method only when the key has a value. With a default on value, it calls the method on every list request, with the default when the key is missing. This list hides archived items, unless the client asks for them:

@fr.list_filter("allow_archived")
def filter_allow_archived(self, value: bool = False) -> ColumnElement[bool] | None:
    return None if value else ~Item.archived

A key can also be a field and an operator, as the generated keys are. The operator can be a new one:

from sqlalchemy import func


@fr.list_filter("name__length")
def filter_name_length(self, value: int) -> ColumnElement[bool]:
    """Items whose name has this many characters."""
    return func.length(Item.name) == value

The client sends ?name__length=5. Before the last __ comes a field with filters, such as name or vendor.name, so __ always splits a field from an operator. A key without __ and ., such as popular, needs no field.

A filter on a column lets a client find out the values of that column, one request at a time. Write a filter only on data that the client may see.

Details

value: list[str] reads a repeated key, as in ?tag=a&tag=b. A plain type takes the last value, as in FastAPI.

The condition may use only the tables that are in the statement. A condition on another table, such as Vendor.name == value, raises TypeError, because SQL would add the table as a cross join that the scope does not reach. Use the relationship instead: Item.vendor.has(Vendor.name == value).

On an AsyncRestView, the method can be async def, to load data with self.session first. It runs when a plain method would, and only on routes that filter. Often a subquery in the condition is enough, and it is faster than loading ids for IN (...). On a RestView, an async def method fails when the class is defined.

The key is always on: include_filters and exclude_filters choose among the generated keys only. A key with the name of a generated key that is on fails when the view registers. To replace a generated key, such as name or name__icontains, leave it out with exclude_filters, and decorate your own.

A key with an operator looks like a generated key, but it works as your method does. It takes one value of the method’s type, so a comma or a repeated key does not combine values, as they do for a generated key.

A subclass inherits the key. It drops the key by setting the name of the method to None, as in filter_popular = None. A type checker rejects None in place of a method, so give it a type: filter_popular: Any = None. An override without the decorator fails when the class is defined.

Restly runs the filter methods only for the view’s own list params: those of a list route, or a dict or a QueryParams that a method gets. A Pydantic model of another class, such as one of your own, does not run them, and Restly warns with RestlySkippedFilterMethodsWarning.

A key does not start with _. It has __ at most once, with a field before it and an operator after it, and a . only in the field. It is not the name of the view’s page or sort parameters, such as page, page_size or sort, and not the name of a Pydantic model attribute, such as copy.

fr.query.apply_list_params has no view, so it cannot call the method. It raises ValueError for list parameters with a value for the key. A key with a default always has a value. Call the view’s apply_list_params instead.

Accept extra query parameters#

The unknown-key check accepts every query parameter that the route declares. So declare a key of your own where your code uses it. Then the key has a type, and OpenAPI shows it next to the filters. A key that changes the response is a parameter of the endpoint method:

from fastapi.responses import PlainTextResponse


class ItemView(fr.AsyncRestView):
    ...

    @fr.get("/")
    async def get_many_endpoint(self, list_params, as_text: bool = False):
        if not as_text:
            return await super().get_many_endpoint(list_params)
        result = await self.handle_get_many(list_params)
        return PlainTextResponse("\n".join(item.name for item in result.objects))

The client sends ?as_text=true, and ?as_text=maybe gets 422. A key that filters the rows is a filter of your own. A value that other methods or the scope need comes from a dependency: a class attribute with Depends, or a member of Current that Current.depends binds.

Some keys are only known at run time, such as a family of keys extra.<name> that your code reads from self.request.query_params. A route cannot declare them. For these, set unknown_query_params to "ignore". The view then accepts every key that the route does not read, and leaves it out, as FastAPI does:

class ItemView(fr.AsyncRestView):
    ...
    unknown_query_params = "ignore"

Then a mistyped filter key is not an error, and the client gets more rows than it asked for. The default is "forbid". These are the values of Pydantic’s extra setting.

Details

A key that a dependency reads is accepted wherever the dependency is declared: on the app or a router, in the view’s dependencies, or in a class attribute. The key of an APIKeyQuery is accepted too. A project base view can set unknown_query_params once for every view.

Use the filters in a route of your own#

apply_filters applies the client’s filters to a statement, without the sort and the page. This route counts the items per category, under the client’s other filters:

@fr.get("/category-counts")
async def category_counts(self, list_params) -> dict[str, int]:
    await self.authorize(fr.Action.GET_MANY)
    stmt = sa.select(Item.category, sa.func.count()).group_by(Item.category)
    stmt = fr.apply_clauses(stmt, fr.resolve_scope(self))
    others = list_params.model_copy(update={"category": None})
    stmt = await self.apply_filters(stmt, others)
    return dict((await self.session.execute(stmt)).all())

The route calls authorize and applies the scope itself, as every read route of your own does; see Use the scope in your own query. It removes the client’s own category filter. Otherwise the result has only the category that the client chose.

Details

apply_list_params would add ORDER BY and LIMIT. PostgreSQL rejects an order on a column that the GROUP BY does not have.

A real route also removes category__in and the other keys of category. The route still documents page, page_size and sort, and ignores them.

A route method with a list_params parameter takes the same list params as GET /. Restly gives it the view’s schema_list_params, so filters, sort and page are read and checked in the same way, OpenAPI shows them, and an unknown key returns 422. A custom list route, such as a trash route, passes them on with self.handle_get_many(list_params, scope=...); see Read other rows on one route. The route can take its own query parameters next to list_params, as in def search(self, list_params, mode: SearchMode).

A route can also pass self.request.query_params on, in place of list_params. Restly reads the keys of the list params from it and leaves out the keys that the route reads, such as mode above. Any other key gets 422, as on the list route, or is left out when the view sets unknown_query_params = "ignore". OpenAPI does not show the list keys of such a route. A dict, as in view.get_many({"name": "axe"}), holds list params only, so a key that they do not have always gets 422.

On an async view, apply_filters is async def, as apply_list_params is. A view can override it to add a condition to every list, and to every route that calls it. apply_list_params calls it first. Add the condition to the statement before super().apply_filters(...). After it, a sort on a relationship that a filter joined joins that table a second time. A join that every list needs goes in an apply_list_params override. A route of your own that reads rows of the model starts from self.select(), which has the scope, as Use the scope in your own query shows.

Write the list params yourself#

For list params that Restly does not generate, such as one search key, or the keys of another client library, write them as a Pydantic model. Declare it as schema_list_params, and apply it to the statement in apply_filters and apply_list_params:

from typing import Literal


class ItemSearch(fr.query.PagedListParams):
    q: str | None = None
    order: Literal["name", "-name"] = "name"


class ItemView(fr.AsyncRestView):
    prefix = "/items"
    model = Item
    schema = ItemSchema
    schema_list_params = ItemSearch

    async def apply_filters(self, stmt, list_params: ItemSearch):
        if list_params.q:
            stmt = stmt.where(Item.name.icontains(list_params.q, autoescape=True))
        return stmt

    async def apply_list_params(self, stmt, list_params: ItemSearch):
        stmt = await self.apply_filters(stmt, list_params)
        name = Item.name.desc() if list_params.order == "-name" else Item.name
        offset = (list_params.page - 1) * list_params.page_size
        return stmt.order_by(name, Item.id).limit(list_params.page_size).offset(offset)

The client sends GET /items/?q=axe&order=-name&page=2. Restly reads the model from the query string, as on every list route: a bad value or an unknown key gets 422, and OpenAPI shows each field. The scope still applies, and total_count counts the rows that apply_filters keeps.

fr.query.PagedListParams gives the list params the page fields, page and page_size. Restly fills them from the view’s pagination: their names in the URL, the default page size and the bounds. Your code reads list_params.page and list_params.page_size, also when the pagination renames them, such as page_size_query_param="size". The response reports the page that the client gets.

List params of your own on a view with pages need these fields, or the view fails when it registers: its list would return every row, while its response says that it has pages. For a list without pages, subclass pydantic.BaseModel and set pagination = None.

Details

On a sync view, the two methods are def. apply_list_params adds the order and the page. Order by a unique column last, such as Item.id, so that a row never moves to another page. In a base view for several models, fr.query.order_by_primary_key adds the primary key of the view’s model.

One class of list params serves views with other pagination: each view gets its own page fields. Page fields that you write yourself also work, when they have the names of the view’s page parameters.

The list settings, such as include_filters, sort_query_param and default_sort, and fr.list_filter apply to the list params that Restly generates. A view with list params of its own that has one of them fails when it registers. For a default order in list params of your own, give their sort field a default, as order has here. A subclass with a new schema gets generated list params, so it declares its own again. The React Admin views have list params of their own: sort, range and filter.

Filter and sort on the fields of the response#

The list params of a client library often name the fields of the resource, as in filter={"category":"tools"}. For each such key, fr.query.FilterKey finds the field and its column. As in the generated filters, a field that the response does not show is an unknown key. Here it gets a 400, not a 422, because the key is inside the value of filter; see Why a bad list request returns 422 or 400. parse() gives a value the validation of the field and the form of the column, as the generated filters do. fr.query.SortKey does the same for a sort key:

from typing import Any

import pydantic


class ItemListParams(fr.query.PagedListParams):
    filter: pydantic.Json[dict[str, Any]] | None = None
    sort: str | None = None


class ItemView(fr.AsyncRestView):
    prefix = "/items"
    model = Item
    schema = ItemSchema
    schema_list_params = ItemListParams

    async def apply_filters(self, stmt, list_params: ItemListParams):
        for name, value in (list_params.filter or {}).items():
            key = fr.query.FilterKey(self.model, self.schema_response, name)
            stmt = stmt.where(key.column == key.parse(value))
        return stmt

    async def apply_list_params(self, stmt, list_params: ItemListParams):
        stmt = await self.apply_filters(stmt, list_params)
        sorted_on = []
        if list_params.sort:
            name = list_params.sort.removeprefix("-")
            key = fr.query.SortKey(self.model, self.schema_response, name)
            descending = list_params.sort.startswith("-")
            stmt = stmt.order_by(
                key.term(descending=descending, sort_nulls=self.sort_nulls)
            )
            sorted_on.append(key.column)
        stmt = fr.query.order_by_primary_key(stmt, self.model, sorted_on=sorted_on)
        offset = (list_params.page - 1) * list_params.page_size
        return stmt.limit(list_params.page_size).offset(offset)

The client sends GET /items/?filter={"category":"tools"}&sort=-name&page=2. The condition, such as ==, stays yours. As in the example above, the list params get the page fields from PagedListParams, and apply_list_params applies the page. order_by_primary_key() orders by the primary key last, so that a row never moves to another page; sorted_on names the column that the list is sorted on already, which does not come again. The React Admin views use these classes for their filter and sort.

Details

A key is the name of a field, or its alias when the field has one. The model has an attribute with the name of the field that is one value in SQL: a column, a column_property, or a hybrid property with an SQL expression. A hybrid property that is plain Python, such as self.deleted_at is not None, is an unknown key. A key with a dot, such as vendor.name, needs a join, so these classes do not know it.

parse() takes text from a URL or a value from JSON, and a number becomes text for a text field. The validators of the field get only this field, so info.data is {}; see Filter values go through the field’s validation. None stays None when the field allows it, so key.column == None gives IS NULL. A value that fails gets a 400 with the reason. term() puts NULLs where sort_nulls says.

Restly cannot see a value that a serializer hides, such as an email shown as a***@example.com. A filter or a sort on such a field would let a client find out the value. Type such a field as a subclass of pydantic.Secret with its own _display(), so it is no key, as a SecretStr is not.

Internals#

How Restly builds the list params#

In before_include_view(), Restly sets one class attribute, schema_list_params. It is a Pydantic model that derive_schema_list_params(cls.schema_response, cls.model, pagination=cls.pagination) generates, with the view’s list settings. It holds the pagination parameters, the sort parameter, the filter keys that the settings keep, and the view’s own keys. Restly builds it once, when the view registers.

A route reads the list params through a dependency, not as a FastAPI query model. FastAPI splits a query model into separate parameters only when it is the only query parameter of the route. Next to a dependency that reads ?api_key=, the filters would become one required object. So Restly validates the model from the query string itself, and shows each key as its own parameter in OpenAPI, next to the other parameters of the route.

Apart from __isnull, the generated filter values are text in this model. Restly validates them against the field when apply_list_params builds the query. A key of your own has the type of its method’s value.

See also#