SQLAlchemyでサブクエリで取得したカラムで絞り込めない

Question

前提・実現したいこと

テーブルの名前毎の最新データを取得したいです。
これよりもいい実装があればクエリ自体は変わっても問題ありません。
linesテーブルのもとからあるカラム(id, name等)であればlatest_linesのカラムが使用されるのですが、maxなどの関数を使用して取得したデータがうまく使用されないという状況です。

発生している問題・エラーメッセージ

pythonでlatest_lines.created_atと書いている部分が、なぜか実際のSQLではlinesのcreated_atに置き換わっています。
なので実際には最新ではないデータが返却されます。

from sqlalchemy import desc, func
from sqlalchemy.orm import aliased

from app.database import session
from app.models import Line

latest_lines = aliased(
    Line,
    session.query(
        Line.name.label("name"),
        func.max(Line.created_at).label("created_at"),
    )
    .group_by(Line.name)
    .subquery("latest_lines"),
)

lines = (
    session.query(Line, latest_lines)
    .filter(Line.name == latest_lines.name)
    .filter(Line.created_at == latest_lines.created_at)
    .group_by(Line.name)
    .order_by(desc(Line.created_at))
    .all()
)

↓実際に発行されているSQL(必要ないselectのカラムは削除してあります)

SELECT
    `lines`.id AS lines_id,
    `lines`.name AS lines_name,
    `lines`.created_at AS lines_created_at,
    latest_lines.name AS latest_lines_name
FROM
    `lines`,
    (
        SELECT
            `lines`.name AS name,
            max(`lines`.created_at) AS created_at
        FROM
            `lines`
        GROUP BY
            `lines`.name
    ) AS latest_lines
WHERE
    `lines`.name = latest_lines.name
AND `lines`.created_at = `lines`.created_at
GROUP BY
    `lines`.name

補足情報（FW/ツールのバージョンなど）

Python 3.9.7
SQLAlchemy 1.4.23
MySQL 5.7.35

理想は以下のようなクエリを発行したいのですがjoinのサブクエリの記述方法がいまいちわからず上記のような書き方になっています。

select
    id,
    `lines`.name,
    `lines`.created_at
from
    `lines`
    join
        (
            select
                name,
                max(created_at) as created_at
            from
                `lines`
            group by
                name
        ) as `latest_lines`
    on  `lines`.name = latest_lines.name
    and `lines`.created_at = latest_lines.created_at
group by
    `lines`.name
order by
    `lines`.name

Answer 1

バグでしょうけど、つまらないバグを回避するためにも、もう少しわかり易いネーミングにすることも考えては？

                max(created_at) as created_at
                                    ↓
                max(created_at) as max_at

    and `lines`.created_at = latest_lines.created_at                                    
                                           ↓
    and `lines`.created_at = latest_lines.max_at

動作確認はしていません。

Comments

[fuuga]

回答ありがとうございます。
名前を変えて試してみましたが、Python側でエラーが発生してしまいます。
```
Traceback (most recent call last):
File "/usr/src/app/test.py", line 37, in <module>
.filter(Line.created_at == latest_lines.max_at)
File "/usr/src/app/.venv/lib/python3.9/site-packages/sqlalchemy/orm/util.py", line 546, in __getattr__
attr = getattr(target, key)
AttributeError: type object 'Line' has no attribute 'max_at'
```

[Orlofsky]

SQLAlchemy ってSQLのスキルがなくても使えるってのがウリだけど、なかなかバグが解消しないから、使わないって選択肢もあるのでは？

[fuuga]

なるほど、ありがとうございます。
ただPythonのORMでは一番良さそうなのと、今やってるプロジェクト全体で使用しているのでできればSQLAlchemyで実装できればと思っている次第です。

Answer 2

解決できました。
原因はaliased関数の使い方を間違えていたようです。
第一引数にモデルを渡していたのでそちらが使用されていたようです。
どこかで見た記事ではそのようになっていたので勘違いしておりました。

from sqlalchemy import desc, func
from sqlalchemy.orm import aliased

from app.database import session
from app.models import Line

latest_lines = aliased(
    session.query(
        Line.name.label("name"),
        func.max(Line.created_at).label("created_at"),
    )
    .group_by(Line.name)
    .subquery(),
    name="latest_lines",
)

lines = (
    session.query(Line, latest_lines)
    .filter(Line.name == latest_lines.c.name)
    .filter(Line.created_at == latest_lines.c.created_at)
    .group_by(Line.name)
    .order_by(desc(Line.created_at))
    .all()
)