Species filter leads to an error

[jankrepl]

Adding the query param species__name__ilike=mouse to the electrical cell recording get many endpoint leads to internal server error

See full command below

curl -X 'GET' \
  'https://staging.openbluebrain.com/api/entitycore/electrical-cell-recording?page=1&page_size=5&order_by=-creation_date&etype__order_by=pref_label&contribution__order_by=pref_label&created_by__order_by=pref_label&updated_by__order_by=pref_label&subject__order_by=-creation_date&species__name__ilike=mouse&species__order_by=name&strain__order_by=name&brain_region__order_by=name&with_facets=false&within_brain_region_ascendants=false'  \
  -H 'accept: application/json' \
  -H "Authorization: Bearer $TOKEN"

Using species__name instead seems to lead to the same error

[eleftherioszisis]

Server error:

{"time":"2025-06-24T14:00:19.268713+00:00","level":"ERROR","name":"uvicorn.protocols.http.httptools_impl","message":"Exception in ASGI application\n","extra":{},"exception":{"type":"SAWarning","value":"SELECT statement has a cartesian product between FROM element(s) \"species\" and FROM element \"license_1\". Apply join condition(s) between each element to resolve.","traceback":"Traceback (most recent call last):\n File \"/code/.venv/lib/python3.12/site-packages/uvicorn/protocols/http/httptools_impl.py\", line 409, in run_asgi\n result = await app( # type: ignore[func-returns-value]\n ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n File \"/code/.venv/lib/python3.12/site-packages/uvicorn/middleware/proxy_headers.py\", line 60, in __call__\n return await self.app(scope, receive, send)\n ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n File \"/code/.venv/lib/python3.12/site-packages/fastapi/applications.py\", line 1054, in __call__\n await super().__call__(scope, receive, send)\n File \"/code/.venv/lib/python3.12/site-packages/starlette/applications.py\", line 112, in __call__\n await self.middleware_stack(scope, receive, send)\n File \"/code/.venv/lib/python3.12/site-packages/starlette/middleware/errors.py\", line 187, in __call__\n raise exc\n File \"/code/.venv/lib/python3.12/site-packages/starlette/middleware/errors.py\", line 165, in __call__\n await self.app(scope, receive, _send)\n File \"/code/.venv/lib/python3.12/site-packages/starlette/middleware/cors.py\", line 85, in __call__\n await self.app(scope, receive, send)\n File \"/code/.venv/lib/python3.12/site-packages/starlette/middleware/exceptions.py\", line 62, in __call__\n await wrap_app_handling_exceptions(self.app, conn)(scope, receive, send)\n File \"/code/.venv/lib/python3.12/site-packages/starlette/_exception_handler.py\", line 53, in wrapped_app\n raise exc\n File \"/code/.venv/lib/python3.12/site-packages/starlette/_exception_handler.py\", line 42, in wrapped_app\n await app(scope, receive, sender)\n File \"/code/.venv/lib/python3.12/site-packages/starlette/routing.py\", line 714, in __call__\n await self.middleware_stack(scope, receive, send)\n File \"/code/.venv/lib/python3.12/site-packages/starlette/routing.py\", line 734, in app\n await route.handle(scope, receive, send)\n File \"/code/.venv/lib/python3.12/site-packages/starlette/routing.py\", line 288, in handle\n await self.app(scope, receive, send)\n File \"/code/.venv/lib/python3.12/site-packages/starlette/routing.py\", line 76, in app\n await wrap_app_handling_exceptions(app, request)(scope, receive, send)\n File \"/code/.venv/lib/python3.12/site-packages/starlette/_exception_handler.py\", line 53, in wrapped_app\n raise exc\n File \"/code/.venv/lib/python3.12/site-packages/starlette/_exception_handler.py\", line 42, in wrapped_app\n await app(scope, receive, sender)\n File \"/code/.venv/lib/python3.12/site-packages/starlette/routing.py\", line 73, in app\n response = await f(request)\n ^^^^^^^^^^^^^^^^\n File \"/code/.venv/lib/python3.12/site-packages/fastapi/routing.py\", line 301, in app\n raw_response = await run_endpoint_function(\n ^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n File \"/code/.venv/lib/python3.12/site-packages/fastapi/routing.py\", line 214, in run_endpoint_function\n return await run_in_threadpool(dependant.call, **values)\n ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n File \"/code/.venv/lib/python3.12/site-packages/starlette/concurrency.py\", line 37, in run_in_threadpool\n return await anyio.to_thread.run_sync(func)\n ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n File \"/code/.venv/lib/python3.12/site-packages/anyio/to_thread.py\", line 56, in run_sync\n return await get_async_backend().run_sync_in_worker_thread(\n ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n File \"/code/.venv/lib/python3.12/site-packages/anyio/_backends/_asyncio.py\", line 2470, in run_sync_in_worker_thread\n return await future\n ^^^^^^^^^^^^\n File \"/code/.venv/lib/python3.12/site-packages/anyio/_backends/_asyncio.py\", line 967, in run\n result = context.run(func, *args)\n ^^^^^^^^^^^^^^^^^^^^^^^^\n File \"/code/app/service/electrical_cell_recording.py\", line 111, in read_many\n return router_read_many(\n ^^^^^^^^^^^^^^^^^\n File \"/code/app/queries/common.py\", line 294, in router_read_many\n data = db.execute(data_query).scalars().unique()\n ^^^^^^^^^^^^^^^^^^^^^^\n File \"/code/.venv/lib/python3.12/site-packages/sqlalchemy/orm/session.py\", line 2365, in execute\n return self._execute_internal(\n ^^^^^^^^^^^^^^^^^^^^^^^\n File \"/code/.venv/lib/python3.12/site-packages/sqlalchemy/orm/session.py\", line 2251, in _execute_internal\n result: Result[Any] = compile_state_cls.orm_execute_statement(\n ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n File \"/code/.venv/lib/python3.12/site-packages/sqlalchemy/orm/context.py\", line 306, in orm_execute_statement\n result = conn.execute(\n ^^^^^^^^^^^^^\n File \"/code/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py\", line 1416, in execute\n return meth(\n ^^^^^\n File \"/code/.venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py\", line 523, in _execute_on_connection\n return connection._execute_clauseelement(\n ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n File \"/code/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py\", line 1630, in _execute_clauseelement\n compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(\n ^^^^^^^^^^^^^^^^^^^^^^\n File \"/code/.venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py\", line 711, in _compile_w_cache\n compiled_sql = self._compiler(\n ^^^^^^^^^^^^^^^\n File \"/code/.venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py\", line 320, in _compiler\n return dialect.statement_compiler(dialect, self, **kw)\n ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n File \"/code/.venv/lib/python3.12/site-packages/sqlalchemy/sql/compiler.py\", line 1429, in __init__\n Compiled.__init__(self, dialect, statement, **kwargs)\n File \"/code/.venv/lib/python3.12/site-packages/sqlalchemy/sql/compiler.py\", line 870, in __init__\n self.string = self.process(self.statement, **compile_kwargs)\n ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n File \"/code/.venv/lib/python3.12/site-packages/sqlalchemy/sql/compiler.py\", line 915, in process\n return obj._compiler_dispatch(self, **kwargs)\n ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n File \"/code/.venv/lib/python3.12/site-packages/sqlalchemy/sql/visitors.py\", line 141, in _compiler_dispatch\n return meth(self, **kw) # type: ignore # noqa: E501\n ^^^^^^^^^^^^^^^^\n File \"/code/.venv/lib/python3.12/site-packages/sqlalchemy/sql/compiler.py\", line 4838, in visit_select\n text = self._compose_select_body(\n ^^^^^^^^^^^^^^^^^^^^^^^^^^\n File \"/code/.venv/lib/python3.12/site-packages/sqlalchemy/sql/compiler.py\", line 5008, in _compose_select_body\n from_linter.warn()\n File \"/code/.venv/lib/python3.12/site-packages/sqlalchemy/sql/compiler.py\", line 740, in warn\n util.warn(message)\n File \"/code/.venv/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py\", line 1821, in warn\n _warnings_warn(msg, exc.SAWarning)\n File \"/code/.venv/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py\", line 1896, in _warnings_warn\n warnings.warn(message, category, stacklevel=stacklevel + 1)\nsqlalchemy.exc.SAWarning: SELECT statement has a cartesian product between FROM element(s) \"species\" and FROM element \"license_1\". Apply join condition(s) between each element to resolve.\n"}}

