[Feedback Wanted] Query / Filter Parameters

[awtkns]

In an upcoming version of CRUDRouter I am hoping to add Query / Filter support. I've created this thread in hopes of getting some community feedback on how you guys would like the query params constructed.

• Ability to filter column(s) eg: /potato?color=red or /potato?color=red&?type=small
• Ability to filter multiple values in the same column eg: /potato?color=red&color=blue
• Ability to order by columns
• etc...

Please feel free to make any suggestions 😄

[marcoaaguiar]

I skimmed through #61 I haven't seen anything related to ordering/sorting. Is it expected to 0.8?

[awtkns]

Hi @marcoaaguiar. Currently it only does filtering but I am looking at adding more feature before the merge (eg: order by and perhaps "like filtering" as currently it only does exact matches).

The tricky thing is that not exactly sure what the preferred query parameter syntax would be amongst the community.

eg: /potato?color=red&order_by=mass&order=DESC

Any thoughts?

[marcoaaguiar]

Hi Adam, I recently had to hack this package to allow for sorting. I chose Github query params as a reference, which are sort and direction.
Regarding sort vs. order_by, I find both good choices, sort conforms in Python lingo, order_by with SQL's.
Regarding direction (asc and desc), I really like it because it brings no confusion when paired with sort/order_by.

[awtkns]

I took a look into how GitHub (and others do it) and I think it would make sense to go with sort and direction.

As for "like" filtering, I might make sense to include that in a later version to get some feedback.

[Chise1]

My project has this function,may be can give you some idea?

import inspect
from typing import Callable, Optional, Tuple, Type

from fastapi import APIRouter, Depends
from pydantic.main import BaseModel
from tortoise import Model
from tortoise.contrib.pydantic import pydantic_model_creator
from tortoise.query_utils import Q

from fast_tmp.depends.auth import get_user_has_perms
from fast_tmp.utils.pydantic_creator import pydantic_offsetlimit_creator


def add_filter(func: Callable, filters: Optional[Tuple[str, ...]] = None):
    signature = inspect.signature(func)
    res = []
    for k, v in signature.parameters.items():
        if k == "kwargs":
            continue
        res.append(v)
    if filters:
        for filter_ in filters:
            res.append(
                inspect.Parameter(
                    filter_, kind=inspect.Parameter.KEYWORD_ONLY, annotation=str, default="__null__"
                )
            )
    # noinspection Mypy,PyArgumentList
    func.__signature__ = inspect.Signature(parameters=res, __validate_parameters__=False)


def create_pydantic_schema(
    model: Type[Model],
    name: str,
    fields: Optional[Tuple[str, ...]] = None,
    exclude_readonly: bool = False,
) -> Type[BaseModel]:
    if fields:
        return pydantic_model_creator(
            model, name=name, include=fields, exclude_readonly=exclude_readonly
        )
    else:
        return pydantic_model_creator(model, name=name, exclude_readonly=exclude_readonly)

def create_list_route_with_page(
    route: APIRouter,
    path: str,
    model: Type[Model],
    fields: Optional[Tuple[str, ...]] = None,
    codenames: Optional[Tuple[str, ...]] = None,
    searchs: Optional[Tuple[str, ...]] = None,
    filters: Optional[Tuple[str, ...]] = None,
    res_pydantic_model: Optional[Type[BaseModel]] = None,
    random_str: str = "",
):
    """
    创建list的路由
    """
    if res_pydantic_model:
        schema = res_pydantic_model
    else:
        schema = create_pydantic_schema(
            model,
            f"CREATORList{model.__name__}{path.replace('/', '_')}Page{random_str}",
            fields=fields,
        )
    paging_schema = pydantic_offsetlimit_creator(schema)

    async def model_list(
        offset: int = 0,
        limit: int = 10,
        search: Optional[str] = None,
        **kwargs,
    ):
        count = model.all()
        query = model.all().limit(limit).offset(offset)
        if search and searchs:
            x = [Q(**{f"{i}__contains": search}) for i in searchs]
            if x:
                q = x[0]
                for i in x[1:]:
                    q = q | i
                query = query.filter(q)
                count = count.filter(q)
        if kwargs:
            s = {}
            for k, v in kwargs.items():
                if not v == "__null__":
                    s[k] = v
                else:
                    pass
            if s:
                query = query.filter(**s)
                count = count.filter(**s)

        data = await query
        return paging_schema(total=await count.count(), data=[schema.from_orm(i) for i in data])

    add_filter(model_list, filters)
    route.get(
        path, dependencies=[Depends(get_user_has_perms(codenames))], response_model=paging_schema
    )(model_list)

[Faridalim]

Hi, i think this feature need more push. Your idea is very good and pretty simple. Maybe we can add something like

/potato?color=eq.red&color=eq.blue&price=gte.50

we can start from eq or equal and add more operators in the future.

@awtkns what do you think?

[jpuerta]

Not sure if this has already been discussed, but why not adopting the Django filter() syntax? It provides a comprehensive pseudo-language for covering the whole SQL WHERE clause syntax (including querying nested model attributes) and, given that some of the underlying ORMs already rely on it (e.g.: ormar, Tortoise), it might potentially allow for an straightforward implementation:

