equivalent for .subquery('t2') sqlalchemy

[movaldivia]

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

subq = session.exec(select(User.user_id, func.max(User.created_at).label( 'maxdate')) .group_by(User.user_id).subquery('t2')) query = session.exec(select(User).join(subq, and_( User.user_id == subq.c.user_id, User.created_at == subq.c.maxdate))).all()

Description

Error when trying to create a subquery

Executable SQL or text() construct expected, got <sqlalchemy.sql.selectable.Subquery at 0x7f5cac0da990; t2>.

trying this use case: https://stackoverflow.com/questions/45775724/sqlalchemy-group-by-and-return-max-date

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.4

Python Version

3.7.9

Additional Context

No response

[alhoo]

You could try to execute the two queries in one exec call

 subq = ( select(User.user_id, func.max(User.created_at).label("maxdate")) .group_by(User.user_id) .subquery("t2") ) query = session.exec( select(User).join( subq, and_(User.user_id == subq.c.user_id, User.created_at == subq.c.maxdate), ) ).all() 

[YuriiMotov]

Runnable code example:

from datetime import datetime from sqlalchemy import and_, func from sqlmodel import Field, Session, SQLModel, create_engine, select sqlite_file_name = "database.db" sqlite_url = f"sqlite:///{sqlite_file_name}" engine = create_engine(sqlite_url, echo=True) class User(SQLModel, table=True): id: int = Field(primary_key=True) user_id: int created_at: datetime if __name__ == "__main__": SQLModel.metadata.create_all(engine) with Session(engine) as session: subq = ( select(User.user_id, func.max(User.created_at).label("maxdate")) .group_by(User.user_id) .subquery("t2") ) query = session.exec( select(User).join( subq, and_(User.user_id == subq.c.user_id, User.created_at == subq.c.maxdate), ) ).all() subq = ( select(User.user_id, func.max(User.created_at).label("maxdate")) .group_by(User.user_id) .subquery("t2") ) query = session.exec( select(User).join( subq, and_(User.user_id == subq.c.user_id, User.created_at == subq.c.maxdate), ) ).all()

SELECT user.id, user.user_id, user.created_at FROM user JOIN ( SELECT user.user_id AS user_id, max(user.created_at) AS maxdate FROM user GROUP BY user.user_id ) AS t2 ON user.user_id = t2.user_id AND user.created_at = t2.maxdate