From 47849b53b9fdff99a1361e458c44d81cc1e1b3a8 Mon Sep 17 00:00:00 2001
From: zsloan
Date: Mon, 8 Jul 2024 19:18:53 +0000
Subject: Add Peak Chr and Peak Mb columns to ProbeSet export in the GN2 REST
 API

---
 gn2/wqflask/api/router.py | 33 +++++++++++++++++++--------------
 1 file changed, 19 insertions(+), 14 deletions(-)

diff --git a/gn2/wqflask/api/router.py b/gn2/wqflask/api/router.py
index bcd08e8d..e9b70919 100644
--- a/gn2/wqflask/api/router.py
+++ b/gn2/wqflask/api/router.py
@@ -379,23 +379,28 @@ def fetch_traits(dataset_name, file_format="json"):
         if len(trait_ids) > 0:
             if data_type == "ProbeSet":
                 query = """
-                            SELECT
-                                ProbeSet.Id, ProbeSet.Name, ProbeSet.Symbol, ProbeSet.description, ProbeSet.Chr, ProbeSet.Mb, ProbeSet.alias,
-                                ProbeSetXRef.mean, ProbeSetXRef.se, ProbeSetXRef.Locus, ProbeSetXRef.LRS, ProbeSetXRef.pValue, ProbeSetXRef.additive, ProbeSetXRef.h2
-                            FROM
-                                ProbeSet, ProbeSetXRef, ProbeSetFreeze
+                            SELECT DISTINCT
+                                ProbeSet.`Id`, ProbeSet.`Name`, ProbeSet.`Symbol`, ProbeSet.`description`,
+                                ProbeSet.`Chr`, ProbeSet.`Mb`, ProbeSet.`alias`, ProbeSetXRef.`mean`,
+                                ProbeSetXRef.`se`, ProbeSetXRef.`Locus`, ProbeSetXRef.`LRS`,
+                                ProbeSetXRef.`pValue`, ProbeSetXRef.`additive`, ProbeSetXRef.`h2`,
+                                Geno.`Chr`, Geno.`Mb`
+                            FROM 
+                                Species
+                                INNER JOIN InbredSet ON InbredSet.`SpeciesId`= Species.`Id`
+                                INNER JOIN ProbeFreeze ON ProbeFreeze.`InbredSetId` = InbredSet.`Id`
+                                INNER JOIN Tissue ON ProbeFreeze.`TissueId` = Tissue.`Id`
+                                INNER JOIN ProbeSetFreeze ON ProbeSetFreeze.`ProbeFreezeId` = ProbeFreeze.`Id`
+                                INNER JOIN ProbeSetXRef ON ProbeSetXRef.`ProbeSetFreezeId` = ProbeSetFreeze.`Id`
+                                INNER JOIN ProbeSet ON ProbeSet.`Id` = ProbeSetXRef.`ProbeSetId`
+                                LEFT JOIN Geno ON ProbeSetXRef.`Locus` = Geno.`Name` AND Geno.`SpeciesId` = Species.`Id`
                             WHERE
-                                ProbeSetXRef.ProbeSetFreezeId = "{0}" AND
-                                ProbeSetXRef.ProbeSetId = ProbeSet.Id AND
-                                ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id AND
-                                ProbeSetFreeze.public > 0 AND
-                                ProbeSetFreeze.confidentiality < 1
+                                ProbeSetXRef.ProbeSetFreezeId = "{0}"
                             ORDER BY
-                                ProbeSet.Id
-                        """
+                                ProbeSet.Id"""
 
-                field_list = ["Id", "Name", "Symbol", "Description", "Chr", "Mb",
-                              "Aliases", "Mean", "SE", "Locus", "LRS", "P-Value", "Additive", "h2"]
+                field_list = ["Id", "Name", "Symbol", "Description", "Chr", "Mb", "Aliases", "Mean",
+                              "SE", "Locus", "LRS", "P-Value", "Additive", "h2", "Peak Chr", "Peak Mb"]
             elif data_type == "Geno":
                 query = """
                             SELECT
-- 
cgit v1.2.3