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()