Bug: sorting on hybrid properties is broken in SQLAlchemyAsyncRepository

[ElvisTheKing]

Description

Sorting in SQLAlchemyAsyncRepository is broken when trying to sort on hybrid fields, see MCVE

when sorting is applied to stament itself it works fine: statement=select(Chat).order_by(Chat.last_message_time.asc()) when sorting is applied via order_by param of repository it fails with "NotImplementedError: Operator 'getitem' is not supported on this expression" error

URL to code causing the issue

No response

MCVE

#models:
class ChatMessage(UUIDBase):
    content: Mapped[str]

    chat_id: Mapped[UUID] = mapped_column(ForeignKey("chat.id", ondelete="CASCADE"))
    chat: Mapped["Chat"] = relationship(
        back_populates="messages", lazy="joined", innerjoin=True
    )

    created_at: Mapped[datetime] = mapped_column(
        DateTimeUTC(timezone=True),
        default=lambda: datetime.now(timezone.utc),
    )

class Chat(UUIDBase):
    messages: Mapped[list["ChatMessage"]] = relationship(
        back_populates="chat", lazy="noload", passive_deletes=True
    )

    @hybrid_property
    def last_message_time(self) -> Optional[datetime]:
        return max((msg.created_at for msg in self.messages), default=None)

    @last_message_time.inplace.expression
    @classmethod
    def _last_message_time_expression(cls)->SQLColumnExpression[DateTime]:
        # chat_alias = aliased(ChatMessage)
        return (
            select(func.max(ChatMessage.created_at))
            .where(ChatMessage.chat_id == cls.id)
            .label("last_message_time")
        )

#repo
class ChatRepository(SQLAlchemyAsyncRepository[Chat]):
    # select = select(Chat.id)
    model_type = Chat

# sort using statement in controller 
class ChatController(Controller):
    dependencies = {"repo": Provide(provide_chat_repo)}
    tags = ["Chat"]

    @get("/chats/", return_dto=ChatListDTO)
    async def list_chats(
        self, repo: ChatRepository, limit_offset: LimitOffset, current_user: User
    ) -> OffsetPagination[Chat]:
        result, total = await repo.list_and_count(
            limit_offset,
            statement=(
                select(Chat)
                .order_by(Chat.last_message_time.asc())
            ),
            load=[selectinload(Chat.messages)],
        )
        return OffsetPagination[Chat](
            items=result,
            total=total,
            limit=limit_offset.limit,
            offset=limit_offset.offset,
        )

# broken sort using order in repo call controller 
class ChatController(Controller):
    dependencies = {"repo": Provide(provide_chat_repo)}
    tags = ["Chat"]

    @get("/chats/", return_dto=ChatListDTO)
    async def list_chats(
        self, repo: ChatRepository, limit_offset: LimitOffset, current_user: User
    ) -> OffsetPagination[Chat]:
        result, total = await repo.list_and_count(
            limit_offset,
            statement=(
                select(Chat)
            ),
            order_by=Chat.last_message_time.desc(),
            load=[selectinload(Chat.messages)],
        )
        return OffsetPagination[Chat](
            items=result,
            total=total,
            limit=limit_offset.limit,
            offset=limit_offset.offset,
        )

Steps to reproduce

No response

Screenshots

No response

Logs

Uncaught exception (connection_type=http, path=/api/v1/chats):
Traceback (most recent call last):
  File "c:\code\.venv\Lib\site-packages\litestar\middleware\_internal\exceptions\middleware.py", line 159, in __call__
    await self.app(scope, receive, capture_response_started)
  File "c:\code\.venv\Lib\site-packages\litestar\_asgi\asgi_router.py", line 99, in __call__
    await asgi_app(scope, receive, send)
  File "c:\code\.venv\Lib\site-packages\litestar\routes\http.py", line 80, in handle
    response = await self._get_response_for_request(
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "c:\code\.venv\Lib\site-packages\litestar\routes\http.py", line 132, in _get_response_for_request
    return await self._call_handler_function(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "c:\code\.venv\Lib\site-packages\litestar\routes\http.py", line 152, in _call_handler_function
    response_data, cleanup_group = await self._get_response_data(
                                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "c:\code\.venv\Lib\site-packages\litestar\routes\http.py", line 200, in _get_response_data
    else await route_handler.fn(**parsed_kwargs)
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "c:\code\chat_api\controllers\chat.py", line 72, in list_chats
    result, total = await repo.list_and_count(
                    ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "c:\code\.venv\Lib\site-packages\advanced_alchemy\repository\_async.py", line 1297, in list_and_count
    return await self._list_and_count_window(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "c:\code\.venv\Lib\site-packages\advanced_alchemy\repository\_async.py", line 1372, in _list_and_count_window
    statement = self._apply_order_by(statement=statement, order_by=order_by)
                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "c:\code\.venv\Lib\site-packages\advanced_alchemy\repository\_util.py", line 202, in _apply_order_by
    for order_field, is_desc in order_by:
        ^^^^^^^^^^^^^^^^^^^^
  File "c:\code\.venv\Lib\site-packages\sqlalchemy\sql\operators.py", line 657, in __getitem__
    return self.operate(getitem, index)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "c:\code\.venv\Lib\site-packages\sqlalchemy\sql\elements.py", line 1513, in operate
    return op(self.comparator, *other, **kwargs)  # type: ignore[no-any-return]  # noqa: E501
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "c:\code\.venv\Lib\site-packages\sqlalchemy\sql\operators.py", line 657, in __getitem__
    return self.operate(getitem, index)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "c:\code\.venv\Lib\site-packages\sqlalchemy\sql\type_api.py", line 194, in operate
    return op_fn(self.expr, op, *other, **addtl_kw)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "c:\code\.venv\Lib\site-packages\sqlalchemy\sql\default_comparator.py", line 243, in _getitem_impl
    _unsupported_impl(expr, op, other, **kw)
  File "c:\code\.venv\Lib\site-packages\sqlalchemy\sql\default_comparator.py", line 249, in _unsupported_impl
    raise NotImplementedError(
NotImplementedError: Operator 'getitem' is not supported on this expression

Package Version

advanced_alchemy-0.17.2

Platform

• [ ] Linux
• [ ] Mac
• [X] Windows
• [ ] Other (Please specify in the description above)

[ElvisTheKing]

my bad, needed to provide OrderingPair