aboutsummaryrefslogtreecommitdiff
path: root/wqflask/utility/helper_functions.py
diff options
context:
space:
mode:
authorBonfaceKilz2021-04-19 17:46:38 +0300
committerBonfaceKilz2021-04-23 17:42:26 +0300
commit4534daa6fb07c23b90e024560ca64091fc330eed (patch)
tree927e0d9488e7605fad66f84957b2557cf6ff7764 /wqflask/utility/helper_functions.py
parentb0ccb12682fed83bf72d22ff42f1f442a8e6176e (diff)
downloadgenenetwork2-4534daa6fb07c23b90e024560ca64091fc330eed.tar.gz
Move looped sql query into one statement in "get_species_groups"
It's in-efficient to have a sql query executed in a loop. As data grows, the query becomes slower. It's better to let sql handle such queries.
Diffstat (limited to 'wqflask/utility/helper_functions.py')
-rw-r--r--wqflask/utility/helper_functions.py29
1 files changed, 14 insertions, 15 deletions
diff --git a/wqflask/utility/helper_functions.py b/wqflask/utility/helper_functions.py
index 15d5b3ab..4ba92ed5 100644
--- a/wqflask/utility/helper_functions.py
+++ b/wqflask/utility/helper_functions.py
@@ -47,19 +47,18 @@ def get_trait_db_obs(self, trait_db_list):
if trait_ob:
self.trait_list.append((trait_ob, dataset_ob))
-def get_species_groups():
-
- species_query = "SELECT SpeciesId, MenuName FROM Species"
- species_ids_and_names = g.db.execute(species_query).fetchall()
-
- species_and_groups = []
- for species_id, species_name in species_ids_and_names:
- this_species_groups = {}
- this_species_groups['species'] = species_name
- groups_query = "SELECT InbredSetName FROM InbredSet WHERE SpeciesId = %s" % (species_id)
- groups = [group[0] for group in g.db.execute(groups_query).fetchall()]
- this_species_groups['groups'] = groups
- species_and_groups.append(this_species_groups)
-
- return species_and_groups
+def get_species_groups():
+ """Group each species into a group"""
+ _menu = {}
+ for species, group_name in g.db.execute(
+ "SELECT s.MenuName, i.InbredSetName FROM InbredSet i "
+ "INNER JOIN Species s ON s.SpeciesId = i.SpeciesId "
+ "ORDER BY i.SpeciesId ASC, i.Name ASC").fetchall():
+ if _menu.get(species):
+ _menu = _menu[species].append(group_name)
+ else:
+ _menu[species] = [group_name]
+ return [{"species": key,
+ "groups": value} for key, value in
+ list(_menu.items())]