# HG changeset patch
# User Goffi <goffi@goffi.org>
# 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)