filter on computed field

[gab23r]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question 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

from typing import Optional
from datetime import datetime

from sqlmodel import Field, Session, SQLModel, create_engine, func, col


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    start: datetime = Field(default_factory=datetime.utcnow)


sqlite_file_name = "testdatabase.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


def create_heroes():  
    hero_1 = Hero(name="Deadpond")  
    hero_2 = Hero(name="Deadpond")
    hero_3 = Hero(name="Deadpond")

    with Session(engine) as session:  
        session.add(hero_1)  
        session.add(hero_2)
        session.add(hero_3)

        session.commit()  
    
def select_last_heroes_over():

    with Session(engine) as session:  
        subquery = session.query(
            Hero,
            func.rank().over(
                order_by=col(Hero.start).desc(),
                partition_by=Hero.name
            ).label('rnk')
        ).subquery()

        query = session.query(subquery).filter(
            subquery.c.rnk==1
        )
    return session.exec(query).first()

def main():  
    create_db_and_tables()  
    create_heroes()  
    last_result = select_last_heroes_over()
    print("last added hero is ", last_result)
    assert last_result[0] == 3 # works


if __name__ == "__main__":  
    main()

Description

I need to do exactly the same as, compute a rank by some column and filter on it:
https://stackoverflow.com/questions/40635099/convert-rank-and-partition-query-to-sqlalchemy

If I tried the same thing with sqlmodel its works but I have plenty of DeprecationWarning, so they might be something wrong.
I got this:

[/var/folders/zw/r9r5yqys72q8_803tr3_ps0m0000gn/T/ipykernel_50754/3353154533.py:38](https://file+.vscode-resource.vscode-cdn.net/var/folders/zw/r9r5yqys72q8_803tr3_ps0m0000gn/T/ipykernel_50754/3353154533.py:38): DeprecationWarning: 
        🚨 You probably want to use `session.exec()` instead of `session.query()`.

        `session.exec()` is SQLModel's own short version with increased type
        annotations.

        Or otherwise you might want to use `session.execute()` instead of
        `session.query()`.
        
  subquery = session.query(

What is the way to do it with sqlmodel ?
Bonus: In the code last_result is a tuple, is it possible to get a SQLModel back with extra column rank ?
Thank you a lot,

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.14

Python Version

3.11

Additional Context

No response

[YuriiMotov]

Sorry, but it looks like you are looking for free labor..
I would recommend reading the docs: https://sqlmodel.tiangolo.com/tutorial/
If you still have questions - please ask more concrete questions