Add Automatic Filters for Auto Generated Endpoints

[AndreGuerra123]

Is your feature request related to a problem? Please describe.
I would like to be able to filter get_multi endpoint.

Describe the solution you'd like
I want to be filtering and searching in the get_multi endpoint. This is such a common feature in most CRUD system that I consider relevant. This is also available in flask-muck.

Describe alternatives you've considered
Some out-of-the-box solution are out there:
https://github.com/arthurio/fastapi-filter (sqlalchemy + mongo)
https://github.com/OleksandrZhydyk/FastAPI-SQLAlchemy-Filters (specific sqlalchemy)

Additional context
Add any other context or screenshots about the feature request here.
Ifnot being implemented could an example be provided?

[igorbenav]

@dtiesling do you want to do it?

[dtiesling]

Sure! If I can write it for flask-muck I’m sure I can get it working for fastcrud. Assign it to me and I’ll check it out this week.

[dtiesling]

I don't think I'm following this ticket. I thought the request was to add filtering to get_multi but the docs say that is already supported.

relevant docstring

[igorbenav]

I believe it's for the get_multi endpoint, not the method. Can you please clarify it @AndreGuerra123?

[igorbenav]

Relevant code here and here

[dtiesling]

OIC I missed that it wasn't implemented at the endpoint level. This will be an interesting way to learn FastAPI.

@igorbenav do you have any opinions on the schema for sending filters? In Flask-Muck I have it as json-encoded object in the filters query param. You can see what that looks like here.

Another option is to try dynamically building a list of all possible filters for a model and adding those to the endpoint.

It also looks simple to grab all the parameters off the raw request object and use those to generate the arbitrary filters but then they wouldn't be documented in the OpenAPI spec.

[igorbenav]

Hmm maybe combining json-encoded objects for complex filters, but still dynamically building the simple filters by column is a good balance between simplicity and flexibility.

Another possibility is actually creating all possible (even complex) filters based on the column type (numeric or date alow gt, gte etc). This would be complete chaos when there are too many filters though.

Either way, I think it would be best to let the user have some control over it, having the option of no filters, simple filters only or combined for example. What do you think?

[AndreGuerra123]

believe it's for the get_multi endpoint, not the method. Can you please clarify it @AndreGuerra123?

From my understanding (I could be totally wrong), the get_multi method is used by the endpoint. I assume that if there are some filters defined in the method it works. However, there are several caveats some specific to the get_multi method, others to the filtering strategy itself:

• Regarding the method, only supports "ands" and exact matches because uses the kwargs to filter in the inner crud operation. I think most other CRUD creators support more filtering strategies such as le,gt and support dates.
• Allowing "referenced entity" filtering. I don't know how without a join strategy. However I've seem it as well in a similar project.
• The CRUD get_ multi_by_cursor pagination only seem to sort by one element the sorted filter and it has to be "id" to work properly as non-unique filter fail. I think and effort to decouple the pagination from filtering and sorting should be done. Maybe unite both queries.

Recommendation from what I feel it's intuitive:

• Use fastapi-filter to have all kind of support for filtering. The default could be at an initial stage no filter, however other CRUD creators automatically support filter for string, datetime and numeric out-of-the-box.
• Allow user user to specify they "FilterDepends", basically what can you filter with. Use the same package sorting mechanism as is intuitive, but doesn't seem a prio.
• (This maybe just me being naive, but can we include selectively just the get_multi endpoint? I suggest to call it list as well to be honest).

Hope I have helped. If you need the refs I found from other projects I will gladly add them here. Just not want to contaminate your project with external links.

Regards,

[dtiesling]

I had a little time to look into this tonight. Having the filters come in as a json blob makes the whole thing easier from an implementation standpoint because there is a static query param argument that we can type hint.

If the preference is each filter is its own query param I'm leaning toward an approach where the query params are read directly from the request object. To handle the issue of the openapi spec I think there could be a helper that generates all the openapi parameters and passes it to the openapi_extra kwarg in the call to add_api_route. I would have to test to see how that openapi_extra plays with the spec FastAPI is already creating

[igorbenav]

Cool, @dtiesling, go ahead and update as you go please

[igorbenav]

Hey, @dtiesling, any news?

[dtiesling]

Sorry @igorbenav Ive had a ton of stuff come up lately and haven’t been able to get to this. You should pull me off of this ticket and I’ll update here if I free up and can make more movement on it.

[igorbenav]

Sure

[samuel-favarin-dbc]

Hi folks, I'm in the same scenario. I would like to filter or add a WHERE clause in this get_multi operation. Do we have any news about this update? Thanks!

[igorbenav]

@samuel-favarin-dbc if you are talking about FastCRUD's get_multi method, you can already do this. You just need to pass the filters as kwargs, documentation here.

Ex: get all users with name "John":

crud = FastCRUD(User)

crud.get_multi(
    db=db,
    name="John"
)

It also supports filters like greater than, greater than or equal etc. Docs here.

If you are talking about filters in the auto generated get_multi endpoint, I need to do some stuff before.