# HG changeset patch # User Goffi # Date 1749052338 -7200 # Node ID 5e48ae998976c40fc452a595bf9c1cdbc8f91b1d # Parent ec9583cac6c21ba4c1c45cc70479dc769f5414bf memory (sqla): Add filter for keyword: A `keyword` filter can now be used to retrieve messages matching given keyword. rel 458 diff -r ec9583cac6c2 -r 5e48ae998976 libervia/backend/memory/sqla.py --- a/libervia/backend/memory/sqla.py Thu May 15 17:45:19 2025 +0200 +++ b/libervia/backend/memory/sqla.py Wed Jun 04 17:52:18 2025 +0200 @@ -28,7 +28,7 @@ from alembic import config as al_config, script as al_script from alembic.runtime import migration as al_migration -from sqlalchemy import and_, delete, event, func, or_, update +from sqlalchemy import and_, delete, event, exists, func, or_, update from sqlalchemy import Integer, literal_column, text from sqlalchemy.dialects.sqlite import insert from sqlalchemy.engine import Connection, Engine @@ -667,6 +667,14 @@ stmt = stmt.where(History.origin_id == filters["origin_id"]) if "thread_id" in filters: stmt = stmt.where(Thread.thread_id == filters["thread_id"]) + if "keyword" in filters: + keyword = filters["keyword"] + # FIXME: "json_each" works with SQLite, but when PostGreSQL support will + # be added, "json_array_elements" may have to be used with it. + subquery = func.json_each(History.extra["keywords"]).table_valued("value") + stmt = stmt.where( + exists().where(subquery.c.value == keyword) + ) if limit is not None: stmt = stmt.limit(limit)