sqladmin: Columns from relationahip can't be sorted or searched

Checklist

The bug is reproducible against the latest release or master.
There are no similar issues or pull requests to fix it yet.

Describe the bug

If try to sort by Location (http://127.0.0.1:8000/admin/storage/list?sortBy=location&sort=asc) column 500 Internal Server error

Also even if this column is marked as searchable, can’t find any results.

INFO:     127.0.0.1:51626 - "GET /storage/list?sortBy=location&sort=asc HTTP/1.1" 500 Internal Server Error
ERROR:    Exception in ASGI application
Traceback (most recent call last):
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\sqlalchemy\sql\compiler.py", line 2443, in visit_textual_label_reference
    col = with_cols[element.element]
          ~~~~~~~~~^^^^^^^^^^^^^^^^^
KeyError: 'location'

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\uvicorn\protocols\http\h11_impl.py", line 408, in run_asgi
    result = await app(  # type: ignore[func-returns-value]
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\uvicorn\middleware\proxy_headers.py", line 84, in __call__
    return await self.app(scope, receive, send)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\fastapi\applications.py", line 292, in __call__
    await super().__call__(scope, receive, send)
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\starlette\applications.py", line 122, in __call__
    await self.middleware_stack(scope, receive, send)
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\starlette\middleware\errors.py", line 184, in __call__
    raise exc
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\starlette\middleware\errors.py", line 162, in __call__
    await self.app(scope, receive, _send)
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\starlette\middleware\exceptions.py", line 79, in __call__
    raise exc
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\starlette\middleware\exceptions.py", line 68, in __call__
    await self.app(scope, receive, sender)
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\fastapi\middleware\asyncexitstack.py", line 20, in __call__
    raise e
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\fastapi\middleware\asyncexitstack.py", line 17, in __call__
    await self.app(scope, receive, send)
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\starlette\routing.py", line 718, in __call__
    await route.handle(scope, receive, send)
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\starlette\routing.py", line 443, in handle
    await self.app(scope, receive, send)
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\starlette\applications.py", line 122, in __call__
    await self.middleware_stack(scope, receive, send)
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\starlette\middleware\errors.py", line 184, in __call__
    raise exc
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\starlette\middleware\errors.py", line 162, in __call__
    await self.app(scope, receive, _send)
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\starlette\middleware\sessions.py", line 86, in __call__
    await self.app(scope, receive, send_wrapper)
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\starlette\middleware\exceptions.py", line 79, in __call__
    raise exc
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\starlette\middleware\exceptions.py", line 68, in __call__
    await self.app(scope, receive, sender)
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\starlette\routing.py", line 718, in __call__
    await route.handle(scope, receive, send)
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\starlette\routing.py", line 276, in handle
    await self.app(scope, receive, send)
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\starlette\routing.py", line 66, in app
    response = await func(request)
               ^^^^^^^^^^^^^^^^^^^
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\sqladmin\authentication.py", line 66, in wrapper_decorator
    return await func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\sqladmin\application.py", line 438, in list
    pagination = await model_view.list(request)
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\sqladmin\models.py", line 794, in list
    rows = await self._run_query(stmt)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\sqladmin\models.py", line 705, in _run_query
    return await anyio.to_thread.run_sync(self._run_query_sync, stmt)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\anyio\to_thread.py", line 33, in run_sync
    return await get_asynclib().run_sync_in_worker_thread(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\anyio\_backends\_asyncio.py", line 877, in run_sync_in_worker_thread
    return await future
           ^^^^^^^^^^^^
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\anyio\_backends\_asyncio.py", line 807, in run
    result = context.run(func, *args)
             ^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\sqladmin\models.py", line 696, in _run_query_sync
    result = session.execute(stmt)
             ^^^^^^^^^^^^^^^^^^^^^
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\sqlalchemy\orm\session.py", line 2262, in execute
    return self._execute_internal(
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\sqlalchemy\orm\session.py", line 2144, in _execute_internal
    result: Result[Any] = compile_state_cls.orm_execute_statement(
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\sqlalchemy\orm\context.py", line 293, in orm_execute_statement
    result = conn.execute(
             ^^^^^^^^^^^^^
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\sqlalchemy\engine\base.py", line 1412, in execute
    return meth(
           ^^^^^
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\sqlalchemy\sql\elements.py", line 516, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\sqlalchemy\engine\base.py", line 1627, in _execute_clauseelement
    compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
                                                ^^^^^^^^^^^^^^^^^^^^^^
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\sqlalchemy\sql\elements.py", line 704, in _compile_w_cache
    compiled_sql = self._compiler(
                   ^^^^^^^^^^^^^^^
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\sqlalchemy\sql\elements.py", line 316, in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\sqlalchemy\sql\compiler.py", line 1426, in __init__
    Compiled.__init__(self, dialect, statement, **kwargs)
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\sqlalchemy\sql\compiler.py", line 867, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\sqlalchemy\sql\compiler.py", line 912, in process
    return obj._compiler_dispatch(self, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\sqlalchemy\sql\visitors.py", line 143, in _compiler_dispatch
    return meth(self, **kw)  # type: ignore  # noqa: E501
           ^^^^^^^^^^^^^^^^
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\sqlalchemy\sql\compiler.py", line 4730, in visit_select
    text = self._compose_select_body(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\sqlalchemy\sql\compiler.py", line 4913, in _compose_select_body
    text += self.order_by_clause(select, **kwargs)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\sqlalchemy\sql\compiler.py", line 5022, in order_by_clause
    order_by = self._generate_delimited_list(
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\sqlalchemy\sql\compiler.py", line 2696, in _generate_delimited_list
    return separator.join(
           ^^^^^^^^^^^^^^^
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\sqlalchemy\sql\compiler.py", line 2696, in <genexpr>
    return separator.join(
                         ^
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\sqlalchemy\sql\compiler.py", line 2698, in <genexpr>
    for s in (c._compiler_dispatch(self, **kw) for c in elements)
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\sqlalchemy\sql\visitors.py", line 143, in _compiler_dispatch
    return meth(self, **kw)  # type: ignore  # noqa: E501
           ^^^^^^^^^^^^^^^^
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\sqlalchemy\sql\compiler.py", line 3022, in visit_unary
    return self._generate_generic_unary_modifier(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\sqlalchemy\sql\compiler.py", line 3395, in _generate_generic_unary_modifier
    return unary.element._compiler_dispatch(self, **kw) + opstring
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\sqlalchemy\sql\visitors.py", line 143, in _compiler_dispatch
    return meth(self, **kw)  # type: ignore  # noqa: E501
           ^^^^^^^^^^^^^^^^
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\sqlalchemy\sql\compiler.py", line 2445, in visit_textual_label_reference
    coercions._no_text_coercion(
  File "C:\mSpaceLock\mSpaceLock-www\env\Lib\site-packages\sqlalchemy\sql\coercions.py", line 601, in _no_text_coercion
    raise exc_cls(
sqlalchemy.exc.CompileError: Can't resolve label reference for ORDER BY / GROUP BY / DISTINCT etc. Textual SQL expression 'location' should be explicitly declared as text('location')

Steps to reproduce the bug

StorageView is defined:

class StorageView(ModelView, model=Storage):
    column_list = [
        Storage.storage_id,
        Storage.is_occupied,
        Storage.is_restricted,
        Storage.rented_until,
        Storage.location,
    ]
[...]
    column_searchable_list = [
        Storage.storage_id,
        Storage.location,
    ]
    column_sortable_list = [
        Storage.storage_id,
        Storage.rented_until,
        Storage.location,
    ]
[...]

Location in Storage Base model:

class Storage(Base):
[...]
    location_uuid: Mapped[str] = mapped_column(
        ForeignKey("location.location_uuid"),
    )
    location: Mapped["Location"] = relationship(
        "Location",
        lazy="selectin",
        single_parent=True,
        back_populates="storages",
    )
   
[...]
    def __str__(self) -> str:
        return f"{self.location} - {self.storage_id}"

Expected behavior

Sorting and searching by that column should be possible and not cause 500 Internal Server Error

Actual behavior

No response

Debugging material

No response

Environment

Python 3.11 Windows 10 SQLAdmin 0.15.1

Additional context

No response

Upvote & Fund

We’re using Polar.sh so you can upvote and help fund this issue.
We receive the funding once the issue is completed & confirmed by you.
Thank you in advance for helping prioritize & fund our backlog.

<picture> <source media="(prefers-color-scheme: dark)" srcset="https://polar.sh/api/github/aminalaee/sqladmin/issues/643/pledge.svg?darkmode=1"> Fund with Polar

</picture>

About this issue

Original URL
State: closed
Created 9 months ago
Reactions: 1
Comments: 16 (11 by maintainers)

Most upvoted comments

I fixed my code, and all working well, so issue was in my code not in the prepared new functionality.

NightSkySK on Oct 26, 2023

@aminalaee It is not much, but at least that I can do to support great job which you’ve done so far. I wish to become long term supporter of this project, but I first need to make some agreements.

NightSkySK on Oct 11, 2023