Fetch genotype records.

1 files changed, 56 insertions, 5 deletions

diff --git a/uploader/genotypes/models.py b/uploader/genotypes/models.py
index 0305a15..1e09cd3 100644
--- a/uploader/genotypes/models.py
+++ b/uploader/genotypes/models.py
@@ -1,5 +1,6 @@
 """Functions for handling genotypes."""
 from typing import Optional
+from functools import reduce
 from datetime import datetime
 
 import MySQLdb as mdb
@@ -32,13 +33,12 @@ def genotype_markers(
         population_id: int,
         offset: int = 0,
         limit: int = -1# no limit if negative, zero returns empty list.
-) -> tuple[tuple[dict, ...], int, int]:
+) -> tuple[tuple[dict, ...], int]:
     """Retrieve markers from the database.
 
     Return: A tuple of:
-            - Listing of the markers
-            - The total number of markers found in the system
-            - The number of markers that were actually fetched.
+            - Listing of the markers,
+            - The total number of markers found in the system.
     """
     _query_template = (
         "SELECT %%COLS%% "
@@ -70,7 +70,58 @@ def genotype_markers(
             (species_id, population_id))
         debug_query(cursor, app.logger)
         _records = tuple(dict(row) for row in cursor.fetchall())
-        return _records, _total_records, len(_records)
+        return _records, _total_records
+
+
+def genotype_records(
+        conn: mdb.Connection,
+        species_id: int,
+        population_id: int,
+        offset: int = 0,
+        limit: int = -1# no limit if negative, zero returns empty list.
+) -> tuple[tuple[dict, ...], int]:
+    """Retrieve the actual genotype records from the database.
+
+    Returns: A tuple of:
+             - the listing of the genotype data,
+             - the total number of genotype records for this population.
+    """
+    def __organise_geno_records__(acc, row):
+        _current_row = acc.get(row["GenoId"], {
+            "GenoId": row["GenoId"],
+            "data": {}
+        })
+        _current_row["data"][row["StrainName"]] = row["value"]
+        return {
+            **acc,
+            _current_row["GenoId"]: _current_row
+        }
+
+    _query_template = (
+        "SELECT gxr.GenoId, gxr.DataId, gdt.value, strn.Name AS StrainName "
+        "FROM GenoXRef AS gxr "
+        "INNER JOIN GenoData AS gdt ON gxr.DataId = gdt.Id "
+        "INNER JOIN Strain AS strn ON gdt.StrainId = strn.Id "
+        "WHERE gxr.GenoId IN (%%PARAMS_STR%%)")
+
+    with conn.cursor(cursorclass=DictCursor) as cursor:
+        _markers, _num_records = genotype_markers(
+            conn, species_id, population_id, offset, limit)
+        _genoids = tuple(_marker["Id"] for _marker in _markers)
+        cursor.execute(
+            _query_template.replace(
+                "%%PARAMS_STR%%", ",".join(["%s"] * len(_genoids))),
+            _genoids)
+        _records: dict[str, dict] = reduce(
+            __organise_geno_records__, cursor.fetchall(), {})
+        return (
+            tuple({
+                **_marker,
+                "data": _records.get(
+                    _marker["Id"], {}
+                ).get("data", {})
+            } for _marker in _markers),
+            _num_records)
 
 
 def genotype_dataset(