From 6c03a5aeb95f37c477bed3f432790329a0bd5038 Mon Sep 17 00:00:00 2001 From: zsloan Date: Mon, 11 Apr 2016 21:46:04 +0000 Subject: Figured out how to load results into dataTable using AJAX, but still need to get it working with Scroller correctly Added code getting the description, etc to trait.py since it was missed from last commit --- wqflask/base/trait.py | 55 ++++++++- wqflask/wqflask/gsearch.py | 166 ++++++++++++++-------------- wqflask/wqflask/templates/gsearch_gene.html | 60 +++------- wqflask/wqflask/update_search_results.py | 130 ++++++++++++++++++++++ wqflask/wqflask/views.py | 13 ++- 5 files changed, 293 insertions(+), 131 deletions(-) create mode 100644 wqflask/wqflask/update_search_results.py diff --git a/wqflask/base/trait.py b/wqflask/base/trait.py index ce3a7608..0bc10b97 100644 --- a/wqflask/base/trait.py +++ b/wqflask/base/trait.py @@ -2,7 +2,7 @@ from __future__ import absolute_import, division, print_function import string import resource - +import codecs from htmlgen import HTMLgen2 as HT @@ -309,13 +309,50 @@ class GeneralTrait(object): holder = unicode(trait_info[i], "utf8", "ignore") setattr(self, field, holder) + description_string = unicode(str(self.description).strip(codecs.BOM_UTF8), 'utf-8') + target_string = unicode(str(self.probe_target_description).strip(codecs.BOM_UTF8), 'utf-8') + + if len(description_string) > 1 and description_string != 'None': + description_display = description_string + else: + description_display = self.symbol + + if (len(description_display) > 1 and description_display != 'N/A' and + len(target_string) > 1 and target_string != 'None'): + description_display = description_display + '; ' + target_string.strip() + + # Save it for the jinja2 template + self.description_display = description_display + + #XZ: trait_location_value is used for sorting + trait_location_repr = 'N/A' + trait_location_value = 1000000 + + if self.chr and self.mb: + #Checks if the chromosome number can be cast to an int (i.e. isn't "X" or "Y") + #This is so we can convert the location to a number used for sorting + trait_location_value = convert_location_to_value(self.chr, self.mb) + #try: + # trait_location_value = int(self.chr)*1000 + self.mb + #except ValueError: + # if self.chr.upper() == 'X': + # trait_location_value = 20*1000 + self.mb + # else: + # trait_location_value = (ord(str(self.chr).upper()[0])*1000 + + # self.mb) + + #ZS: Put this in function currently called "convert_location_to_value" + self.location_repr = 'Chr%s: %.6f' % (self.chr, float(self.mb)) + self.location_value = trait_location_value + + if self.dataset.type == 'Publish': self.confidential = 0 if self.pre_publication_description and not self.pubmed_id: self.confidential = 1 - - description = self.post_publication_description + description = self.post_publication_description + #If the dataset is confidential and the user has access to confidential #phenotype traits, then display the pre-publication description instead #of the post-publication description @@ -662,7 +699,17 @@ class GeneralTrait(object): ZValue = ZValue*sqrt(self.overlap-3) self.p_value = 2.0*(1.0 - reaper.normp(abs(ZValue))) - +def convert_location_to_value(chromosome, mb): + try: + location_value = int(chromosome)*1000 + float(mb) + except ValueError: + if chromosome.upper() == 'X': + location_value = 20*1000 + float(mb) + else: + location_value = (ord(str(chromosome).upper()[0])*1000 + + float(mb)) + + return location_value @app.route("/trait/get_sample_data") def get_sample_data(): diff --git a/wqflask/wqflask/gsearch.py b/wqflask/wqflask/gsearch.py index 4cd3874c..4f9dc316 100644 --- a/wqflask/wqflask/gsearch.py +++ b/wqflask/wqflask/gsearch.py @@ -1,94 +1,94 @@ from __future__ import absolute_import, print_function, division from flask import Flask, g -from base.data_set import create_dataset -from base.trait import GeneralTrait -from dbFunction import webqtlDatabaseFunction +#from base.data_set import create_dataset +#from base.trait import GeneralTrait +#from dbFunction import webqtlDatabaseFunction -from utility.benchmark import Bench +#from utility.benchmark import Bench class GSearch(object): def __init__(self, kw): self.type = kw['type'] self.terms = kw['terms'] - if self.type == "gene": - sql = """ - SELECT - Species.`Name` AS species_name, - InbredSet.`Name` AS inbredset_name, - Tissue.`Name` AS tissue_name, - ProbeSetFreeze.Name AS probesetfreeze_name, - ProbeSet.Name AS probeset_name, - ProbeSet.Symbol AS probeset_symbol, - ProbeSet.`description` AS probeset_description, - ProbeSet.Chr AS chr, - ProbeSet.Mb AS mb, - ProbeSetXRef.Mean AS mean, - 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 - AND ProbeSetFreeze.public > 0 - ORDER BY species_name, inbredset_name, tissue_name, probesetfreeze_name, probeset_name - LIMIT 6000 - """ % (self.terms) - with Bench("Running query"): - re = g.db.execute(sql).fetchall() - self.trait_list = [] - with Bench("Creating trait objects"): - for line in re: - dataset = create_dataset(line[3], "ProbeSet", get_samplelist=False) - trait_id = line[4] - #with Bench("Building trait object"): - this_trait = GeneralTrait(dataset=dataset, name=trait_id, get_qtl_info=True, get_sample_info=False) - self.trait_list.append(this_trait) + # if self.type == "gene": + # sql = """ + # SELECT + # Species.`Name` AS species_name, + # InbredSet.`Name` AS inbredset_name, + # Tissue.`Name` AS tissue_name, + # ProbeSetFreeze.Name AS probesetfreeze_name, + # ProbeSet.Name AS probeset_name, + # ProbeSet.Symbol AS probeset_symbol, + # ProbeSet.`description` AS probeset_description, + # ProbeSet.Chr AS chr, + # ProbeSet.Mb AS mb, + # ProbeSetXRef.Mean AS mean, + # 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 + # AND ProbeSetFreeze.public > 0 + # ORDER BY species_name, inbredset_name, tissue_name, probesetfreeze_name, probeset_name + # LIMIT 6000 + # """ % (self.terms) + # with Bench("Running query"): + # re = g.db.execute(sql).fetchall() + # self.trait_list = [] + # with Bench("Creating trait objects"): + # for line in re: + # dataset = create_dataset(line[3], "ProbeSet", get_samplelist=False) + # trait_id = line[4] + # with Bench("Building trait object"): + # this_trait = GeneralTrait(dataset=dataset, name=trait_id, get_qtl_info=True, get_sample_info=False) + # self.trait_list.append(this_trait) - elif self.type == "phenotype": - sql = """ - SELECT - Species.`Name`, - InbredSet.`Name`, - PublishFreeze.`Name`, - PublishXRef.`Id`, - Phenotype.`Post_publication_description`, - Publication.`Authors`, - Publication.`Year`, - PublishXRef.`LRS`, - PublishXRef.`Locus`, - PublishXRef.`additive` - FROM Species,InbredSet,PublishFreeze,PublishXRef,Phenotype,Publication - WHERE PublishXRef.`InbredSetId`=InbredSet.`Id` - AND PublishFreeze.`InbredSetId`=InbredSet.`Id` - AND InbredSet.`SpeciesId`=Species.`Id` - AND PublishXRef.`PhenotypeId`=Phenotype.`Id` - AND PublishXRef.`PublicationId`=Publication.`Id` - AND (Phenotype.Post_publication_description REGEXP "[[:<:]]%s[[:>:]]" - OR Phenotype.Pre_publication_description REGEXP "[[:<:]]%s[[:>:]]" - OR Phenotype.Pre_publication_abbreviation REGEXP "[[:<:]]%s[[:>:]]" - OR Phenotype.Post_publication_abbreviation REGEXP "[[:<:]]%s[[:>:]]" - OR Phenotype.Lab_code REGEXP "[[:<:]]%s[[:>:]]" - OR Publication.PubMed_ID REGEXP "[[:<:]]%s[[:>:]]" - OR Publication.Abstract REGEXP "[[:<:]]%s[[:>:]]" - OR Publication.Title REGEXP "[[:<:]]%s[[:>:]]" - OR Publication.Authors REGEXP "[[:<:]]%s[[:>:]]" - OR PublishXRef.Id REGEXP "[[:<:]]%s[[:>:]]") - ORDER BY Species.`Name`, InbredSet.`Name`, PublishXRef.`Id` - LIMIT 6000 - """ % (self.terms, self.terms, self.terms, self.terms, self.terms, self.terms, self.terms, self.terms, self.terms, self.terms) - re = g.db.execute(sql).fetchall() - self.trait_list = [] - with Bench("Creating trait objects"): - for line in re: - dataset = create_dataset(line[2], "Publish") - trait_id = line[3] - this_trait = GeneralTrait(dataset=dataset, name=trait_id, get_qtl_info=True, get_sample_info=False) - self.trait_list.append(this_trait) + # elif self.type == "phenotype": + # sql = """ + # SELECT + # Species.`Name`, + # InbredSet.`Name`, + # PublishFreeze.`Name`, + # PublishXRef.`Id`, + # Phenotype.`Post_publication_description`, + # Publication.`Authors`, + # Publication.`Year`, + # PublishXRef.`LRS`, + # PublishXRef.`Locus`, + # PublishXRef.`additive` + # FROM Species,InbredSet,PublishFreeze,PublishXRef,Phenotype,Publication + # WHERE PublishXRef.`InbredSetId`=InbredSet.`Id` + # AND PublishFreeze.`InbredSetId`=InbredSet.`Id` + # AND InbredSet.`SpeciesId`=Species.`Id` + # AND PublishXRef.`PhenotypeId`=Phenotype.`Id` + # AND PublishXRef.`PublicationId`=Publication.`Id` + # AND (Phenotype.Post_publication_description REGEXP "[[:<:]]%s[[:>:]]" + # OR Phenotype.Pre_publication_description REGEXP "[[:<:]]%s[[:>:]]" + # OR Phenotype.Pre_publication_abbreviation REGEXP "[[:<:]]%s[[:>:]]" + # OR Phenotype.Post_publication_abbreviation REGEXP "[[:<:]]%s[[:>:]]" + # OR Phenotype.Lab_code REGEXP "[[:<:]]%s[[:>:]]" + # OR Publication.PubMed_ID REGEXP "[[:<:]]%s[[:>:]]" + # OR Publication.Abstract REGEXP "[[:<:]]%s[[:>:]]" + # OR Publication.Title REGEXP "[[:<:]]%s[[:>:]]" + # OR Publication.Authors REGEXP "[[:<:]]%s[[:>:]]" + # OR PublishXRef.Id REGEXP "[[:<:]]%s[[:>:]]") + # ORDER BY Species.`Name`, InbredSet.`Name`, PublishXRef.`Id` + # LIMIT 6000 + # """ % (self.terms, self.terms, self.terms, self.terms, self.terms, self.terms, self.terms, self.terms, self.terms, self.terms) + # re = g.db.execute(sql).fetchall() + # self.trait_list = [] + # with Bench("Creating trait objects"): + # for line in re: + # dataset = create_dataset(line[2], "Publish") + # trait_id = line[3] + # this_trait = GeneralTrait(dataset=dataset, name=trait_id, get_qtl_info=True, get_sample_info=False) + # self.trait_list.append(this_trait) diff --git a/wqflask/wqflask/templates/gsearch_gene.html b/wqflask/wqflask/templates/gsearch_gene.html index 7cc9a1bd..2ff75052 100755 --- a/wqflask/wqflask/templates/gsearch_gene.html +++ b/wqflask/wqflask/templates/gsearch_gene.html @@ -13,6 +13,7 @@
+

