Where with multiple expressions - Can we use a dict ?

[legalla]

First Check

• I added a very descriptive title to this issue.
• I used the GitHub search to find a similar issue and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

@api.get("/devices", response_model=schemas.DeviceDisplay)
def get_devices(os_type: Optional[schemas.OsTypes] = None,
                discovered: Optional[schemas.Discovered] = None,
                device_site: Optional[str]
                device_status: Optional[schemas.DevicesStatus]
                db: Session = Depends(dependencies.get_session),
                api_key: APIKey = Depends(dependencies.get_api_key)):
    filters_items = {}
    if os_type.value: filters_items['platform'] = os_type.value 
    if discovered.value: filters_items['discovered'] = discovered.value 
    if device_site: filters_items['site'] = device_site 
    if device_status: filters_items['status'] = device_status 
    statement = select(sql_models.devices).where(**filters_items)
    request_exec = db.exec(statement)
    result = request_exec.all()
    return result

Description

I'm using FastAPI and just installed SQLModel to test it.

For this use case (GET request), end user should be able to select one or multiple options which would be used to filter entries to return (Like Site or OS Type). Each field is optional, and I can't strictly determine/know in advance which item will have to be used as a WHERE CLAUSE.
In the same way, if end user let all field to Default (None/Null), the where clause will be used in the request, but with no filters.

So, as I'm doing with SQLAlchemy, I can create a dictionary and push it into my request, and it will automatically take the dictionary key and transform them in WHERE Clauses through the filter_by function.
But I didn't find a way to the same with SQLModel. it seems that we have to "statically" specify which items we want to use as filters.

Am I missing something ?
If not, how can we do the job with SQLModel ?

Here is the error message I have :
Exception:..... TypeError: where() got an unexpected keyword argument 'platform'
(Which seems to be normal, as we can't use keywords argument in a where, as explained in the documentation)

Thanks a lot for your support,

Operating System

Linux

Operating System Details

FastAPI runs into a Docker container.

SQLModel Version

0.0.3

Python Version

3.8.6

Additional Context

No response

[thexcw]

You can do it the same way you did before. Try this:
statement = select(sql_models.devices).filter_by(**filters_items)

https://sqlmodel.tiangolo.com/features/#based-on-sqlalchemy

[legalla]

Indeed, I missed that part :-)
Tested, and it works perfectly.

I close this issue.

Thanks again !

[johnatr]

what if you need to set various operators and not just an eq? ex. >, <, IN, etc ?