user resources: return total with filtered records.

Return a count of the total number of resources that the user has
access to even if we are only interested in a few of the records.

2 files changed, 28 insertions, 9 deletions

diff --git a/gn_auth/auth/authorisation/data/views.py b/gn_auth/auth/authorisation/data/views.py
index 4bf6746..1526070 100644
--- a/gn_auth/auth/authorisation/data/views.py
+++ b/gn_auth/auth/authorisation/data/views.py
@@ -95,7 +95,7 @@ def authorisation() -> Response:
             with require_oauth.acquire("profile group resource") as _token:
                 user = _token.user
                 resources = attach_resources_data(
-                    auth_conn, user_resources(auth_conn, _token.user))
+                    auth_conn, user_resources(auth_conn, _token.user)[0])
                 resources_roles = user_resource_roles(auth_conn, _token.user)
                 privileges = {
                     resource_id: tuple(
diff --git a/gn_auth/auth/authorisation/resources/models.py b/gn_auth/auth/authorisation/resources/models.py
index 4816527..f0647c3 100644
--- a/gn_auth/auth/authorisation/resources/models.py
+++ b/gn_auth/auth/authorisation/resources/models.py
@@ -185,18 +185,36 @@ def group_leader_resources(
     return tuple()
 
 
-def user_resources(conn: db.DbConnection, user: User) -> Sequence[Resource]:
+def user_resources(
+        conn: db.DbConnection,
+        user: User,
+        start_at: int = 0,
+        count: int = 0
+) -> tuple[Sequence[Resource], int]:
     """List the resources available to the user"""
-    with db.cursor(conn) as cursor:
-        cursor.execute(
-            ("SELECT DISTINCT(r.resource_id), r.resource_name,  "
-             "r.resource_category_id, r.public, r.created_by, r.created_at, "
-             "rc.resource_category_key, rc.resource_category_description "
+    query_template = ("SELECT %%COLUMNS%%  "
              "FROM user_roles AS ur "
              "INNER JOIN resources AS r ON ur.resource_id=r.resource_id "
              "INNER JOIN resource_categories AS rc "
              "ON r.resource_category_id=rc.resource_category_id "
-             "WHERE ur.user_id=?"),
+             "WHERE ur.user_id=? %%LIMITS%%")
+    with db.cursor(conn) as cursor:
+        cursor.execute(
+            query_template.replace(
+                "%%COLUMNS%%", "COUNT(DISTINCT(r.resource_id)) AS count"
+            ).replace(
+                "%%LIMITS%%", ""),
+            (str(user.user_id),))
+        _total_records = int(cursor.fetchone()["count"])
+        cursor.execute(
+            query_template.replace(
+                "%%COLUMNS%%",
+                "DISTINCT(r.resource_id), r.resource_name, "
+                "r.resource_category_id, r.public, r.created_by, r.created_at, "
+                "rc.resource_category_key, rc.resource_category_description"
+            ).replace(
+                "%%LIMITS%%",
+                ("" if count <= 0 else f"LIMIT {count} OFFSET {start_at}")),
             (str(user.user_id),))
         rows = cursor.fetchall() or []
 
@@ -208,7 +226,8 @@ def user_resources(conn: db.DbConnection, user: User) -> Sequence[Resource]:
             resource_from_dbrow(row),
             created_by=_creators_[row["created_by"]],
             created_at=datetime.fromtimestamp(row["created_at"])
-        ) for row in rows)
+        ) for row in rows), _total_records
+
 
 
 def resource_data(conn, resource, offset: int = 0, limit: Optional[int] = None) -> tuple[dict, ...]: