auth: Fetch user group phenotypes not attached to a resource.

2 files changed, 60 insertions, 11 deletions

diff --git a/gn3/auth/authorisation/groups/views.py b/gn3/auth/authorisation/groups/views.py
index 0ff2903..202df95 100644
--- a/gn3/auth/authorisation/groups/views.py
+++ b/gn3/auth/authorisation/groups/views.py
@@ -4,10 +4,11 @@ import datetime
 from typing import Iterable
 from functools import partial
 
+from MySQLdb.cursors import DictCursor
 from flask import request, jsonify, Response, Blueprint, current_app
 
 from gn3.auth import db
-from gn3 import db_utils as gn3dbutils
+from gn3 import db_utils as gn3db
 
 from gn3.auth.dictify import dictify
 from gn3.auth.db_utils import with_db_connection
@@ -166,13 +167,57 @@ def unlinked_genotype_data(
         return tuple(dict(row) for row in cursor.fetchall())
 
 def unlinked_phenotype_data(
-        conn: db.DbConnection, group: Group) -> tuple[dict, ...]:#pylint: disable=[unused-argument]
+        authconn: db.DbConnection, gn3conn: gn3db.Connection,
+        group: Group) -> tuple[dict, ...]:
     """
     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.")
+    with db.cursor(authconn) as authcur, gn3conn.cursor(DictCursor) as gn3cur:
+        authcur.execute(
+            "SELECT lpd.* FROM linked_phenotype_data AS lpd "
+            "LEFT JOIN phenotype_resources AS pr "
+            "ON lpd.data_link_id=pr.data_link_id "
+            "WHERE lpd.group_id=? AND pr.data_link_id IS NULL",
+            (str(group.group_id),))
+        ids = tuple((
+            row["SpeciesId"], row["InbredSetId"], row["PublishFreezeId"],
+            row["PublishXRefId"]) for row in authcur.fetchall())
+        if len(ids) < 1:
+            return tuple()
+        paramstr = ", ".join(["(%s, %s, %s, %s)"] * len(ids))
+        gn3cur.execute(
+            "SELECT spc.SpeciesId, spc.SpeciesName, iset.InbredSetId, "
+            "iset.InbredSetName, pf.Id AS PublishFreezeId, "
+            "pf.Name AS dataset_name, pf.FullName AS dataset_fullname, "
+            "pf.ShortName AS dataset_shortname, pxr.Id AS PublishXRefId, "
+            "pub.PubMed_ID, pub.Title, pub.Year, "
+            "phen.Pre_publication_description, "
+            "phen.Post_publication_description, phen.Original_description "
+            "FROM "
+            "Species AS spc "
+            "INNER JOIN InbredSet AS iset "
+            "ON spc.SpeciesId=iset.SpeciesId "
+            "INNER JOIN PublishFreeze AS pf "
+            "ON iset.InbredSetId=pf.InbredSetId "
+            "INNER JOIN PublishXRef AS pxr "
+            "ON pf.InbredSetId=pxr.InbredSetId "
+            "INNER JOIN Publication AS pub "
+            "ON pxr.PublicationId=pub.Id "
+            "INNER JOIN Phenotype AS phen "
+            "ON pxr.PhenotypeId=phen.Id "
+            "WHERE (spc.SpeciesId, iset.InbredSetId, pf.Id, pxr.Id) "
+            f"IN ({paramstr})",
+            tuple(item for sublist in ids for item in sublist))
+        return tuple({
+            **{key: value for key, value in row.items() if key not in
+               ("Post_publication_description", "Pre_publication_description",
+                "Original_description")},
+            "description": (
+                row["Post_publication_description"] or
+                row["Pre_publication_description"] or
+                row["Original_description"])
+        } for row in gn3cur.fetchall())
 
 @groups.route("/<string:resource_type>/unlinked-data")
 @require_oauth("profile group resource")
@@ -183,8 +228,10 @@ 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:
-            ugroup = user_group(conn, the_token.user).maybe(# type: ignore[misc]
+        gn3db_uri = current_app.config["SQL_URI"]
+        with (db.connection(db_uri) as authconn,
+              gn3db.database_connection(gn3db_uri) as gn3conn):
+            ugroup = user_group(authconn, the_token.user).maybe(# type: ignore[misc]
                 DUMMY_GROUP, lambda grp: grp)
             if ugroup == DUMMY_GROUP:
                 return jsonify(tuple())
@@ -192,10 +239,13 @@ def unlinked_data(resource_type: str) -> Response:
             unlinked_fns = {
                 "mrna": unlinked_mrna_data,
                 "genotype": unlinked_genotype_data,
-                "phenotype": unlinked_phenotype_data
+                "phenotype": lambda conn, grp: partial(
+                    unlinked_phenotype_data, gn3conn=gn3conn)(
+                        authconn=conn, group=grp)
             }
             return jsonify(tuple(
-                dict(row) for row in unlinked_fns[resource_type](conn, ugroup)))
+                dict(row) for row in unlinked_fns[resource_type](
+                    authconn, ugroup)))
 
     return jsonify(tuple())
 
@@ -212,7 +262,7 @@ def link_data() -> Response:
             raise InvalidData("Unexpected dataset type requested!")
         def __link__(conn: db.DbConnection):
             group = group_by_id(conn, group_id)
-            with gn3dbutils.database_connection(current_app.config["SQL_URI"]) as gn3conn:
+            with gn3db.database_connection(current_app.config["SQL_URI"]) as gn3conn:
                 return link_data_to_group(
                     conn, gn3conn, dataset_type, dataset_ids, group)
 
diff --git a/gn3/auth/authorisation/resources/models.py b/gn3/auth/authorisation/resources/models.py
index 4c71ade..2e2ff53 100644
--- a/gn3/auth/authorisation/resources/models.py
+++ b/gn3/auth/authorisation/resources/models.py
@@ -240,8 +240,7 @@ def phenotype_resource_data(
     cursor.execute(
         "SELECT * FROM phenotype_resources AS pr "
         "INNER JOIN linked_phenotype_data AS lpd "
-        "ON (pr.trait_id=lpd.dataset_or_trait_id "
-        "AND pr.dataset_type=lpd.dataset_type) "
+        "ON pr.data_link_id=lpd.data_link_id "
         "WHERE pr.resource_id=?",
         (str(resource_id),))
     return cursor.fetchall()