modify the main query in gene global search to add locus chr, locus mb and optimize the speed of query

1 files changed, 22 insertions, 15 deletions

diff --git a/wqflask/wqflask/gsearch.py b/wqflask/wqflask/gsearch.py
index 9e46c0c5..6ff57563 100644
--- a/wqflask/wqflask/gsearch.py
+++ b/wqflask/wqflask/gsearch.py
@@ -1,4 +1,6 @@
 import json
+import datetime as dt
+from types import SimpleNamespace
 
 from flask import Flask, g
 from base.data_set import create_dataset
@@ -9,8 +11,9 @@ from base import webqtlConfig
 
 from utility import hmac
 
-from utility.type_checking import is_float, is_int, is_str, get_float, get_int, get_string
 from utility.benchmark import Bench
+from utility.authentication_tools import check_resource_availability
+from utility.type_checking import is_float, is_int, is_str, get_float, get_int, get_string
 
 from utility.logger import getLogger
 logger = getLogger(__name__)
@@ -42,15 +45,19 @@ class GSearch(object):
                 ProbeSetXRef.LRS AS lrs,
                 ProbeSetXRef.`Locus` AS locus,
                 ProbeSetXRef.`pValue` AS pvalue,
-                ProbeSetXRef.`additive` AS additive
-                FROM Species, InbredSet, ProbeSetXRef, ProbeSet, ProbeFreeze, ProbeSetFreeze, Tissue
-                WHERE InbredSet.`SpeciesId`=Species.`Id`
-                AND ProbeFreeze.InbredSetId=InbredSet.`Id`
-                AND ProbeFreeze.`TissueId`=Tissue.`Id`
-                AND ProbeSetFreeze.ProbeFreezeId=ProbeFreeze.Id
-                AND ( MATCH (ProbeSet.Name,ProbeSet.description,ProbeSet.symbol,alias,GenbankId, UniGeneId, Probe_Target_Description) AGAINST ('%s' IN BOOLEAN MODE) )
-                AND ProbeSet.Id = ProbeSetXRef.ProbeSetId
-                AND ProbeSetXRef.ProbeSetFreezeId=ProbeSetFreeze.Id
+                ProbeSetXRef.`additive` AS additive,
+                ProbeSetFreeze.Id AS probesetfreeze_id,
+                Geno.Chr as geno_chr,
+                Geno.Mb as 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 ( MATCH (ProbeSet.Name,ProbeSet.description,ProbeSet.symbol,ProbeSet.alias,ProbeSet.GenbankId, ProbeSet.UniGeneId, ProbeSet.Probe_Target_Description) AGAINST ('%s' IN BOOLEAN MODE) )
                 AND ProbeSetFreeze.confidentiality < 1
                 AND ProbeSetFreeze.public > 0
                 ORDER BY species_name, inbredset_name, tissue_name, probesetfreeze_name, probeset_name
@@ -90,16 +97,16 @@ class GSearch(object):
                     this_trait['additive'] = "N/A"
                     if line[14] != "" and line[14] != None:
                         this_trait['additive'] = '%.3f' % line[14]
+                    this_trait['dataset_id'] = line[15]
+                    this_trait['locus_chr'] = line[16]
+                    this_trait['locus_mb'] = line[17]
 
                     #dataset = create_dataset(line[3], "ProbeSet", get_samplelist=False)
                     #trait_id = line[4]
                     #with Bench("Building trait object"):
-                    trait_ob = create_trait(dataset_name=this_trait['dataset'], name=this_trait['name'], get_qtl_info=True, get_sample_info=False)
-                    if not trait_ob:
-                        continue
                     max_lrs_text = "N/A"
-                    if trait_ob.locus_chr != "" and trait_ob.locus_mb != "":
-                        max_lrs_text = "Chr" + str(trait_ob.locus_chr) + ": " + str(trait_ob.locus_mb)
+                    if this_trait['locus_chr'] != None and this_trait['locus_mb'] != None:
+                        max_lrs_text = "Chr" + str(this_trait['locus_chr']) + ": " + str(this_trait['locus_mb'])
                     this_trait['max_lrs_text'] = max_lrs_text
 
                     trait_list.append(this_trait)