auth: Enable listing of unlinked genotype/mRNA group data.

2 files changed, 57 insertions, 52 deletions

diff --git a/gn3/auth/authorisation/groups/views.py b/gn3/auth/authorisation/groups/views.py
index 6f1a6df..d7a46c0 100644
--- a/gn3/auth/authorisation/groups/views.py
+++ b/gn3/auth/authorisation/groups/views.py
@@ -14,11 +14,11 @@ from gn3.auth.db_utils import with_db_connection
 
 from .data import link_data_to_group, retrieve_ungrouped_data
 from .models import (
-    user_group, all_groups, DUMMY_GROUP, GroupRole, group_by_id, join_requests,
-    group_role_by_id, GroupCreationError, accept_reject_join_request,
-    group_users as _group_users, create_group as _create_group,
-    add_privilege_to_group_role, delete_privilege_to_group_role,
-    create_group_role as _create_group_role)
+    Group, user_group, all_groups, DUMMY_GROUP, GroupRole, group_by_id,
+    join_requests, group_role_by_id, GroupCreationError,
+    accept_reject_join_request, group_users as _group_users,
+    create_group as _create_group, add_privilege_to_group_role,
+    delete_privilege_to_group_role, create_group_role as _create_group_role)
 
 from ..roles.models import Role
 from ..checks import authorised_p
@@ -138,6 +138,42 @@ def reject_join_requests() -> Response:
             accept_reject_join_request, request_id=request_id,
             user=the_token.user, status="REJECTED")))
 
+def unlinked_mrna_data(
+        conn: db.DbConnection, group: Group) -> tuple[dict, ...]:
+    """
+    Retrieve all mRNA Assay data linked to a group but not linked to any
+    resource.
+    """
+    query = (
+        "SELECT lmd.* FROM linked_mrna_data lmd "
+        "LEFT JOIN mrna_resources mr ON lmd.data_link_id=mr.data_link_id "
+        "WHERE lmd.group_id=? AND mr.data_link_id IS NULL")
+    with db.cursor(conn) as cursor:
+        cursor.execute(query, (str(group.group_id),))
+        return tuple(dict(row) for row in cursor.fetchall())
+
+def unlinked_genotype_data(
+        conn: db.DbConnection, group: Group) -> tuple[dict, ...]:
+    """
+    Retrieve all genotype data linked to a group but not linked to any resource.
+    """
+    query = (
+        "SELECT lgd.* FROM linked_genotype_data lgd "
+        "LEFT JOIN genotype_resources gr ON lgd.data_link_id=gr.data_link_id "
+        "WHERE lgd.group_id=? AND gr.data_link_id IS NULL")
+    with db.cursor(conn) as cursor:
+        cursor.execute(query, (str(group.group_id),))
+        return tuple(dict(row) for row in cursor.fetchall())
+
+def unlinked_phenotype_data(
+        conn: db.DbConnection, group: Group) -> tuple[dict, ...]:#pylint: disable=[unused-argument]
+    """
+    Retrieve all phenotype data linked to a group but not linked to any
+    resource.
+    """
+    raise NotImplementedError(
+        "The feature to fetch unlinked phenotype data is not implemented yet.")
+
 @groups.route("/<string:resource_type>/unlinked-data")
 @require_oauth("profile group resource")
 def unlinked_data(resource_type: str) -> Response:
@@ -147,48 +183,19 @@ def unlinked_data(resource_type: str) -> Response:
 
     with require_oauth.acquire("profile group resource") as the_token:
         db_uri = current_app.config["AUTH_DB"]
-        with db.connection(db_uri) as conn, db.cursor(conn) as cursor:
+        with db.connection(db_uri) as conn:
             ugroup = user_group(conn, the_token.user).maybe(# type: ignore[misc]
                 DUMMY_GROUP, lambda grp: grp)
             if ugroup == DUMMY_GROUP:
                 return jsonify(tuple())
