SQLAlchemy: Cursor pagination doesn't work with union queries

[JulianJvn]

Summary

Cursor pagination doesn't seem to work with union queries, in contrast to non-cursor pagination.

Steps to Reproduce

Run this Python script:

from fastapi_pagination import Params
from fastapi_pagination.cursor import CursorParams
from fastapi_pagination.ext.sqlalchemy import paginate
from sqlalchemy import Column, Integer, String, create_engine, select
from sqlalchemy.orm import Session, declarative_base

Base = declarative_base()


class TableA(Base):  # type: ignore[misc,valid-type]
    __tablename__ = "table_a"
    id = Column(Integer, primary_key=True)
    name = Column(String)


class TableB(Base):  # type: ignore[misc,valid-type]
    __tablename__ = "table_b"
    id = Column(Integer, primary_key=True)
    name = Column(String)


def main() -> None:
    engine = create_engine("sqlite:///:memory:")
    Base.metadata.create_all(engine)
    with Session(engine) as session, session.begin():
        session.add_all([TableA(name=f"NameA{i}") for i in range(1, 6)])
        session.add_all([TableB(name=f"NameB{i}") for i in range(1, 6)])
    query = select(TableA.id, TableA.name).union_all(select(TableB.id, TableB.name)).order_by("id")
    with Session(engine) as session:
        paginate(session, query, params=Params())  # works
        paginate(session, query, params=CursorParams())  # doesn't work


if __name__ == "__main__":
    main()

Expected Behavior

The script doesn't raise an exception and cursor pagination works with SQLAlchemy union queries.

Actual Behavior

The line marked # doesn't work raises an exception:

Traceback (most recent call last):
  File ".venv\Lib\site-packages\sqlakeyset\paging.py", line 204, in prepare_paging
    column_descriptions = q.column_descriptions
                          ^^^^^^^^^^^^^^^^^^^^^
AttributeError: 'CompoundSelect' object has no attribute 'column_descriptions'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "test.py", line 35, in <module>
    main()
    ~~~~^^
  File "test.py", line 31, in main
    paginate(session, query, params=CursorParams())  # doesn't work
    ~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File ".venv\Lib\site-packages\fastapi_pagination\ext\sqlalchemy.py", line 450, in paginate
    return run_sync_flow(
        _sqlalchemy_flow(
    ...<11 lines>...
        ),
    )
  File ".venv\Lib\site-packages\fastapi_pagination\flow.py", line 56, in run_sync_flow
    res = gen.throw(exc)
  File ".venv\Lib\site-packages\fastapi_pagination\flow.py", line 51, in run_sync_flow
    res = gen.send(res)
  File ".venv\Lib\site-packages\fastapi_pagination\ext\sqlalchemy.py", line 322, in _sqlalchemy_flow
    page = yield from generic_flow(
           ^^^^^^^^^^^^^^^^^^^^^^^^
    ...<10 lines>...
    )
    ^
  File ".venv\Lib\site-packages\fastapi_pagination\flows.py", line 122, in generic_flow
    items, more_data = yield from cursor_flow(raw_params)
                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File ".venv\Lib\site-packages\fastapi_pagination\ext\sqlalchemy.py", line 285, in _cursor_flow
    page = yield _call(
                 ~~~~~^
        conn,  # type: ignore[arg-type]
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    ...<2 lines>...
        page=raw_params.cursor,  # type: ignore[arg-type]
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File ".venv\Lib\site-packages\sqlakeyset\paging.py", line 442, in select_page
    return core_get_page(session, selectable, per_page, place, backwards, unique=unique)
  File ".venv\Lib\site-packages\sqlakeyset\paging.py", line 303, in core_get_page
    sel = prepare_paging(
        q=selectable,
    ...<4 lines>...
        dialect=get_bind(q=selectable, s=s).dialect,
    )
  File ".venv\Lib\site-packages\sqlakeyset\paging.py", line 206, in prepare_paging
    column_descriptions = q._raw_columns  # type: ignore
                          ^^^^^^^^^^^^^^
AttributeError: 'CompoundSelect' object has no attribute '_raw_columns'

Additional Information

• The query parameter of fastapi_pagination.ext.sqlalchemy.(a)paginate() is annotated as Selectable or SelectableOrQueries, which are type aliases: https://github.com/uriyyo/fastapi-pagination/blob/0556f17466acc222e0bb477886f4abb8f4bcac71/fastapi_pagination/ext/sqlalchemy.py#L86-L87 SQLAlchemy union queries are CompoundSelect instances. So (a)paginate() says it supports union queries. Therefore, mypy doesn't show any issues when run on the above script.
• When using cursor pagination, eventually fastapi_pagination.ext.sqlalchemy._cursor_flow() is called, which also gets a Selectable query: https://github.com/uriyyo/fastapi-pagination/blob/0556f17466acc222e0bb477886f4abb8f4bcac71/fastapi_pagination/ext/sqlalchemy.py#L272 It calls sqlakeyset.paging.select_page() with the selectable parameter set to the query: https://github.com/uriyyo/fastapi-pagination/blob/0556f17466acc222e0bb477886f4abb8f4bcac71/fastapi_pagination/ext/sqlalchemy.py#L280-L290 However, select_page() only accepts Select as parameter type. CompoundSelect is not a subclass of Select, but a sister class. Due to the # type: ignore[arg-type] comments in the above code, this error is masked from static type checking.

Version Information

• Windows 11
• Python 3.14.0
• Packages:
  • aiosqlite 0.21.0
  • fastapi-pagination 0.15.0
  • mypy 1.18.2
  • sqlakeyset 2.0.1762907931
  • sqlalchemy 2.0.44

[uriyyo]

Hi @JulianJvn,

Really appreciate such a detailed issue 🙏

[uriyyo]

Hi @JulianJvn ,

This issue should be fixed in version 0.15.1.

[uriyyo]

I'm closing this issue, please reopen it if issue is still present on your side 🙏