Fetch genotype resources by dataset.

1 files changed, 40 insertions, 1 deletions

diff --git a/gn_auth/auth/authorisation/data/genotypes.py b/gn_auth/auth/authorisation/data/genotypes.py
index ddb0add..d44cbfb 100644
--- a/gn_auth/auth/authorisation/data/genotypes.py
+++ b/gn_auth/auth/authorisation/data/genotypes.py
@@ -1,7 +1,9 @@
 """Handle linking of Genotype data to the Auth(entic|oris)ation system."""
 import uuid
-from dataclasses import asdict
+import logging
 from typing import Iterable
+from functools import reduce
+from dataclasses import asdict
 
 from gn_libs import mysqldb as gn3db
 from MySQLdb.cursors import DictCursor
@@ -11,6 +13,9 @@ from gn_auth.auth.db import sqlite3 as authdb
 from gn_auth.auth.authorisation.checks import authorised_p
 from gn_auth.auth.authorisation.resources.groups.models import Group
 
+
+logger = logging.getLogger(__name__)
+
 def linked_genotype_data(conn: authdb.DbConnection) -> Iterable[dict]:
     """Retrieve genotype data that is linked to user groups."""
     with authdb.cursor(conn) as cursor:
@@ -95,3 +100,37 @@ def link_genotype_data(
             "group": asdict(group),
             "datasets": datasets
         }
+
+
+def resources_by_datasets_and_traits(
+        authconn: authdb.DbConnection,
+        dsets_traits: tuple[tuple[str, str], ...]
+) -> tuple[dict, ...]:
+    """Fetch resources by their attached datasets and traits."""
+    traits_by_datasets: dict[str, tuple[str, ...]] = reduce(
+        lambda acc, curr: {
+            **acc,
+            curr[0]: acc.get(curr[0], tuple()) + (curr[1],)
+        },
+        dsets_traits,
+        {})
+    paramstr = ", ".join(["?"] * len(dsets_traits))
+    query = (
+        "SELECT r.*, rc.*, lgd.dataset_name FROM linked_genotype_data AS lgd "
+        "INNER JOIN genotype_resources AS mr ON lgd.data_link_id=mr.data_link_id "
+        "INNER JOIN resources AS r ON mr.resource_id=r.resource_id "
+        "INNER JOIN resource_categories AS rc "
+        "ON r.resource_category_id=rc.resource_category_id "
+        "WHERE lgd.dataset_name "
+        f"IN ({paramstr})")
+    logger.debug("QUERY: %s", query)
+    with authdb.cursor(authconn) as cursor:
+        params = tuple(traits_by_datasets.keys())
+        logger.debug("QUERY PARAMS: %s", params)
+        cursor.execute(query, tuple(traits_by_datasets.keys()))
+        return tuple({
+            "resource_id": row["resource_id"],
+            "resource_data": tuple(
+                f'{row["dataset_name"]}::{trait_id}'
+                for trait_id in traits_by_datasets[row["dataset_name"]])
+        } for row in cursor.fetchall())