-            type_filter = {
-                "all": "",
-                "mrna": 'WHERE dataset_type="mRNA"',
-                "genotype": 'WHERE dataset_type="Genotype"',
-                "phenotype": 'WHERE dataset_type="Phenotype"'
-            }[resource_type]
-
-            except_filter = (
-                "SELECT group_id, dataset_type, "
-                "dataset_id AS dataset_or_trait_id FROM mrna_resources "
-                "UNION "
-                "SELECT group_id, dataset_type, "
-                "trait_id AS dataset_or_trait_id FROM genotype_resources "
-                "UNION "
-                "SELECT group_id, dataset_type, "
-                "trait_id AS dataset_or_trait_id FROM phenotype_resources")
-
-            ids_query = (
-                "SELECT * FROM ("
-                "SELECT group_id, dataset_type, dataset_or_trait_id "
-                "FROM linked_group_data "
-                f"EXCEPT SELECT * FROM ({except_filter})"
-                f") {type_filter}")
-            cursor.execute(ids_query)
-            ids = cursor.fetchall()
-
-            if ids:
-                clause = ", ".join(["(?, ?, ?)"] * len(ids))
-                data_query = (
-                    "SELECT * FROM linked_group_data "
-                    "WHERE (group_id, dataset_type, dataset_or_trait_id) "
-                    f"IN (VALUES {clause}) ")
-                params = tuple(item for sublist in
-                               ((row[0], row[1], row[2]) for row in ids)
-                               for item in sublist)
-                cursor.execute(data_query, params)
-                return jsonify(tuple(dict(row) for row in cursor.fetchall()))
+
+            unlinked_fns = {
+                "mrna": unlinked_mrna_data,
+                "genotype": unlinked_genotype_data,
+                "phenotype": unlinked_phenotype_data
+            }
+            return jsonify(tuple(
+                dict(row) for row in unlinked_fns[resource_type](conn, ugroup)))
 
     return jsonify(tuple())
 
diff --git a/gn3/auth/authorisation/resources/models.py b/gn3/auth/authorisation/resources/models.py
index afda5e8..4c71ade 100644
--- a/gn3/auth/authorisation/resources/models.py
+++ b/gn3/auth/authorisation/resources/models.py
@@ -217,9 +217,8 @@ def mrna_resource_data(
         cursor: db.DbCursor, resource_id: UUID) -> Sequence[sqlite3.Row]:
     """Fetch data linked to a mRNA resource"""
     cursor.execute(
-        "SELECT * FROM mrna_resources AS mr INNER JOIN linked_group_data AS lgd"
-        " ON (mr.dataset_id=lgd.dataset_or_trait_id "
-        "AND mr.dataset_type=lgd.dataset_type) "
+        "SELECT * FROM mrna_resources AS mr INNER JOIN linked_mrna_data AS lmr"
+        " ON mr.data_link_id=lmr.data_link_id "
         "WHERE mr.resource_id=?",
         (str(resource_id),))
     return cursor.fetchall()
@@ -229,9 +228,8 @@ def genotype_resource_data(
     """Fetch data linked to a Genotype resource"""
     cursor.execute(
         "SELECT * FROM genotype_resources AS gr "
-        "INNER JOIN linked_group_data AS lgd "
-        "ON (gr.trait_id=lgd.dataset_or_trait_id "
-        "AND gr.dataset_type=lgd.dataset_type) "
+        "INNER JOIN linked_genotype_data AS lgd "
+        "ON gr.data_link_id=lgd.data_link_id "
         "WHERE gr.resource_id=?",
         (str(resource_id),))
     return cursor.fetchall()
@@ -241,9 +239,9 @@ def phenotype_resource_data(
     """Fetch data linked to a Phenotype resource"""
     cursor.execute(
         "SELECT * FROM phenotype_resources AS pr "
-        "INNER JOIN linked_group_data AS lgd "
-        "ON (pr.trait_id=lgd.dataset_or_trait_id "
-        "AND pr.dataset_type=lgd.dataset_type) "
+        "INNER JOIN linked_phenotype_data AS lpd "
+        "ON (pr.trait_id=lpd.dataset_or_trait_id "
+        "AND pr.dataset_type=lpd.dataset_type) "
         "WHERE pr.resource_id=?",
         (str(resource_id),))
     return cursor.fetchall()