Minimal repro:

'https://staging.openbluebrain.com/api/entitycore/electrical-cell-recording?species__name__ilike=mouse&species__order_by=name' -H "Authorization: Bearer $TOKEN"

[eleftherioszisis]

The nested filter order_by does not play very well with the rest of the filtering and needs improvement. Is it really needed at the moment to chain all those order_bys in nested filters?

[BoBer78]

Same issue with the name filter. I used the endpoint with :

{
  "within_brain_region_brain_region_id": "75e3944e-8dcd-4310-a3a2-258f93de63d6",
  "within_brain_region_ascendants": true,
  "species__name": "Mus musculus",
  "page_size": 5
}

And I got a 500 internal server error. From the AWS logs, here is the issue :

Traceback (most recent call last):\n  File \"/code/.venv/lib/python3.12/site-packages/uvicorn/protocols/http/httptools_impl.py\", line 409, in run_asgi\n    result = await app(  # type: ignore[func-returns-value]\n             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n  File \"/code/.venv/lib/python3.12/site-packages/uvicorn/middleware/proxy_headers.py\", line 60, in __call__\n    return await self.app(scope, receive, send)\n           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n  File \"/code/.venv/lib/python3.12/site-packages/fastapi/applications.py\", line 1054, in __call__\n    await super().__call__(scope, receive, send)\n  File \"/code/.venv/lib/python3.12/site-packages/starlette/applications.py\", line 112, in __call__\n    await self.middleware_stack(scope, receive, send)\n  File \"/code/.venv/lib/python3.12/site-packages/starlette/middleware/errors.py\", line 187, in __call__\n    raise exc\n  File \"/code/.venv/lib/python3.12/site-packages/starlette/middleware/errors.py\", line 165, in __call__\n    await self.app(scope, receive, _send)\n  File \"/code/.venv/lib/python3.12/site-packages/starlette/middleware/cors.py\", line 85, in __call__\n    await self.app(scope, receive, send)\n  File \"/code/.venv/lib/python3.12/site-packages/starlette/middleware/exceptions.py\", line 62, in __call__\n    await wrap_app_handling_exceptions(self.app, conn)(scope, receive, send)\n  File \"/code/.venv/lib/python3.12/site-packages/starlette/_exception_handler.py\", line 53, in wrapped_app\n    raise exc\n  File \"/code/.venv/lib/python3.12/site-packages/starlette/_exception_handler.py\", line 42, in wrapped_app\n    await app(scope, receive, sender)\n  File \"/code/.venv/lib/python3.12/site-packages/starlette/routing.py\", line 714, in __call__\n    await self.middleware_stack(scope, receive, send)\n  File \"/code/.venv/lib/python3.12/site-packages/starlette/routing.py\", line 734, in app\n    await route.handle(scope, receive, send)\n  File \"/code/.venv/lib/python3.12/site-packages/starlette/routing.py\", line 288, in handle\n    await self.app(scope, receive, send)\n  File \"/code/.venv/lib/python3.12/site-packages/starlette/routing.py\", line 76, in app\n    await wrap_app_handling_exceptions(app, request)(scope, receive, send)\n  File \"/code/.venv/lib/python3.12/site-packages/starlette/_exception_handler.py\", line 53, in wrapped_app\n    raise exc\n  File \"/code/.venv/lib/python3.12/site-packages/starlette/_exception_handler.py\", line 42, in wrapped_app\n    await app(scope, receive, sender)\n  File \"/code/.venv/lib/python3.12/site-packages/starlette/routing.py\", line 73, in app\n    response = await f(request)\n               ^^^^^^^^^^^^^^^^\n  File \"/code/.venv/lib/python3.12/site-packages/fastapi/routing.py\", line 301, in app\n    raw_response = await run_endpoint_function(\n                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n  File \"/code/.venv/lib/python3.12/site-packages/fastapi/routing.py\", line 214, in run_endpoint_function\n    return await run_in_threadpool(dependant.call, **values)\n           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n  File \"/code/.venv/lib/python3.12/site-packages/starlette/concurrency.py\", line 37, in run_in_threadpool\n    return await anyio.to_thread.run_sync(func)\n           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n  File \"/code/.venv/lib/python3.12/site-packages/anyio/to_thread.py\", line 56, in run_sync\n    return await get_async_backend().run_sync_in_worker_thread(\n           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n  File \"/code/.venv/lib/python3.12/site-packages/anyio/_backends/_asyncio.py\", line 2470, in run_sync_in_worker_thread\n    return await future\n           ^^^^^^^^^^^^\n  File \"/code/.venv/lib/python3.12/site-packages/anyio/_backends/_asyncio.py\", line 967, in run\n    result = context.run(func, *args)\n             ^^^^^^^^^^^^^^^^^^^^^^^^\n  File \"/code/app/service/electrical_cell_recording.py\", line 111, in read_many\n    return router_read_many(\n           ^^^^^^^^^^^^^^^^^\n  File \"/code/app/queries/common.py\", line 294, in router_read_many\n    data = db.execute(data_query).scalars().unique()\n           ^^^^^^^^^^^^^^^^^^^^^^\n  File \"/code/.venv/lib/python3.12/site-packages/sqlalchemy/orm/session.py\", line 2365, in execute\n    return self._execute_internal(\n           ^^^^^^^^^^^^^^^^^^^^^^^\n  File \"/code/.venv/lib/python3.12/site-packages/sqlalchemy/orm/session.py\", line 2251, in _execute_internal\n    result: Result[Any] = compile_state_cls.orm_execute_statement(\n                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n  File \"/code/.venv/lib/python3.12/site-packages/sqlalchemy/orm/context.py\", line 306, in orm_execute_statement\n    result = conn.execute(\n             ^^^^^^^^^^^^^\n  File \"/code/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py\", line 1416, in execute\n    return meth(\n           ^^^^^\n  File \"/code/.venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py\", line 523, in _execute_on_connection\n    return connection._execute_clauseelement(\n           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n  File \"/code/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py\", line 1630, in _execute_clauseelement\n    compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(\n                                                ^^^^^^^^^^^^^^^^^^^^^^\n  File \"/code/.venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py\", line 711, in _compile_w_cache\n    compiled_sql = self._compiler(\n                   ^^^^^^^^^^^^^^^\n  File \"/code/.venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py\", line 320, in _compiler\n    return dialect.statement_compiler(dialect, self, **kw)\n           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n  File \"/code/.venv/lib/python3.12/site-packages/sqlalchemy/sql/compiler.py\", line 1429, in __init__\n    Compiled.__init__(self, dialect, statement, **kwargs)\n  File \"/code/.venv/lib/python3.12/site-packages/sqlalchemy/sql/compiler.py\", line 870, in __init__\n    self.string = self.process(self.statement, **compile_kwargs)\n                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n  File \"/code/.venv/lib/python3.12/site-packages/sqlalchemy/sql/compiler.py\", line 915, in process\n    return obj._compiler_dispatch(self, **kwargs)\n           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n  File \"/code/.venv/lib/python3.12/site-packages/sqlalchemy/sql/visitors.py\", line 141, in _compiler_dispatch\n    return meth(self, **kw)  # type: ignore  # noqa: E501\n           ^^^^^^^^^^^^^^^^\n  File \"/code/.venv/lib/python3.12/site-packages/sqlalchemy/sql/compiler.py\", line 4838, in visit_select\n    text = self._compose_select_body(\n           ^^^^^^^^^^^^^^^^^^^^^^^^^^\n  File \"/code/.venv/lib/python3.12/site-packages/sqlalchemy/sql/compiler.py\", line 5008, in _compose_select_body\n    from_linter.warn()\n  File \"/code/.venv/lib/python3.12/site-packages/sqlalchemy/sql/compiler.py\", line 740, in warn\n    util.warn(message)\n  File \"/code/.venv/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py\", line 1821, in warn\n    _warnings_warn(msg, exc.SAWarning)\n  File \"/code/.venv/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py\", line 1896, in _warnings_warn\n    warnings.warn(message, category, stacklevel=stacklevel + 1)\nsqlalchemy.exc.SAWarning: SELECT statement has a cartesian product between FROM element(s) \"species\" and FROM element \"license_1\".  Apply join condition(s) between each element to resolve.\n"