From 96d7b81cff1b4cf9334b5e37556e0323ac9ef154 Mon Sep 17 00:00:00 2001
From: Frederick Muriuki Muriithi
Date: Tue, 6 Sep 2022 13:41:34 +0300
Subject: Refactor: Use joins for queries

Use joins to relate tables when fetching data.
---
 wqflask/wqflask/correlation/rust_correlation.py | 22 +++++++++++++---------
 1 file changed, 13 insertions(+), 9 deletions(-)

(limited to 'wqflask')

diff --git a/wqflask/wqflask/correlation/rust_correlation.py b/wqflask/wqflask/correlation/rust_correlation.py
index 5109c72e..a4a08a54 100644
--- a/wqflask/wqflask/correlation/rust_correlation.py
+++ b/wqflask/wqflask/correlation/rust_correlation.py
@@ -23,15 +23,19 @@ def query_probes_metadata(dataset, trait_list):
 
             query = """
                     SELECT ProbeSet.Name,ProbeSet.Chr,ProbeSet.Mb,
-                    Symbol,mean,description,additive,LRS,Geno.Chr, Geno.Mb
-                    from Geno, Species,ProbeSet,ProbeSetXRef,ProbeSetFreeze
-                    where ProbeSet.Name in ({}) and
-                    Species.Name = %s and
-                    Geno.Name = ProbeSetXRef.Locus and
-                    Geno.SpeciesId = Species.Id and
-                    ProbeSet.Id=ProbeSetXRef.ProbeSetId and
-                    ProbeSetFreeze.Id = ProbeSetXRef.ProbeSetFreezeId and
-                    ProbeSetFreeze.Name = %s
+                    ProbeSet.Symbol,ProbeSetXRef.mean,ProbeSet.description,
+                    ProbeSetXRef.additive,ProbeSetXRef.LRS,Geno.Chr, Geno.Mb
+                    FROM ProbeSet INNER JOIN ProbeSetXRef
+                    ON ProbeSet.Id=ProbeSetXRef.ProbeSetId
+                    INNER JOIN Geno
+                    ON ProbeSetXRef.Locus = Geno.Name
+                    INNER JOIN Species
+                    ON Geno.SpeciesId = Species.Id
+                    WHERE ProbeSet.Name in ({}) AND
+                    Species.Name = %s AND
+                    ProbeSetXRef.ProbeSetFreezeId IN (
+                      SELECT ProbeSetFreeze.Id
+                      FROM ProbeSetFreeze WHERE ProbeSetFreeze.Name = %s)
                 """.format(", ".join(["%s"] * len(trait_list)))
 
             cursor.execute(query,
-- 
cgit v1.2.3