Merge branch 'chores/gn2-queries-optimization' of github.com:Alexanderlacuna/genenetwork2 into Alexanderlacuna-chores/gn2-queries-optimization

2 files changed, 49 insertions, 6 deletions

diff --git a/wqflask/wqflask/correlation/rust_correlation.py b/wqflask/wqflask/correlation/rust_correlation.py
index a4cef6b8..5109c72e 100644
--- a/wqflask/wqflask/correlation/rust_correlation.py
+++ b/wqflask/wqflask/correlation/rust_correlation.py
@@ -15,6 +15,53 @@ from gn3.computations.rust_correlation import parse_tissue_corr_data
 from gn3.db_utils import database_connector
 
 
+def query_probes_metadata(dataset, trait_list):
+    """query traits metadata in bulk for probeset"""
+
+    with database_connector() as conn:
+        with conn.cursor() as cursor:
+
+            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
+                """.format(", ".join(["%s"] * len(trait_list)))
+
+            cursor.execute(query,
+                           (tuple(trait_list) +
+                            (dataset.group.species,) + (dataset.name,))
+                           )
+
+            return cursor.fetchall()
+
+
+def get_metadata(dataset, traits):
+
+    return {trait_name: {
+            "name": trait_name,
+            "view": True,
+            "symbol": symbol,
+            "dataset": dataset.name,
+            "dataset_name": dataset.shortname,
+            "mean": mean,
+            "description": description,
+            "additive": additive,
+            "lrs_score": f"{lrs:3.1f}",
+            "location": f"Chr{probe_chr}: {probe_mb:.6f}",
+            "lrs_location": f"Chr{chr_score}: {mb:.6f}"
+
+            } for trait_name, probe_chr, probe_mb, symbol, mean, description,
+            additive, lrs, chr_score, mb
+            in query_probes_metadata(dataset, traits)}
+
+
 def chunk_dataset(dataset, steps, name):
 
     results = []
@@ -268,5 +315,6 @@ def compute_correlation_rust(
             results, top_a, top_b),
         "this_trait": this_trait.name,
         "target_dataset": start_vars['corr_dataset'],
+        "traits_metadata": get_metadata(target_dataset, list(results.keys())),
         "return_results": n_top
     }
diff --git a/wqflask/wqflask/correlation/show_corr_results.py b/wqflask/wqflask/correlation/show_corr_results.py
index f5fdd9b3..4910b0e0 100644
--- a/wqflask/wqflask/correlation/show_corr_results.py
+++ b/wqflask/wqflask/correlation/show_corr_results.py
@@ -93,8 +93,7 @@ def correlation_json_for_table(correlation_data, this_trait, this_dataset, targe
 
     new_traits_metadata = {}
 
-    (file_path, dataset_metadata) = fetch_all_cached_metadata(
-        target_dataset['name'])
+    dataset_metadata = correlation_data["traits_metadata"]
 
     for i, trait_dict in enumerate(corr_results):
         trait_name = list(trait_dict.keys())[0]
@@ -187,10 +186,6 @@ def correlation_json_for_table(correlation_data, this_trait, this_dataset, targe
 
         results_list.append(results_dict)
 
-    cache_new_traits_metadata(dataset_metadata,
-                              new_traits_metadata,
-                              file_path)
-
     return json.dumps(results_list)