Fetch genetic markers by both species and population. HEAD main

A species can have more markers than are considered in a particular
population. This update changes the code to filter the markers by both
species and population to ensure only the markers referenced in the
population actually show up.

1 files changed, 27 insertions, 9 deletions

diff --git a/uploader/genotypes/models.py b/uploader/genotypes/models.py
index 34d2cfe..0305a15 100644
--- a/uploader/genotypes/models.py
+++ b/uploader/genotypes/models.py
@@ -29,30 +29,48 @@ def genotype_markers_count(conn: mdb.Connection, species_id: int) -> int:
 def genotype_markers(
         conn: mdb.Connection,
         species_id: int,
+        population_id: int,
         offset: int = 0,
-        limit: Optional[int] = None
-) -> tuple[tuple[dict, ...], int]:
-    """Retrieve markers from the database."""
+        limit: int = -1# no limit if negative, zero returns empty list.
+) -> tuple[tuple[dict, ...], int, 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.
+    """
     _query_template = (
-        "SELECT %%COLS%% FROM Geno AS gno "
-        "WHERE gno.SpeciesId=%s "
+        "SELECT %%COLS%% "
+        "FROM Species AS spc "
+        "INNER JOIN InbredSet AS iset "
+        "ON spc.Id = iset.SpeciesId "
+        "INNER JOIN GenoFreeze AS gfr "
+        "ON iset.Id = gfr.InbredSetId "
+        "INNER JOIN GenoXRef AS gxr "
+        "ON gfr.Id = gxr.GenoFreezeId "
+        "INNER JOIN Geno AS gno "
+        "ON gxr.GenoId = gno.Id "
+        "WHERE spc.Id=%s "
+        "AND iset.Id=%s "
         "%%LIMIT%%")
 
     with conn.cursor(cursorclass=DictCursor) as cursor:
         cursor.execute(
             _query_template.replace("%%LIMIT%%", "").replace(
                 "%%COLS%%", "COUNT(gno.Id) AS total_records"),
-            (species_id,))
+            (species_id, population_id))
         _total_records = cursor.fetchone()["total_records"]
         cursor.execute(
             _query_template.replace("%%COLS%%", "gno.*").replace(
                 "%%LIMIT%%",
                 (f"LIMIT {int(limit)} OFFSET {int(offset)}"
-                 if bool(limit) and limit > 0
+                 if bool(limit) and limit >= 0
                  else "")),
-            (species_id,))
+            (species_id, population_id))
         debug_query(cursor, app.logger)
-        return tuple(dict(row) for row in cursor.fetchall()), _total_records
+        _records = tuple(dict(row) for row in cursor.fetchall())
+        return _records, _total_records, len(_records)
 
 
 def genotype_dataset(