Getting filtered data from database

[saeedesmaili]

First, thanks for this great tutorial. With this async approach, how is it possible to get filtered data from the database (using sqlalchemy and sqlmodel)? SQLModel's documentations suggest using where, but it doesn't work for async approach and they don't have any documentation for this yet. I tried the following, but didn't work:

@app.get("/songs/{id}", response_model=list[Song])
def get_song(id: int = Path(..., gt=0), session: Session = Depends(get_session)):
    result = session.execute(select(Song).where(Song.id == id))
    songs = result.scalars().all()
    return [Song(name=song.name, artist=song.artist, id=song.id) for song in songs]

[mtucker502]

I am doing the following and it is working:

    async def get_multi(
        self,
        session: Session,
        *,
        offset: int = 0,
        limit: int = 100,
        filter_by: UpdateSchemaType = SQLModel()
    ) -> List[ModelType]:
        results = await session.execute(
            select(self.model)
            .filter_by(**filter_by.dict(exclude_defaults=True))
            .offset(offset)
            .limit(limit)
        )
        return results.scalars().all()

Basically I've added the update model (since all of it's attributes are optional) as a Depends in the route. This allows for every field of the update model to be used as a parameter. I then pass that to my crud functions as shown above.