SQLAlchemy scalars and intersect anomaly

Question

The SQLAlchemy manual says* that the Session.scalars() method returns ORM objects.

*[Selecting ORM Entries][1]

The following code shows two examples one of which returns an ORM object but the other does not. The first uses a select statement which selects a single ORM object. The second example does not return an ORM object. It is identical except for the introduction of SQLAlchemy’s intersect() function. It is only returning the first column of the desired object.

Although it is possible to select the primary keys of records and then carry out a second select for ORM objects that seems like a kludge. Is there a more elegant solution?

from sqlalchemy import create_engine, select, intersect
from sqlalchemy.orm import Mapped, mapped_column, DeclarativeBase, Session


class Base(DeclarativeBase):
    pass


class Movie(Base):
    __tablename__ = "movie"

    title: Mapped[str]
    id: Mapped[int] = mapped_column(primary_key=True)

    def __repr__(self):
        return (
            f"{self.__class__.__qualname__}("
            f"title={self.title!r}, "
            f"id={self.id!r})"
        )


engine = create_engine("sqlite+pysqlite:///:memory:")
Base.metadata.create_all(engine)
with Session(engine) as session:
    movie_1 = Movie(title="Great Movie 1")
    movie_2 = Movie(title="Great Movie 2")
    session.add_all((movie_1, movie_2))

    statement = select(Movie).where(Movie.title == "Great Movie 1")
    print("\n", statement)
    result = session.scalars(statement).all()
    print(f"{result=}")

    stmt_isec = intersect(statement)
    # In case you're wondering, the next line has the same effect as 
    # the unary intersect.
    # stmt_isec = intersect(*[statement, statement])
    print("\n", stmt_isec)
    result = session.scalars(stmt_isec).all()
    print(f"{result=}")

Output:

SELECT movie.title, movie.id 
FROM movie 
WHERE movie.title = :title_1
result=[Movie(title='Great Movie 1', id=1)]

SELECT movie.title, movie.id 
FROM movie 
WHERE movie.title = :title_1 INTERSECT SELECT movie.title, movie.id 
FROM movie 
WHERE movie.title = :title_1
result=['Great Movie 1']

Use Case

Consider a five-column table. The end user is provided with a search form with five fields for entering search criteria for each of the five columns. The user enters search criteria for fields one and four, intending that all records which match those criteria will be returned.

Constructing the select statement is straightforward. The problem is that with five columns, there are 120 (5 factorial) different statements that would be required.

The solution is to construct a single select statement for each column that is present in the user’s search. These are then combined by the SQLAlchemy intersect function. In our example, a select statement would be created for columns one and four but not for two, three, or five. These two become the arguments for the intersect function.

Answer 1

Use from_statement like this to get ORM objects:

statement = select(Movie).where(Movie.title == "Great Movie 1")
stmt_isec = intersect(statement, statement)
session.scalars(select(Movie).from_statement(stmt_isec)).all()

Docs: selecting-entities-from-unions-and-other-set-operations