From 73be62c9e5ef5c4b3a87d3fcdc46f338679bf5e1 Mon Sep 17 00:00:00 2001
From: Artem Tarasov
Date: Wed, 13 May 2015 14:16:14 +0300
Subject: combine three similar queries into one

---
 wqflask/base/data_set.py | 59 ++++++++++++++++++++++++------------------------
 1 file changed, 30 insertions(+), 29 deletions(-)

(limited to 'wqflask')

diff --git a/wqflask/base/data_set.py b/wqflask/base/data_set.py
index f5bd5cb8..706b193d 100755
--- a/wqflask/base/data_set.py
+++ b/wqflask/base/data_set.py
@@ -319,38 +319,39 @@ class DatasetGroup(object):
         dataset_menu = []
         print("[tape4] webqtlConfig.PUBLICTHRESH:", webqtlConfig.PUBLICTHRESH)
         print("[tape4] type webqtlConfig.PUBLICTHRESH:", type(webqtlConfig.PUBLICTHRESH))
-        results = g.db.execute("""SELECT PublishFreeze.FullName,PublishFreeze.Name FROM
-                PublishFreeze,InbredSet WHERE PublishFreeze.InbredSetId = InbredSet.Id
-                and InbredSet.Name = %s and PublishFreeze.public > %s""",
-                (self.name, webqtlConfig.PUBLICTHRESH))
-        for item in results.fetchall():
-            dataset_menu.append(dict(tissue=None,
-                                     datasets=[item]))
-
-        results = g.db.execute("""SELECT GenoFreeze.FullName,GenoFreeze.Name FROM GenoFreeze,
-                InbredSet WHERE GenoFreeze.InbredSetId = InbredSet.Id and InbredSet.Name =
-                %s and GenoFreeze.public > %s""",
-                (self.name, webqtlConfig.PUBLICTHRESH))
-        for item in results.fetchall():
-            dataset_menu.append(dict(tissue=None,
-                                datasets=[item]))
-
-        #03/09/2009: Xiaodong changed the SQL query to order by Name as requested by Rob.
         results = g.db.execute('''
-            SELECT Tissue.Name, ProbeSetFreeze.FullName,ProbeSetFreeze.Name
-            FROM ProbeSetFreeze, ProbeFreeze, InbredSet, Tissue
-            WHERE ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id and
-                  ProbeFreeze.TissueId = Tissue.Id and
-                  ProbeSetFreeze.public > %s and
-                  ProbeFreeze.InbredSetId = InbredSet.Id and
-                  InbredSet.Name like %s
-            ORDER BY Tissue.Name, ProbeSetFreeze.CreateTime desc, ProbeSetFreeze.AvgId
-            ''', (webqtlConfig.PUBLICTHRESH, "%" + self.name + "%"))
+             (SELECT '#PublishFreeze',PublishFreeze.FullName,PublishFreeze.Name
+              FROM PublishFreeze,InbredSet
+              WHERE PublishFreeze.InbredSetId = InbredSet.Id
+                and InbredSet.Name = %s
+                and PublishFreeze.public > %s)
+             UNION
+             (SELECT '#GenoFreeze',GenoFreeze.FullName,GenoFreeze.Name
+              FROM GenoFreeze, InbredSet
+              WHERE GenoFreeze.InbredSetId = InbredSet.Id
+                and InbredSet.Name = %s
+                and GenoFreeze.public > %s)
+             UNION
+             (SELECT Tissue.Name, ProbeSetFreeze.FullName,ProbeSetFreeze.Name
+              FROM ProbeSetFreeze, ProbeFreeze, InbredSet, Tissue
+              WHERE ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id
+                and ProbeFreeze.TissueId = Tissue.Id
+                and ProbeFreeze.InbredSetId = InbredSet.Id
+                and InbredSet.Name like %s
+                and ProbeSetFreeze.public > %s
+              ORDER BY Tissue.Name, ProbeSetFreeze.CreateTime desc, ProbeSetFreeze.AvgId)
+            ''', (self.name, webqtlConfig.PUBLICTHRESH,
+                  self.name, webqtlConfig.PUBLICTHRESH,
+                  "%" + self.name + "%", webqtlConfig.PUBLICTHRESH))
 
         for tissue_name, dataset in itertools.groupby(results.fetchall(), itemgetter(0)):
-            dataset_sub_menu = [item[1:] for item in dataset]
-            dataset_menu.append(dict(tissue=tissue_name,
-                                     datasets=dataset_sub_menu))
+            if tissue_name in ['#PublishFreeze', '#GenoFreeze']:
+                for item in dataset:
+                    dataset_menu.append(dict(tissue=None, datasets=[item[1:]]))
+            else:
+                dataset_sub_menu = [item[1:] for item in dataset]
+                dataset_menu.append(dict(tissue=tissue_name,
+                                    datasets=dataset_sub_menu))
 
         Redis.set(key, pickle.dumps(dataset_menu, pickle.HIGHEST_PROTOCOL))
         Redis.expire(key, 60*5)
-- 
cgit v1.2.3