sts-sponsors team mailing list archive

[Thorsten Merten <mp+438678@xxxxxxxxxxxxxxxxxx>]

Thorsten Merten has proposed merging ~thorsten-merten/maas-site-manager:MAASENG-1290-add-filter-to-sites into maas-site-manager:main.

Commit message:
feat: add filters to sites list view

see  MAASENG-1390 and MAASENG-1290



Requested reviews:
  MAAS Committers (maas-committers)

For more details, see:
https://code.launchpad.net/~thorsten-merten/maas-site-manager/+git/maas-site-manager/+merge/438678
-- 
Your team MAAS Committers is requested to review the proposed merge of ~thorsten-merten/maas-site-manager:MAASENG-1290-add-filter-to-sites into maas-site-manager:main.

diff --git a/backend/msm/db/queries.py b/backend/msm/db/queries.py
index ce2bd93..19ba033 100644
--- a/backend/msm/db/queries.py
+++ b/backend/msm/db/queries.py
@@ -3,10 +3,20 @@ from datetime import (
     datetime,
     timedelta,
 )
-from typing import Any
+from typing import (
+    Any,
+    cast,
+)
 from uuid import UUID
 
-from sqlalchemy import select
+from sqlalchemy import (
+    and_,
+    ColumnElement,
+    or_,
+    select,
+    Table,
+    true,
+)
 from sqlalchemy.ext.asyncio import AsyncSession
 
 from ._tables import (
@@ -15,6 +25,87 @@ from ._tables import (
 )
 
 
+def filters_from_arguments(
+    table: Table,
+    **kwargs: list[str] | None,
+) -> ColumnElement[bool]:
+    """
+    Joins all keys of the kwargs by AND and all entries for a single arg by OR.
+    This enables to convert query params such as
+
+      ?name=name1&name=name2&city=city
+
+    to a where clause such as
+
+      (name ilike %name1% OR name ilike %name2%) AND city ilike %city%
+
+    :param table: the table to create the WHERE clause for
+    :param kwargs: the parameters matching the table's column name
+                   as keys and lists of Strings that will be matched
+                   via ilike
+    :returns: a where clause that joins all queries per column
+                  with OR and all columns with AND
+    """
+    ands: list[ColumnElement[Any]] = []
+    for key in kwargs:
+        entries = kwargs[key]
+        if entries and len(entries) == 1:
+            ands.append(
+                cast(
+                    ColumnElement[Any],
+                    table.c[key].icontains(entries[0], autoescape=True),
+                )
+            )
+        if entries and len(entries) > 1:
+            ors: list[ColumnElement[Any]] = [
+                cast(
+                    ColumnElement[Any],
+                    table.c[key].icontains(entry, autoescape=True),
+                )
+                for entry in entries
+            ]
+            ands.append(or_(true(), *ors))
+    return and_(true(), *ands)
+
+
+async def get_filtered_sites(
+    session: AsyncSession,
+    city: list[str] | None = [],
+    name: list[str] | None = [],
+    note: list[str] | None = [],
+    region: list[str] | None = [],
+    street: list[str] | None = [],
+    timezone: list[str] | None = [],
+    url: list[str] | None = [],
+) -> Iterable[dict[str, Any]]:
+    where = filters_from_arguments(
+        Site,
+        city=city,
+        name=name,
+        note=note,
+        region=region,
+        street=street,
+        timezone=timezone,
+        url=url,
+    )
+
+    stmt = select(
+        Site.c.id,
+        Site.c.name,
+        Site.c.identifier,
+        Site.c.city,
+        Site.c.latitude,
+        Site.c.longitude,
+        Site.c.note,
+        Site.c.region,
+        Site.c.street,
+        Site.c.timezone,
+        Site.c.url,
+    ).where(where)
+    result = await session.execute(stmt)
+    return (row._asdict() for row in result.all())
+
+
 async def get_sites(session: AsyncSession) -> Iterable[dict[str, Any]]:
     stmt = select(
         Site.c.id,
diff --git a/backend/msm/user_api/_base.py b/backend/msm/user_api/_base.py
index d1e72f1..07bd159 100644
--- a/backend/msm/user_api/_base.py
+++ b/backend/msm/user_api/_base.py
@@ -1,4 +1,7 @@
-from fastapi import Depends
+from fastapi import (
+    Depends,
+    Query,
+)
 from sqlalchemy.ext.asyncio import AsyncSession
 
 from . import _schema as schema
@@ -14,10 +17,30 @@ async def root() -> dict[str, str]:
 
 
 async def sites(
+    city: list[str] | None = Query(default=None, title="Filter for cities"),
+    name: list[str] | None = Query(default=None, title="Filter for names"),
+    note: list[str] | None = Query(default=None, title="Filter for notes"),
+    region: list[str] | None = Query(default=None, title="Filter for regions"),
+    street: list[str] | None = Query(default=None, title="Filter for streets"),
+    timezone: list[str]
+    | None = Query(default=None, title="Filter for timezones"),
+    url: list[str] | None = Query(default=None, title="Filter for urls"),
     session: AsyncSession = Depends(db_session),
 ) -> list[schema.Site]:
     """Return all sites"""
-    return [schema.Site(**entry) for entry in await queries.get_sites(session)]
+    return [
+        schema.Site(**entry)
+        for entry in await queries.get_filtered_sites(
+            session,
+            city,
+            name,
+            note,
+            region,
+            street,
+            timezone,
+            url,
+        )
+    ]
 
 
 async def tokens(
diff --git a/backend/msm/user_api/tests/test_handlers.py b/backend/msm/user_api/tests/test_handlers.py
index 0fd45a2..20f9b5e 100644
--- a/backend/msm/user_api/tests/test_handlers.py
+++ b/backend/msm/user_api/tests/test_handlers.py
@@ -35,10 +35,12 @@ async def test_list_sites(
     site2 = site1.copy()
     site2["id"] = 2
     site2["identifier"] = "site two"
+    site2["name"] = "BerlinHQ"
+    site2["city"] = "Berlin"
     await fixture.create("site", [site1, site2])
-    response = user_app_client.get("/sites")
+    response = user_app_client.get("/sites?city=onDo")  # vs London
     assert response.status_code == 200
-    assert response.json() == [site1, site2]
+    assert response.json() == [site1]
 
 
 @pytest.mark.asyncio