You searched for {{ terms }}.

To study a record, click on its ID below.
Check records below and click Add button to add to selection.

@@ -27,44 +28,6 @@

- - - - - - - - - - - - - - - - - - - - {% for this_trait in trait_list %} - - - - - - - - - - - - - - - - - {% endfor %} -
IndexSpeciesGroupTissueDatasetRecordSymbolDescriptionLocationMeanMax  
LRS ?		Max LRS LocationAdditive
Effect ?
{{ loop.index }}{{ this_trait.dataset.group.species }}{{ this_trait.dataset.group.name }}{{ this_trait.dataset.tissue }}{{ this_trait.dataset.fullname }}{{ this_trait.name }}{{ this_trait.symbol }}{{ this_trait.description_display }}{{ this_trait.location_repr }}{{ '%0.3f' % this_trait.mean|float }}{{ '%0.3f' % this_trait.LRS_score_repr|float }}{{ this_trait.LRS_location_repr }}{{ '%0.3f' % this_trait.additive|float }}
@@ -156,6 +119,17 @@ console.time("Creating table"); $('#trait_table').DataTable( { + "processing": true, + "serverSide": true, + "paging": false, + "ajax": { + "url": "/gsearch_updating?terms={{ terms }}&type={{ type }}&draw=100", + "dataType": "json", + "contentType": "application/json; charset=utf-8", + "complete": function(response) { + console.log("Response:" + JSON.stringify(response)); + } + }, "columns": [ { "type": "natural" }, { "type": "natural" }, @@ -184,15 +158,15 @@ } } ], - "sDom": "RZBtir", - "autoWidth": false, - "bLengthChange": true, + "sDom": "RZBfrtiS", + "scrollY": 800, "bDeferRender": true, - "scrollCollapse": false, + "scroller": { + "loadingIndicator": true + }, "colResize": { "tableWidthFixed": false, }, - "paging": false } ); console.timeEnd("Creating table"); diff --git a/wqflask/wqflask/update_search_results.py b/wqflask/wqflask/update_search_results.py new file mode 100644 index 00000000..976fc1cd --- /dev/null +++ b/wqflask/wqflask/update_search_results.py @@ -0,0 +1,130 @@ +from __future__ import absolute_import, print_function, division + +import json + +from flask import Flask, g +from base.data_set import create_dataset +from base.trait import GeneralTrait +from dbFunction import webqtlDatabaseFunction + +from utility.benchmark import Bench + +class GSearch(object): + + def __init__(self, kw): + self.type = kw['type'] + self.terms = kw['terms'] + self.draw = int(kw['draw']) + #self.row_range = kw['row_range'] + if self.type == "gene": + sql = """ + SELECT + Species.`Name` AS species_name, + InbredSet.`Name` AS inbredset_name, + Tissue.`Name` AS tissue_name, + ProbeSetFreeze.Name AS probesetfreeze_name, + ProbeSet.Name AS probeset_name, + ProbeSet.Symbol AS probeset_symbol, + ProbeSet.`description` AS probeset_description, + ProbeSet.Chr AS chr, + ProbeSet.Mb AS mb, + ProbeSetXRef.Mean AS mean, + 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 + AND ProbeSetFreeze.public > 0 + ORDER BY species_name, inbredset_name, tissue_name, probesetfreeze_name, probeset_name + LIMIT %s + """ % (self.terms, self.draw) + with Bench("Running query"): + re = g.db.execute(sql).fetchall() + self.trait_list = [] + with Bench("Creating trait objects"): + for line in re: + dataset = create_dataset(line[3], "ProbeSet", get_samplelist=False) + trait_id = line[4] + #with Bench("Building trait object"): + this_trait = GeneralTrait(dataset=dataset, name=trait_id, get_qtl_info=True, get_sample_info=False) + self.trait_list.append(this_trait) + + elif self.type == "phenotype": + sql = """ + SELECT + Species.`Name`, + InbredSet.`Name`, + PublishFreeze.`Name`, + PublishXRef.`Id`, + Phenotype.`Post_publication_description`, + Publication.`Authors`, + Publication.`Year`, + PublishXRef.`LRS`, + PublishXRef.`Locus`, + PublishXRef.`additive` + FROM Species,InbredSet,PublishFreeze,PublishXRef,Phenotype,Publication + WHERE PublishXRef.`InbredSetId`=InbredSet.`Id` + AND PublishFreeze.`InbredSetId`=InbredSet.`Id` + AND InbredSet.`SpeciesId`=Species.`Id` + AND PublishXRef.`PhenotypeId`=Phenotype.`Id` + AND PublishXRef.`PublicationId`=Publication.`Id` + AND (Phenotype.Post_publication_description REGEXP "[[:<:]]%s[[:>:]]" + OR Phenotype.Pre_publication_description REGEXP "[[:<:]]%s[[:>:]]" + OR Phenotype.Pre_publication_abbreviation REGEXP "[[:<:]]%s[[:>:]]" + OR Phenotype.Post_publication_abbreviation REGEXP "[[:<:]]%s[[:>:]]" + OR Phenotype.Lab_code REGEXP "[[:<:]]%s[[:>:]]" + OR Publication.PubMed_ID REGEXP "[[:<:]]%s[[:>:]]" + OR Publication.Abstract REGEXP "[[:<:]]%s[[:>:]]" + OR Publication.Title REGEXP "[[:<:]]%s[[:>:]]" + OR Publication.Authors REGEXP "[[:<:]]%s[[:>:]]" + OR PublishXRef.Id REGEXP "[[:<:]]%s[[:>:]]") + ORDER BY Species.`Name`, InbredSet.`Name`, PublishXRef.`Id` + LIMIT 6000 + """ % (self.terms, self.terms, self.terms, self.terms, self.terms, self.terms, self.terms, self.terms, self.terms, self.terms) + re = g.db.execute(sql).fetchall() + self.trait_list = [] + with Bench("Creating trait objects"): + for line in re: + dataset = create_dataset(line[2], "Publish") + trait_id = line[3] + this_trait = GeneralTrait(dataset=dataset, name=trait_id, get_qtl_info=True, get_sample_info=False) + self.trait_list.append(this_trait) + + self.results = self.convert_to_json() + + def convert_to_json(self): + json_dict = {} + json_dict['draw'] = self.draw, + json_dict['recordsTotal'] = len(self.trait_list), + json_dict['data'] = [] + + for i, trait in enumerate(self.trait_list): + trait_row = ["".format(trait.name, trait.dataset.name), + i+1, + trait.dataset.group.species, + trait.dataset.group.name, + trait.dataset.tissue, + trait.dataset.fullname, + trait.name, + trait.symbol, + trait.description_display, + trait.location_repr, + trait.mean, + trait.LRS_score_repr, + trait.LRS_location_repr, + trait.additive] + json_dict['data'].append(trait_row) + + json_results = json.dumps(json_dict) + return json_results + + + + diff --git a/wqflask/wqflask/views.py b/wqflask/wqflask/views.py index 3bf64a18..aa145969 100644 --- a/wqflask/wqflask/views.py +++ b/wqflask/wqflask/views.py @@ -34,6 +34,7 @@ from flask import (render_template, request, make_response, Response, from wqflask import search_results from wqflask import gsearch +from wqflask import update_search_results from wqflask import docs from wqflask import news from base.data_set import DataSet # Used by YAML in marker_regression @@ -163,9 +164,19 @@ def gsearchact(): result = gsearch.GSearch(request.args).__dict__ type = request.args['type'] if type == "gene": - return render_template("gsearch_gene.html", **result) + return render_template("gsearch_gene_updating.html", **result) elif type == "phenotype": return render_template("gsearch_pheno.html", **result) + +@app.route("/gsearch_updating", methods=('GET',)) +def gsearch_updating(): + result = update_search_results.GSearch(request.args).__dict__ + return result['results'] + # type = request.args['type'] + # if type == "gene": + # return render_template("gsearch_gene_updating.html", **result) + # elif type == "phenotype": + # return render_template("gsearch_pheno.html", **result) @app.route("/docedit") def docedit(): -- cgit v1.2.3