URL	Python
/potato?color=red	Potato.objects.filter(**{'color': 'red'})
/potato?color__iexact=ReD	Potato.objects.filter(**{'color_iexact': 'ReD'})
/potato?price__lte=50	Potato.objects.filter(**{'price__lte': '50'})
/potato?producer__country__in=CN,IN	Potato.objects.filter(**{'producer__country__in': ['CN', 'IN']})

If that syntax is too harsh, we could go for a friendlier refinement (everyone loves brackets and dots):

URL	Python
/potato?color[iexact]=ReD	Potato.objects.filter(**{'color_iexact': 'ReD'})
/potato?price[lte]=50	Potato.objects.filter(**{'price__lte': '50'})
/potato?producer.country[in]=CN,IN	Potato.objects.filter(**{'producer__country__in': ['CN', 'IN']})

And those query strings could be easily parsed from/to JS/Python objects (it might be useful for complex queries):

{
  "producer": {
    "country": {
      "in": ["CN", "IN"]
    }
  }
}

And same for the sorting, why having 2 separate fields, when the Django way is more compact (2 reserved field names vs. 1, out of which direction is a pretty commonly used word) and works smoothly with multi-column sorting?

URL	Python
/potato?ordery_by=-price,producer.country	Potato.objects.order_by(*('-price', 'producer__country'))

[karolzlot]

I suggest adopting syntax from react-admin:

https://github.com/marmelab/react-admin/tree/master/packages/ra-data-simple-rest#rest-dialect

(but feel free to ignore it, as it is possible to supply react-admin with a definition of custom syntax)

---

Here is a long list of other syntaxes currently supported by react-admin. Please take a look, I think it may help to choose the best approach:

https://marmelab.com/react-admin/DataProviders.html#available-providers

(some are not REST, but many are)

[jidn]

Lots of good ideas; each with a different use case. What about callback/hook/plugin for filtering? This would allow for easily customizable filters with that operator you "just can't live without" that doesn't require forking in order to make it work. For example a sequential, functional less-than-or-equal that also provides a between operator filter=and(active,le(2021-01-01,created_on,2021-06-30))

The same could be said of sorting, but I am a fan of sort=group,-created_on with the minus prefix for descending order.

[theultimate1]

Is this part of the current release?

[awtkns]

@theultimate1 not yet. However a preview can be found in #61. It is still a WIP so no guarantees on correctness.

[theultimate1]

@theultimate1 not yet. However a preview can be found in #61. It is still a WIP so no guarantees on correctness.

I tried the #61 and I got some conflicts so I guess I will just wait until it comes in the final release.

[fsecada01]

Is there any movement on this feature enhancement?

[Zaffer]

OData is the way to go: https://www.odata.org/documentation/
It is used by MS Graph is robust for REST and OpenAPI, also ISO approved.

[dclimber]

@awtkns is there a way to use dependencies for filtering?

For example pass a dependency into get_all_route and somehow use it's result?

[AlexAdamenko]

Is there any movement on this feature enhancement?

@fsecada01

In the meantime, what's the workaround for filtering if you are still using this library?

[br3ndonland]

@awtkns thanks for considering filter support. It would be a helpful feature. I've been interested in using fastapi-crudrouter, but the lack of ability to customize the SELECT statement has held me back from doing more with it.

I think this is an important point (#62 (comment)):

Lots of good ideas; each with a different use case. What about callback/hook/plugin for filtering? This would allow for easily customizable filters with that operator you "just can't live without" that doesn't require forking in order to make it work.

It's going to be difficult to meet every use case, but fastapi-crudrouter could provide some default filters while also allowing developers to customize the SELECT statement prepared by the route. In terms of how to implement a custom callback here, I think FastAPI dependencies would be the way to go. Dependencies get automatic access to the endpoint function's arguments, including the query params, and the query params can be parsed to set up filters. This matches up nicely with the "generative" style select() construct in SQLAlchemy 1.4/2.0. Methods on the select() instance can be repeatedly called to build up a statement before sending it to the database.

The implementation could look something like fastapi-filter or fastapi-filters (or could simply rely on one of those as an optional dependency). This example from the fastapi-filters README could help. The inner call to select() sets up the default statement - just the model corresponding to the API endpoint. The outer call to apply_filters is where fastapi-filters reads the query params, matches them up against its supported filters, and uses those filters to update the default statement in generative style.

from fastapi_filters.ext.sqlalchemy import apply_filters


@app.get("/users")
async def get_users(
    db: AsyncSession = Depends(get_db),
    filters: FilterValues = Depends(create_filters_from_model(UserOut)),
) -> List[UserOut]:
    query = apply_filters(select(UserOut), filters)
    return (await db.scalars(query)).all()

fastapi-crudrouter already has a nice API for dependencies. Do you think fastapi-crudrouter could use dependencies to set up filters also? I think it would be pretty cool! Happy to help implement it!

[AndRosis]

Hi, is filtering functionality implemented? Any plans on that if not?