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