[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)