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.
About this issue
- Original URL
- State: closed
- Created 9 months ago
- Reactions: 1
- Comments: 16 (11 by maintainers)
I fixed my code, and all working well, so issue was in my code not in the prepared new functionality.
@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.