Attach publication details to each phenotype trait.

2 files changed, 36 insertions, 8 deletions

diff --git a/uploader/phenotypes/models.py b/uploader/phenotypes/models.py
index 3946a0f..3d656d2 100644
--- a/uploader/phenotypes/models.py
+++ b/uploader/phenotypes/models.py
@@ -96,20 +96,34 @@ def dataset_phenotypes(# pylint: disable=[too-many-arguments, too-many-positiona
         xref_ids: tuple[int, ...] = tuple()
 ) -> tuple[dict, ...]:
     """Fetch the actual phenotypes."""
-    _query = (
-        "SELECT pheno.*, pxr.Id AS xref_id, pxr.InbredSetId, ist.InbredSetCode "
+    _narrow_by_ids = (
+            f" AND pxr.Id IN ({', '.join(['%s'] * len(xref_ids))})"
+            if len(xref_ids) > 0 else "")
+    _narrow_by_limit = (
+        f" LIMIT {limit} OFFSET {offset}" if bool(limit) else "")
+    _pub_query = (
+        "SELECT pub.* "
+        "FROM PublishXRef AS pxr "
+        "INNER JOIN  Publication AS pub ON pxr.PublicationId=pub.Id "
+        "WHERE pxr.InbredSetId=%s") + _narrow_by_ids
+    _pheno_query = ((
+        "SELECT pheno.*, pxr.Id AS xref_id, pxr.InbredSetId, pxr.PublicationId, "
+        "ist.InbredSetCode "
         "FROM Phenotype AS pheno "
         "INNER JOIN PublishXRef AS pxr ON pheno.Id=pxr.PhenotypeId "
         "INNER JOIN PublishFreeze AS pf ON pxr.InbredSetId=pf.InbredSetId "
         "INNER JOIN InbredSet AS ist ON pf.InbredSetId=ist.Id "
-        "WHERE pxr.InbredSetId=%s AND pf.Id=%s") + (
-            f" AND pxr.Id IN ({', '.join(['%s'] * len(xref_ids))})"
-            if len(xref_ids) > 0 else "") + (
-            f" LIMIT {limit} OFFSET {offset}" if bool(limit) else "")
+        "WHERE pxr.InbredSetId=%s AND pf.Id=%s") +
+                    _narrow_by_ids +
+                    _narrow_by_limit)
     with conn.cursor(cursorclass=DictCursor) as cursor:
-        cursor.execute(_query, (population_id, dataset_id) + xref_ids)
+        cursor.execute(_pub_query, (population_id,) + xref_ids)
         debug_query(cursor, logger)
-        return tuple(dict(row) for row in cursor.fetchall())
+        _pubs = {row["Id"]: dict(row) for row in cursor.fetchall()}
+        cursor.execute(_pheno_query, (population_id, dataset_id) + xref_ids)
+        debug_query(cursor, logger)
+        return tuple({**dict(row), "publication": _pubs[row["PublicationId"]]}
+                     for row in cursor.fetchall())
 
 
 def __phenotype_se__(cursor: BaseCursor, xref_id, dataids_and_strainids):
diff --git a/uploader/publications/models.py b/uploader/publications/models.py
index dcfa02b..d913144 100644
--- a/uploader/publications/models.py
+++ b/uploader/publications/models.py
@@ -101,6 +101,20 @@ def fetch_publication_by_id(conn: Connection, publication_id: int) -> dict:
         return dict(_res) if _res else {}
 
 
+def fetch_publications_by_ids(
+        conn: Connection, publications_ids: tuple[int, ...]
+) -> tuple[dict, ...]:
+    """Fetch publications with the given IDs."""
+    if len(publications_ids) == 0:
+        return tuple()
+
+    with conn.cursor(cursorclass=DictCursor) as cursor:
+        paramstr = ", ".join(["%s"] * len(publications_ids))
+        cursor.execute(f"SELECT * FROM Publication WHERE Id IN ({paramstr})",
+                       tuple(publications_ids))
+        return tuple(dict(row) for row in cursor.fetchall())
+
+
 def fetch_publication_phenotypes(
         conn: Connection, publication_id: int) -> Iterable[dict]:
     """Fetch all phenotypes linked to this publication."""