SQLAlchemy correlated subquery TypeError: Boolean value of this clause is not defined

Question

I need to write a Python SQLAlchemy code converting this SQL query, but I cannot find a meaningful way to perform this query in SQL Alchemy.

SELECT *
FROM alpha AS X
WHERE X.index = (
    SELECT
        MAX(index)
    FROM
        alpha AS Y
    WHERE
        X.question = Y.question
        AND X.person = Y.person
)

I've tried like this but I keep getting the error TypeError: Boolean value of this clause is not defined (alpha is a Table).

from sqlalchemy.orm import aliased
from sqlalchemy import func, and_
X = aliased(alpha)
Y = aliased(alpha)
conn.execute(
    select(X).where(
        X.c.index
        == select(func.max(X.c.index))
        .where(
            and_(
                X.c.question == Y.c.question,
                X.c.person == Y.c.person,

            )
        )
        .correlate(X)
    )
)

What am I doing wrong here?

Answer 1

I think you really just need to use scalar_subquery(). As I understand it the correlate will happen automatically, you only need correlate if SQLAlchemy can't tell what you meant.

Code

alpha = Table("alpha", metadata,
              Column("id", Integer, primary_key=True),
              Column("question", String),
              Column("person", String),
              Column("index", Integer),)



Base.metadata.create_all(engine)

with Session(engine) as session:
    X = aliased(alpha, name='X')
    Y = aliased(alpha, name='Y')

    q = select(X).where(
        X.c.index
        == select(func.max(X.c.index))
        .where(
            and_(
                X.c.question == Y.c.question,
                X.c.person == Y.c.person,

            )
        ).scalar_subquery()
    )
    print (q)

SQL

SELECT "X".id, "X".question, "X".person, "X".index 
FROM alpha AS "X" 
WHERE "X".index = (SELECT max("X".index) AS max_1 
FROM alpha AS "Y" 
WHERE "X".question = "Y".question AND "X".person = "Y".person)

This is with Postgresql and SQLAlchemy 1.4.31.